more excel automation trouble with ms access
 

I must not be understanding something about Excel automation. Here is the
start of my code in MS Access.

Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

objXL.Application.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkbook

With objActiveWkb

Worksheets(1).Name = Format(dteRptDate, "mmm dd, yyyy")
Sheets("Sheet1").Name = Format(dteRptDate, "mmm dd, yyyy")

'Set up the font for the entire sheet
Cells.Select

......
.......
etc.



It all compiles just fine but when it gets to either the
"Worksheets(1).Name....." line or the "Sheets("Sheet1").Name... " lines I
get the error:

Method 'Worksheets' of object '_Global' failed.

Same error on the "Cells.Select" line

Method 'Cells' of object '_Global' failed.

What am I doing wrong?

Thanks,

Keith

more excel automation trouble with ms access
 

Why bother to use the With statement if you don't preceed any of the
subordinate objects with a period?

With objActiveWkb

With .Worksheets(1)
.Name = Format(dteRptDate, "mmm dd, yyyy")
.Activate
.Cells.Select

Why use select anyway ? - just address the objects and perform the action.



--
Regards,
Tom Ogilvy


"Keith G Hicks" wrote:

I must not be understanding something about Excel automation. Here is the
start of my code in MS Access.

Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

objXL.Application.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkbook

With objActiveWkb

Worksheets(1).Name = Format(dteRptDate, "mmm dd, yyyy")
Sheets("Sheet1").Name = Format(dteRptDate, "mmm dd, yyyy")

'Set up the font for the entire sheet
Cells.Select

......
.......
etc.



It all compiles just fine but when it gets to either the
"Worksheets(1).Name....." line or the "Sheets("Sheet1").Name... " lines I
get the error:

Method 'Worksheets' of object '_Global' failed.

Same error on the "Cells.Select" line

Method 'Cells' of object '_Global' failed.

What am I doing wrong?

Thanks,

Keith