Can't create workbook object.

[Dave Wilson]

I keep getting an ActiveX component can't create object error message (#429)
while trying to create a workbook instance. Google research leads me to
suspect some misconfiguration on my machine but I can't imagine what that
would be, since I'm having no other problems. The debugger stops on the SET
oBook line of code.

CODE snippet:
Private Sub Form_Open(Cancel As Integer)
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Excel.Workbook
Set oExcel = New Excel.Application
Set oBook = CreateObject("Excel.Workbook")
oBook = oExcel.Workbooks.Add
With oExcel
End With
MsgBox oExcel.Name
Set oExcel = Nothing

End Sub

I have Microsoft Excel 11.0 Object Library as a reference, pushed up to the
highest priority it will go (3rd, behind VBA and Access 11.0)

I've looked at any number of KBs and none identify anything that I'm doing
wrong.

I can run XL, I can open an instance of it from Access using a SHELL script,
the registry settings seem OK. XL doesn't have a dll to register. I'm
stumped.

Thanks in advance for your suggestions.

Dave

 

[Klatuu]

Try it this way, also notice the Quit below, otherwise, the instance of Excel
will stay open:
Private Sub Form_Open(Cancel As Integer)
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Excel.Workbook
Set oExcel = New Excel.Application
Set oBook = oExcel.Workbooks.Add
With oExcel
End With
MsgBox oExcel.Name
oExcel.Quit
Set oExcel = Nothing