method 'add' of object 'workbooks' failed

[Cheryl]

I have some code in an Access 2003 database that has performed fine in Access
2007 until very recently. In fact it performs on at least one computer with
Access 2007 but not on the other.

The references are identical (previously I was using late binding with no
reference to Excel, but that does not seem to make any difference)

Both computers have the same office version, same VBAJet32.dll version but
very different list of updates. The code is as follows

Dim oXL As Object
Dim oWB As Object

Set oXL = CreateObject("Excel.Application")
Set oWB = oXL.workbooks.Add

Set oWB = Nothing
Set oXL = Nothing

It failes on the oXL.workbooks.Add line with the error "method 'add' of
object 'workbooks' failed.

I'm stumped on this one. I also tried at someones suggestion doing:
oXL.workbooks.add
Set oWB = oXL.activeworkbook

to no avail. Hope someone can point me in the right direction on what to
look for, as I'm afraid I will have this issue on other computers as well.

Thanks in advance
Cheryl

 

[Alex Dybenko]

Hi,
line
Set oWB = oXL.workbooks.Add
looks ok
perhaps you have corrupted office installation - try to reinstall it (or
repair office), or perhaps something with user permissions. If you are on
Vista - try to start access with Run As Administrator

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Stefan Hoffmann]

hi Cheryl,

It failes on the oXL.workbooks.Add line with the error "method 'add' of
object 'workbooks' failed.

I'm stumped on this one. I also tried at someones suggestion doing:
oXL.workbooks.add
Set oWB = oXL.activeworkbook

to no avail. Hope someone can point me in the right direction on what to
look for, as I'm afraid I will have this issue on other computers as well.

Use a "debug" version with early binding.

mfG
--> stefan <--

 

[Cheryl]

Uninstalling and reinstalling, repairing, etc. did nothing to resolve this
issue.

Where do I find a find the debug version?