Excel Object - not accessible when coded in Modules section

[Morris]

Hi everyone,

Only yesterday I made some Excel operations in another database and
everything was working fine, so today I'm trying to do it in another
one and it fails.


I've got Access 2003. I choose
'Modules' from the left hand side, create a new module, put this code
in:


public sub subCreate()
Dim objXLApp As Object
Dim objXLWb As Object
Dim objSheet As Object


Set objXLApp = CreateObject("Excel.Application")
Set objXLWb = objXLApp.Application.Workbooks.Open("c:\temp\gps.xls")
Set objSheet = objXLWb.Worksheets(1)


(...)


end sub


And it stops on line: Set objXLWb =
objXLApp.Application.Workbooks.Open("c:\temp\gps.xls")
with an error: Runtime error 1004, Unable to get the 'Open' property
of the Workbooks class


the only difference to my yesterday's database is that it was a part
of a form - therefore the code was located in
Microsoft Office Access Class Objects, but can I make it somehow work
as a module?

Ps. I've joined the references to Mictrosoft Excel 11.0 Object Library


Cheers,
Morris

 

[Douglas J. Steele]

I see no reason why that shouldn't work: certainly, I put code like that in
modules all the time.

Try downloading my July, 2005 "Access Answers" column from Pinnacle
Publication's "Smart Access" from
http://www.accessmvp.com/DJSteele/SmartAccess.html and see whether it works
for you.

BTW, there's no reason to include the reference to the Microsoft Excel 11.0
Object Library, at least not with what you've shown below. You're using Late
Binding, which means no reference is necessary (That makes it much easier to
share in an environment where you can't be sure of exactly what version of
Excel your users will have)