more excel automation trouble with ms access

K

Keith G Hicks

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
 
T

Tom Ogilvy

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top