Help with an Excel Interop DLL error

[JBerns]

Community,

I am developing an iLogic rule to read/import data from an Excel file.

The rule works fine on a computer that has Office 365 installed and Inventor 2022.4.1.

A "System.Runtime.InteropServices.COMException: Invalid Index" error occurs when the rule is run on a computer with Office 2016 and Inventor 2022.3.1.

Any assistance to diagnose or resolve the error would be appreciated.

Here is the code that accesses the XLSX file:

Dim oXLFile As String = "C:\Users\jberns\Downloads\Civil 3D Output file.xlsx"
Dim oSheet As String = "Combined_Data"
Dim oExcel As New Microsoft.Office.Interop.Excel.ApplicationClass

oExcel.DisplayAlerts = False
oExcel.Visible = False

Dim oWB As Workbook = oExcel.Workbooks.Open(oXLFile)
Dim oWS As Worksheet = oWB.Worksheets.Item(oSheet)

Here is the error:

Why would the different versions of Office or Inventor updates cause this error when getting worksheet names?

Can the DLL from PC1 be copied to PC2 and resolve the issue? I certainly don't want to corrupt the Office installation.

As other users test this rule, I wonder how many other similar errors will occur.

Thank you for your time and attention. I look forward to your replies.

Regards,

Jerry

[petr.meduna]

Try to write simple function that return integer index by worksheet name and use this index instead of string.

Private Function getIndexOfWorkSheet(ByVal name As String, ByVal wsheets As Worksheets) As Integer
            Dim idx As Integer = 1
            For Each w As Worksheet In wsheets
                If w.Name = name Then
                    Return idx
                End If
                idx = idx + 1
            Next
End Function

[JBerns]

@petr.meduna,

I can try that as a work-around, but why can I use worksheet names on one computer, but not on the other computer?

Regards,

Jerry

[Dev_rim]

Hi Jerry,

I am not sure but I don't think you need to use worksheets."Item"

As far as I know we don't need to do that in VB.net and I think iLogic is based on this. So can you please try:

Dim oWS as Worksheet =oWB.Sheets("Combined_Data")

Normally in some versions it gives you an type mismatch error. So it you get that kinda error you can also try this one:

Dim oWS as Worksheet = CType(oWB.Sheets("Combined_Data"), Worksheet)

As I said I am not an expert on iLogic 🙂 I hope it helps.

Regards

Devrim

[JBerns]

@petr.meduna,

I added your Function to my code, but it resulted in this error:

System.InvalidCastException: 'Unable to cast COM object of type 'System.__ComObject' to interface type

I may have the code wrong:

Dim oXLFile As String = TxtExcelFile.Text
Dim oSheet As String = "Combined_Data"
Dim oExcel As New Microsoft.Office.Interop.Excel.ApplicationClass

oExcel.DisplayAlerts = False
oExcel.Visible = False

Dim oWB As Workbook = oExcel.Workbooks.Open(oXLFile)
Dim oWSItem As Integer = getIndexOfWorkSheet(oSheet, oWB.Worksheets)

oWB.Worksheets is where the error occurs.

How else do I pass the worksheets?

@Dev_rim,

I tried your suggestions too. Unfortunately, each resulted in Unhandled Exception Errors similar to above.

Its as though Inventor 2022 does not know how to read the file using Excel 2016.

So confusing and frustrating.

Regards,

Jerry

[petr.meduna]

Isn't there a problem with oExcel variable? I use this code to write to excel quite often and never experienced this type of error. Microsoft.Office.Interop.Excel library is derived right from excel directory and I've never had a problem with Excel 2016, 2019, 365.

Imports Microsoft.Office.Interop.Excel

Dim excelApp As Microsoft.Office.Interop.Excel.Application
Dim myExcelTemp As String = My.Application.Info.DirectoryPath & "\temp.xlsx"
excelApp = CreateObject("Excel.Application")
excelApp.Visible = False
excelApp.DisplayAlerts = False
Dim wb As Workbook = excelApp.Workbooks.Open(myExcelTemp)
Dim bomSheet As Worksheet = wb.Worksheets.Item("Sheet1")

[petr.meduna]

Also, there is known issue with COM objects in Excel. Try to repair Excel 2016/365 in Conrol Panel on machine where applications don't work properly.

[JBerns]

@petr.meduna,

Thanks for the suggestion. I will try that.

[JBerns]

Today I found this post by @MechMachineMan :

https://forums.autodesk.com/t5/inventor-ilogic-and-vb-net-forum/ilogic-and-excel-worksheets/td-p/648... 

Not sure how I missed this old post when I was searching for help.

Justin wrote an example that uses all VB.NET code.

Initial testing on my work computer with Inventor 2022 and Excel 2016 is functioning sometimes.

By sometimes I mean that randomly the code reports the XLSX file can't be found with the Excel Open command. Not sure why.

More testing needed. Hopefully a reboot will correct the issue.

Thanks, Justin, for sharing this code!

Regards,

Jerry

[fredform]

I had the same problem on a machine with Inventor 2022 and Office365. Running the "quick repair" from the control panel solved the issue, much to my surprise! 🙂

Thanks for the advice.