Run-Time Error 429: ActiveX component can't create object

[Sharoninthesun]

Hello

I'm having problems with Error 429 and believe it is due to the
references I am using. My aim is to import all worksheets in an Excel
2003 workbook into Access 2003 but I keep getting the same Error 429
message. I've borrowed the following code (thanks BerHav) but need to
sort out this issue. I think I may not have all the correct references
ticked - all references I do have ticked are:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft Excel 11.0 Object Library
Microsoft Office 11.0 Object Library

Does anyone have any advice as to how to resolve this issue? I've tried
some other similar references but keep getting the same error message.

I'm quite new to this so idiot proof advice would be great!!!

Sharon


Option Compare Database


Function Test()
'Dim XLapp As Excel.Application
Dim XLapp As Object
Dim XLFile As String
Dim XLSheet As String
Dim XLRange As String
Dim TableName As String
Dim z As Integer
Dim SheetCount As Integer


Set XLapp = GetObject(, "Excel.Application")

XLapp.Visible = True
XLFile = "C:\structure.xls"

TableName = "Test"
XLRange = "!"

Set XLwb = XLapp.Workbooks.Open(XLFile)

SheetCount = XLapp.ActiveWorkbook.Sheets.Count
For z = 1 To SheetCount
XLSheet = XLapp.ActiveWorkbook.Sheets(z).Name
XLSheet = XLSheet & XLRange
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
TableName, XLFile, True, XLSheet
Next z


MsgBox "Imported Successfully "


XLapp.Quit
Set XLapp = Nothing
Set XLwb = Nothing


End Function

 

[Douglas J. Steele]

What line of code generates the error message?

Realistically, you don't need a reference to Excel with the code you've got:
you're using Late Binding.

 

[Sharoninthesun]

Douglas

Thanks for the response - the line of code which generates the error
message is

Set XLapp = GetObject(, "Excel.Application")

Sharon

 

[Douglas J. Steele]

I believe that should be

Set XLapp = GetObject("Excel.Application")

In other words, no comma in front.

 

[Kodeworks]

Sharon

Actually your call to the GetObject does need the comma. However, it
will work only when a there is an instance of Excel running, otherwise,
it causes error 429. In your code you have to assume that this error
will be raised and test for it by checking XLapp has been really been
assigned the Excel object. If it was not, then you have to make a call
to CreateObject to fire up a new instance of Excel. Amend your code as
follows.

On Error Resume Next ' error 429 may be raised on the next line
Set XLapp = GetObject(, "Excel.Application")
If XLapp Is Nothing Then ' error 429 occured but code excecution
' was allowed to continue due to
' Resume Next above
Set XLapp = CreateObject("Excel.Application")
End If
On Error GoTo Err_Test 'remember to reset the error handler
' back to the one in this function

XLapp.Visible = True 'CreateObject opens an invisible copy
of Excel so let's see it


Sunil Jadwani
Kodeworks - Business Automation Solutions
www.kodeworks.com

 

[Douglas J. Steele]

Quite right. Good catch.

 

[Kodeworks]

Doug

If I only got a dollar for every time I've left out the comma......



Sunil Jadwani
Kodeworks - Business Automation Solutions
www.kodeworks.com