K
KJGinNC
Hi,
I'm starting off here before I go to the Excel groups. I've managed to
figure out how to do a two axes graph in Excel from Access. Now I'm trying
to set it up so I can loop through all of the specialists from an access
table and update the corresponding worksheet in a workbook with new data.
I'm getting stuck on syntax and I've spent hours trying to figure out what
stupid thing I'm doing wrong... I managed to find some help by searching, but
now I'm really stuck!
Stepping through I can get it to the point where it goes to the SheetExists
function, but that doesn't seem to work (always returns false). I found it
in an Excel group (over and over again) but can't seem to make it work in my
code. But even so once it gets back to the Else part of the if statement the
copy syntax is wrong.
Clearly I'm doing something wrong, and it's probably simple... .but what??
Can anyone help??
Thanks!
Karrie
Option Compare Database
Option Explicit
Private Sub cmdChart_Click()
On Error GoTo Err_cmdChart_Click
' Object Variables for Automation
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objDataSheet As Excel.Worksheet
Dim objChartSheet As Excel.ChartObject
Dim objXLRange As Excel.Range
Dim objXLWorkBook As Excel.Workbook
'ADO and other variables
Dim rstChart As ADODB.Recordset, rstSpecialist As ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim varResults As Variant
Dim intCount As Integer
Dim strUserName As String
Set cnn = CurrentProject.Connection
Set rstChart = New ADODB.Recordset
Set rstSpecialist = New ADODB.Recordset
Set objXLBook = GetObject("U:\DbaseDevelopment\Passport Review Board\test
report.xls")
Set objXLApp = objXLBook.Parent
'** Open Specialst table and loop until the end
rstSpecialist.Open "tblSpecialist", cnn, adOpenDynamic, adLockPessimistic
Do Until rstSpecialist.EOF
strUserName = (rstSpecialist![UserName])
'** Open the worksheet for the first user name if it doesn't exist create it
If SheetExists(strUserName) = True Then
Set objDataSheet = objXLBook.Worksheets(strUserName)
Else
'** Create Worksheet
objXLBook.Worksheets("DataTest").Copy After:=Worksheets("DataTest")
ActiveSheet.Name = strUserName
End If
'** Do the chart stuff
REMOVED FOR EASIER READING
'Get next Specialist
rstSpecialist.MoveNext
Loop
objXLBook.Save
objXLApp.Visible = True
objXLBook.Windows(1).Visible = True
objXLApp.Quit
Set objXLBook = Nothing
Set objXLApp = Nothing
Exit_cmdChart_Click:
Exit Sub
Err_cmdChart_Click:
MsgBox Err.Description
Resume Exit_cmdChart_Click
End Sub
Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
'If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function
I'm starting off here before I go to the Excel groups. I've managed to
figure out how to do a two axes graph in Excel from Access. Now I'm trying
to set it up so I can loop through all of the specialists from an access
table and update the corresponding worksheet in a workbook with new data.
I'm getting stuck on syntax and I've spent hours trying to figure out what
stupid thing I'm doing wrong... I managed to find some help by searching, but
now I'm really stuck!
Stepping through I can get it to the point where it goes to the SheetExists
function, but that doesn't seem to work (always returns false). I found it
in an Excel group (over and over again) but can't seem to make it work in my
code. But even so once it gets back to the Else part of the if statement the
copy syntax is wrong.
Clearly I'm doing something wrong, and it's probably simple... .but what??
Can anyone help??
Thanks!
Karrie
Option Compare Database
Option Explicit
Private Sub cmdChart_Click()
On Error GoTo Err_cmdChart_Click
' Object Variables for Automation
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objDataSheet As Excel.Worksheet
Dim objChartSheet As Excel.ChartObject
Dim objXLRange As Excel.Range
Dim objXLWorkBook As Excel.Workbook
'ADO and other variables
Dim rstChart As ADODB.Recordset, rstSpecialist As ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim varResults As Variant
Dim intCount As Integer
Dim strUserName As String
Set cnn = CurrentProject.Connection
Set rstChart = New ADODB.Recordset
Set rstSpecialist = New ADODB.Recordset
Set objXLBook = GetObject("U:\DbaseDevelopment\Passport Review Board\test
report.xls")
Set objXLApp = objXLBook.Parent
'** Open Specialst table and loop until the end
rstSpecialist.Open "tblSpecialist", cnn, adOpenDynamic, adLockPessimistic
Do Until rstSpecialist.EOF
strUserName = (rstSpecialist![UserName])
'** Open the worksheet for the first user name if it doesn't exist create it
If SheetExists(strUserName) = True Then
Set objDataSheet = objXLBook.Worksheets(strUserName)
Else
'** Create Worksheet
objXLBook.Worksheets("DataTest").Copy After:=Worksheets("DataTest")
ActiveSheet.Name = strUserName
End If
'** Do the chart stuff
REMOVED FOR EASIER READING
'Get next Specialist
rstSpecialist.MoveNext
Loop
objXLBook.Save
objXLApp.Visible = True
objXLBook.Windows(1).Visible = True
objXLApp.Quit
Set objXLBook = Nothing
Set objXLApp = Nothing
Exit_cmdChart_Click:
Exit Sub
Err_cmdChart_Click:
MsgBox Err.Description
Resume Exit_cmdChart_Click
End Sub
Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
'If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function