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

[sam]

@WCrihfield bit of update -- made following changes to this simplified version and it works now.

I do really appreciate your help. If you do know why this worked and why it was not working before please do let me know too -- I liked your code as it was really stable code with good error handling. 

Have a good weekend, 
Regards, 

sam

[WCrihfield]

Well, oddly enough, I know from experience that VBA will let you work with objects that it doesn't have a definition for, and allows you to access their Properties & Methods (as long as they exist), without throwing errors.  There just won't be any Object Browser support, or intellisense type support for any of it.  So, if it is working OK for you on the AutoCAD side of the same machine, it should also work the same way on the Inventor side, when both are set-up the same way.  So, maybe try turning off that reference to the Excel Object Library on the Inventor side too.  Then, since it won't recognize any of the object types defined within that object library, just don't try to create any variables of those direct types, and define them all as Object instead when needed, like in your example codes.

PS.  I didn't see your last post when I posted this, because it was on the next tab of the web page. 🙄

[sam]

Thanks for the reply. 
I pretty much did the same as you are explaining and it fixed it. Trying copy same setting as autoCad but if turn off reference in Inventor it gives error with the code shown in snapshot but works fine in ACAd without reference. really odd and strange. 😐

[gerrardhickson]

Hi All. I'm having the same problem as the OP - same error message, same version of Excel.
In my case, I have the Excel reference added.

When I reference the excel object like:

dim oExcel as Excel.Application

 Then I get the error shown in the original post.

When I reference the excel object like:

dim oExcel as Object

then I don't get the error until I try to do this:

Dim oWB As Workbook
Set oWB = oExcel.Workbooks.Open ("C:\Temp\Test.xlsx")

Now, I can continue to work without assigning the object to oWB like this:

oExcel.Workbooks.Open "C:\Temp\Test.xlsx"

But I need to reference the workbook as 'oExcel.Workbooks(1)' or similarly. Any time that I try to assign an excel object to some variable, then I get the message in the original post.

Below is my code (unedited) - which, btw, doesn't work because it has Excel.Application as my object declaration.

Sub iPropExcelUpdate()

Dim oE As Excel.Application

On Error Resume Next

Set oE = GetObject(, "Excel.application")
If Err Then
    Err.Clear
    Set oE = CreateObject("Excel.Application")
End If
On Error GoTo 0

oE.Visible = True
Dim sFN As String
sFN = "C:\Temp\Test.xlsx"

Dim oWB As Workbook
Set oWB = oE.Workbooks.Open(sFN)

End Sub

Any suggestions?

[sebastien_forman]

Hello, same problem for me, all script are running before with inventor 2018 and excel 365 version 2207 build 15427.20210.

We have only upgrade to inventor to version 2022 and all script with excel call is broken.

Dim AppXls As Object
Dim wb As Object 'Excel.workbook

Set AppXls = CreateObject("Excel.Application")
Set wb = AppXls.Workbooks.Open("C:\Users\...\position sheet.xlsx", , True, , , , True, , , , False)

Changing "Excel.workbook" to Object works, BUT impossible to use workbooks.open, that make an run time error '1004' -"Application defined or object defined error"

The reference to "Microsoft Excel 16.0 Object Library" is active.

Curiously, espion watching on AppXls is create have the correct file load in it just before error.

I have try all idea of this topic but nothing works...

Can you help me please?

Thank you

Sébastien

[floccipier]

@sebastien_forman I tried around 2 dozen different ways and finally this worked for me and luckily its been working since then as I depend on it a lot for my day to day job. 

Dim oExcel As Object
On Error Resume Next
Err.Clear
Set oExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set oExcel = CreateObject("Excel.Application")
If Err.Number <> 0 Then
MsgBox "Error: Failed to start Excel: " & Err.Description, vbCritical
Exit Sub
End If
End If

' Open the workbook.
Dim Book As Object
Set Book = oExcel.ActiveWorkbook

' Get the sheet.
Dim sheet As WorkSheet
Set sheet = Book.Sheets.Item("Sheet1")

I hope this works for you.

best reards, 

flo

[floccipier]

sorry I just re read your complete post and this my reply might not be too helpful for you. 

best regards, 

flo

[sebastien.forman]

up