topic Re: CreateObject("Excel.Application") - Office365 in Inventor Programming Forum

CreateObject("Excel.Application") - Office365

floccipier — Mon, 10 May 2021 11:08:17 GMT

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

Re: CreateObject("Excel.Application") - Office365

floccipier — Mon, 10 May 2021 11:21:02 GMT

Update:

Changed below code

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

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.

Re: CreateObject("Excel.Application") - Office365

floccipier — Mon, 10 May 2021 12:05:17 GMT

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

Re: CreateObject("Excel.Application") - Office365

bradeneuropeArthur — Mon, 10 May 2021 16:36:13 GMT

Remove this.

Dim excelApp As New Excel.Application

and it went ahead but now stopping at this line:

Re: CreateObject("Excel.Application") - Office365

floccipier — Mon, 10 May 2021 18:18:35 GMT

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

Re: CreateObject("Excel.Application") - Office365

WCrihfield — Mon, 10 May 2021 18:51:56 GMT

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

Re: CreateObject("Excel.Application") - Office365

bradeneuropeArthur — Tue, 11 May 2021 07:56:09 GMT

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!

Re: CreateObject("Excel.Application") - Office365

floccipier — Fri, 28 May 2021 10:23:52 GMT

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

Re: CreateObject("Excel.Application") - Office365

floccipier — Fri, 28 May 2021 10:24:40 GMT

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

Re: CreateObject("Excel.Application") - Office365

WCrihfield — Fri, 28 May 2021 12:37:29 GMT

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

Re: CreateObject("Excel.Application") - Office365

floccipier — Fri, 28 May 2021 15:44:15 GMT

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

Re: CreateObject("Excel.Application") - Office365

sam — Thu, 15 Jul 2021 16:00:17 GMT

@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

Re: CreateObject("Excel.Application") - Office365

WCrihfield — Thu, 15 Jul 2021 16:27:06 GMT

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.

Re: CreateObject("Excel.Application") - Office365

sam — Fri, 16 Jul 2021 17:04:26 GMT

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

This one gives error literally on first line.

Re: CreateObject("Excel.Application") - Office365

sam — Fri, 16 Jul 2021 17:04:47 GMT

going to try this.

Re: CreateObject("Excel.Application") - Office365

sam — Fri, 16 Jul 2021 17:19:39 GMT

checking with this

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

and it gives same error as in original post.

Re: CreateObject("Excel.Application") - Office365

WCrihfield — Fri, 16 Jul 2021 17:29:13 GMT

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.

Re: CreateObject("Excel.Application") - Office365

sam — Fri, 16 Jul 2021 18:38:19 GMT

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.

Re: CreateObject("Excel.Application") - Office365

WCrihfield — Fri, 16 Jul 2021 18:57:35 GMT

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)?

Re: CreateObject("Excel.Application") - Office365

sam — Fri, 16 Jul 2021 19:10:12 GMT

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