Runtime error 429... Cannot create Excel Object from Access

[ThriftyFinanceGirl]

One machine only. Not having trouble on other machines. And we even had IT
re-install MS Office. Help! Here is the code where this machine is
stopping...

It errors out on the "GetObject" area...

'name and full path to use to save the xls file
strWorkBook = "\\Nas01\dol_exch\Departments\Accounting\Sales " & _
"Tax\Sales Tax Database\TOUR\TourJE_" & intmonth & intyear &
".xls"

strTemplate = "\\Nas01\dol_exch\Departments\Accounting\Sales " & _
"Tax\Sales Tax Database\Templates\TourReallocationJE.xls"

FileCopy strTemplate, strWorkBook
blnEXCEL = False

' Replace True with False if you do not want the first row of
' the worksheet to be a header row (the names of the fields
' from the recordset)
blnHeaderRow = False

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If

 

[ryguy7272]

Did you add a reference to Excel? in VBE, Tools > References > Microsoft
Excel xx.x Object Library. I use Office 2002, so for me it is Excel 10.0
Object Library. Look here - Versions:
http://en.wikipedia.org/wiki/Microsoft_Excel


HTH,
Ryan---

 

[ThriftyFinanceGirl]

Yes, the reference is in place...

Did you add a reference to Excel? in VBE, Tools > References > Microsoft
Excel xx.x Object Library. I use Office 2002, so for me it is Excel 10.0
Object Library. Look here - Versions:
http://en.wikipedia.org/wiki/Microsoft_Excel


HTH,
Ryan---

 

[ryguy7272]

Well, always try the easy things, and the obvious things first. Since it
wasn't the reference, it seems like the issue could be a bit complex. Check
this:
http://support.microsoft.com/kb/828550


Maybe it is a registry setting on that one troublesome computer:
http://support.microsoft.com/kb/177394
http://www.mvps.org/access/bugs/bugs0007.htm
http://ezinearticles.com/?Understanding-and-Fixing-Runtime-Error-429&id=1509811
http://www.instant-registry-fixes.org/fixing-runtime-error-429-caused-by-automation-server/

HTH,
Ryan---

 

[David H]

I realize that you've wrapped the GetObject statement with error trapping,
but try taking it out and going strictly with CreateObject. It'll help to
narrow down were the problems is.

Also
1) Have you Dim'd xlx? If so can you post the declaration.
2) Do you have a Set xlx = Nothing in your module? If not, you need to add
it to the code immediately as the rule is 'explicitly close what you open,
destroy what you create'. It won't solve you're currently problem but will
avoid additional ones down the road.

 

[Tony Toews [MVP]]

One machine only.

Do they have a different version of Excel than your other systems?

Late binding means you can safely remove the reference and only have
an error when the app executes lines of code in question. Rather than
erroring out while starting up the app and not allowing the users in
the app at all. Or when hitting a mid, left or trim function call.

This also is very useful when you don't know version of the external
application will reside on the target system. Or if your organization
is in the middle of moving from one version to another.

For more information including additional text and some detailed links
see the "Late Binding in Microsoft Access" page at
http://www.granite.ab.ca/access/latebinding.htm

Tony