OLE Automating Excel from Access VBA

[Bug16]

I'm pretty new to OLE Automation so bear with me!

I'm intending to create a complex Excel spreadsheet that I want to pump values from Access into on a cell by cell basis.

I've created a quick Access VBA test project that opens my .xls file "oletest", changes focus in Excel to "Sheet1" and pumps a hardcoded value into cell (1,1). It then saves the .xls file under another name and attempts to tidy itself up:

Set xlObject = CreateObject("excel.application&quot
xlObject.Visible = True
xlObject.Application.Workbooks.Open "c:\oletest.xls"
Worksheets("Sheet1&quot.Activate
Worksheets("Sheet1&quot.Cells(1, 1).Value = "Test 123"
Worksheets("Sheet1&quot.SaveAs ("c:\anothertest.xls&quot
xlObject.Application.Workbooks.Close
xlObject.Quit

The problem I have is that the first time I run the project it works. The second time I get:

"Run-time error '1004':

Method 'Worksheets' of object '_Global' failed"

This is caused by the line:

Worksheets("Sheet1&quot.Activate

If I click on help nothing happens.

I'm assuming that I'm not tidying up Excel properly when I close it down but I have no idea what else I should be doing.

Help as always is appreciated!

 

[illini]

Bug16,

Try this...

Private Sub Command0_Click()
Set xlobject = CreateObject("excel.application&quot
xlobject.Visible = True
xlobject.Application.Workbooks.Open "c:\oletest.xls"
Windows("oletest.xls&quot.Activate
Worksheets("Sheet1&quot.Activate
Worksheets("Sheet1&quot.Cells(1, 1).Value = "Test 123"
Worksheets("Sheet1&quot.SaveAs ("c:\anothertest.xls&quot
xlobject.Application.Workbooks.Close
xlobject.Quit
xlobject = ""

End Sub -illini

 

[illini]

Bug16,

Actually, look at the line just above the highlighted.

Windows("oletest.xls&quot.Activate -illini

 

[Bug16]

Hmmm... I now get a new error when I run the project for a second time!

"Run-time error '9':

Subscript out of range"

And it debugs to your Windows("oletest.xls&quot.Activate code.

Now I understand this error in a "normal" coding sense but with regard to OLE I'm guessing that the Windows object doesn't exist once the project has been run and we're trying to activate a non-existent object?

 

[Bug16]

A quick check on the online MSDN shows the problem and solution:

http://support.microsoft.com/default.aspx?scid=kb;en-us;178510