CreateObject("Excel.Application") - Office365

[floccipier]

Hi All, 

I have receive a new machine with office365 installed and my code give following error. 

excel version: 

Microsoft® Excel® for Microsoft 365 MSO (16.0.13929.20222) 64-bit

Invenotr2019 Build 330

Any idea how can I fix it?

regards, 

Sam

[floccipier]

Update: 

Changed below code 

    Dim excelApp As New Excel.Application
    Set excelApp = CreateObject("Excel.Application")

to

    Dim excelApp As New Excel.Application

and it went ahead but now stopping at this line:
    Set workBook = excelApp.Workbooks.Open(ProjectPath)
and error is same as shown in original post. 

[floccipier]

I guess my error is still same as in Locals window value for excelApp is Nothing

[bradeneuropeArthur]

Remove this.

Dim excelApp As New Excel.Application

and it went ahead but now stopping at this line:

[floccipier]

@bradeneuropeArthur I am sorry I didn't understand..

[WCrihfield]

Try this:

Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
Set oExcelApp = New Microsoft.Office.Interop.Excel.Application

Also, do you have the reference turned on within the File menu / References... dialog in your VBA editor?

In iLogic, the first two lines would look like this to set up the references needed:

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.

If you want and have time, I would appreciate your Vote(s) for My IDEAS 💡or you can Explore My CONTRIBUTIONS

[bradeneuropeArthur]

I mean use it this way:

Changed below code 

    Dim excelApp As object 'Excel.Application
    Set excelApp = CreateObject("Excel.Application")

to

or if you have the excel references:

Changed below code 

    Dim excelApp As Excel.Application
    Set excelApp = CreateObject("Excel.Application")

to

Late Binding vs Early Binding!

[floccipier]

Thanks for this, I will check this later and will come back to you.

[floccipier]

Thanks for this, I will check this later and will come back to you!

[WCrihfield · Accepted solution]

Here is a VBA code routine that more thoroughly attempts to either Get or Create an instance of the Excel application, while avoiding potential errors, for use when you have your Reference turned on for the Excel Object Library.  This can be used as it is within your main code, or put into a Function of its own.  It also includes a Boolean variable that is meant to record whether or not Excel was already open when the routine started, which you can use at the end to judge whether to close the Excel application when your done with it.

I put in a bunch of comment lines to help explain what's going on, for those not as familiar with VBA or this process.

    Dim oExcel As Excel.Application
    Dim oXLWasOpen As Boolean 'False by default
    On Error Resume Next
    'try to get open instance of Excel Application
    Set oExcel = GetObject(, "Excel.Application") 'tries to retrieve running instance of Excel
    If Err = 0 Then
        'it found and retrieved an open instance of Excel
        oXLWasOpen = True 'so I know whether to close Excel at the end
        On Error GoTo 0 'resets error handling to normal
    ElseIf Err <> 0 Then
        'it failed to get an open instance of Excel, so now create one
        Err.Clear 'clear it so we know if another error happens
        'try to create an instance of Excel (Open Excel)
        Set oExcel = CreateObject("Excel.Application")
        If Err <> 0 Then
            'the attempt to create an instance of Excel failed too
            Call MsgBox("Failed to Get or Create Excel Application instance. Exiting.", , "")
            Exit Sub
        Else
            'it succeeded to create an instance of Excel (opened Excel)
            Err.Clear
            On Error GoTo 0 'resets error handling to normal
        End If
    End

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.

If you want and have time, I would appreciate your Vote(s) for My IDEAS 💡or you can Explore My CONTRIBUTIONS

[floccipier]

Thanks for this, really well explained. I need to get back to this part of work on Monday, I will check this detailed and original post and then Mark them. But I can already see by reading code that this is really detailed and helpful.
regards,
sam

[sam]

@WCrihfield came to ask something about this and realised that I haven't even it solved because it did solve for the problem for long time. Yesterday even windows sent a notification of update and I updated it and since then now it is not working again. 

Dim oExcel As Excel.Application
'Dim oExcel As Object
Dim oXLWasOpen As Boolean 'False by default
On Error Resume Next
'try to get open instance of Excel Application
Set oExcel = GetObject(, "Excel.Application") 'tries to retrieve running instance of Excel
If Err = 0 Then
'it found and retrieved an open instance of Excel
oXLWasOpen = True 'so I know whether to close Excel at the end
On Error GoTo 0 'resets error handling to normal
ElseIf Err <> 0 Then
'it failed to get an open instance of Excel, so now create one
Err.Clear 'clear it so we know if another error happens
'try to create an instance of Excel (Open Excel)
Set oExcel = CreateObject("Excel.Application")
If Err <> 0 Then
'the attempt to create an instance of Excel failed too
Call MsgBox("Failed to Get or Create Excel Application instance. Exiting.", , "")
Exit Sub
Else
'it succeeded to create an instance of Excel (opened Excel)
Err.Clear
On Error GoTo 0 'resets error handling to normal
End If
End If

It just goes to message Failed to Get or Create Excel Application instance. Exiting even if excel is running. Any idea how I can sort this please. 
regards, 
Sam

[WCrihfield]

Hi @sam.  There seems to be a long and rich history of folks encountering similar problems while working with the Excel application from iLogic/VBA/VB.NET and other code.  There could be various reasons, and it's difficult to diagnose, especially remotely.  One common issue I see is when switching between older and newer Excel files, or when switching from using older Excel application to using newer Excel application.  Older Excel files used the ".xls" file extension, and newer Excel files default to the ".xlsx" file extension.  There seems to be some programming situation's where it seems like it will only work with files with the ".xls" file extension for some reason.  But you're not even trying to access a document yet, so that's not it.  In other scenarios, people had to either undo an update, reinstall or repair their Excel installation, or redo the update, because something must have gone wrong in the update.  If it can't find your Excel application, I'm afraid that's a problem above my skill level to provide a solution for.  There are some other ways to access the Excel application from a code standpoint, but I rather doubt they would help here.

[sam]

Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
Set oExcelApp = New Microsoft.Office.Interop.Excel.Application

This one gives error literally on first line.

[sam]

going to try this.

[sam]

checking with this

Dim excelApp As Excel.Application
Set excelApp = CreateObject("Excel.Application")

and it gives same error as in original post.

[WCrihfield]

If you are using VBA, then double check to make sure your reference to the "Microsoft Excel 16.0 Object Library" is checked (turned on).  The "16.0" part may be different on your machine, because it has to do with what version it is.  To check this, within your VBA Editor screen, click on the Tools tab, then click on References....  Beware, these are set-up differently for document projects than they are for the main application project, so make sure you have the right VBA project selected (active) when you go into the References dialog.  (If your macro is in the Application project, make sure you click on the application project in the project explorer window, before you open the references dialog.)  In that references dialog, scroll down the list until you see an item with a similar name to the one I mentioned above, then make sure it has a check mark in its checkbox, then click the main OK button.

[sam]

Thanks @WCrihfield again for a quick reply.
reference for Excel is there, it was first thing that I check when did the OP.

Another thing that I want to mention that I have a workflow from where I export some data from cad layout to excel and that rule in cad satarts something like this.
blic Sub SpitOutMG()

Dim sset As AcadSelectionSet
Dim ent As AcadEntity
Dim Book1 As Object
Dim Sheet1 As Object
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set Book1 = xlApp.Workbooks.Add()
Set Sheet1 = Book1.worksheets(1)

and works perfectly even if there is no reference of "Microsoft Excel 16.0 Object Library" in AutoCad VBA library. so I took the lead and copy this portion in Inventor VBA


Public Sub SpitOutMG()

'Dim sset As AcadSelectionSet
'Dim ent As AcadEntity
Dim Book1 As Object
Dim Sheet1 As Object
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set Book1 = xlApp.Workbooks.Add()
Set Sheet1 = Book1.Worksheets(1)

Dim fcode(0) As Integer
Dim fdata(0) As Variant
Dim I As Integer

End Sub

and it actually worked and excel is initiated. What does this mean? only difference I noticed is this Sub is not part of UserForm code but in a separate module? I am bit lost here.

[WCrihfield]

In your AutoCAD side VBA Editor, you're saying that you don't have that Excel reference set up within its References dialog, and your code there works OK accessing and using Excel right.  While there, with your AutoCAD side macro code open, if you select the term Workbooks, and right click over it and choose Definition from the right-click menu, does the Object Browser open up and show you that selected object within that Object Browser (recognizes its Type, a shows info about it)?

[sam]

Please have a look at attached video.
and if I select Workbooks term and select definition this is the error that I get.