I have written a series of VBA subs to automate some tasks. The user starts
the code in Access, but all the work takes place in Excel. I have run it on
two machines, both using the same versions of Excel and Access, 2002 SP3. On
the "good machine" everything works fine, on the "bad machine" I get a series
of errors that kills the macro.

Here are the sticking points...

' open excel
Set oExcelApp = GetObject(, "Excel.Application")
If Err < 0 Then
'Excel wasn't running, start it from code
Set oExcelApp = CreateObject("Excel.Application")
bStarted = True
End If

In this code the "bad machine" fails on the first line with a 429 runtime,
can't create object. However if I advance the program counter by hand to the
CreateObject, that works fine. Normally this happens automatically, because
in this case Excel was not running, and on the "good machine" it gets Err <
0 and creates it. This code is used all over the place in our macros, and
works on every other machine.

Later, after advancing by hand, I have created the spreadsheet and attempt
to send it using this code:

With oExcelApp.activesheet.MailEnvelope.Item
.Recipients.Add "Me"
.Subject = "Test"
.Send
End With

Once again this runs fine on all the other machines I've tried it on. On the
bad machine I get another runtime error, 80004005, Method MailEnvelop of
object _Worksheet failed.

This seems like either a versioning problem in the dictionaries, or a
security issue. Can anyone help?

Maury

Take a look at the references to see if any are missing. In the VBE go to
Tools References and look for anything selected but missing. That would be
where I would start.
--
HTH...

Jim Thomlinson


"Maury Markowitz" wrote:

I have written a series of VBA subs to automate some tasks. The user starts
the code in Access, but all the work takes place in Excel. I have run it on
two machines, both using the same versions of Excel and Access, 2002 SP3. On
the "good machine" everything works fine, on the "bad machine" I get a series
of errors that kills the macro.

Here are the sticking points...

' open excel
Set oExcelApp = GetObject(, "Excel.Application")
If Err < 0 Then
'Excel wasn't running, start it from code
Set oExcelApp = CreateObject("Excel.Application")
bStarted = True
End If

In this code the "bad machine" fails on the first line with a 429 runtime,
can't create object. However if I advance the program counter by hand to the
CreateObject, that works fine. Normally this happens automatically, because
in this case Excel was not running, and on the "good machine" it gets Err <
0 and creates it. This code is used all over the place in our macros, and
works on every other machine.

Later, after advancing by hand, I have created the spreadsheet and attempt
to send it using this code:

With oExcelApp.activesheet.MailEnvelope.Item
.Recipients.Add "Me"
.Subject = "Test"
.Send
End With

Once again this runs fine on all the other machines I've tried it on. On the
bad machine I get another runtime error, 80004005, Method MailEnvelop of
object _Worksheet failed.

This seems like either a versioning problem in the dictionaries, or a
security issue. Can anyone help?

Maury

"Jim Thomlinson" wrote:

Take a look at the references to see if any are missing. In the VBE go to
Tools References and look for anything selected but missing. That would be
where I would start.

I'm using late binding, so I don't think that's it.

Maury