Ilogic fails to talk to Excel

[liminma8458]

Hi,

I am using Inventor 2016, Excel 2013, windows 7 64 bit.

I have very simple iLogic rule:

iwood_supp_thk=GoExcel.CellValue("cc_data.xls", "Sheet1", "wood_supp_thk").

It works well for all other machines. But it fails in one particular machine. The message says:

Error in rule: block4_scenario3, in document: block4_scenario3.ipt

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Application'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).

Looks like the iLogic does not talk to Excel correctly. I re-install Inventor and Excel a couple of times, and use the sequence of installing Office first then Inventor secondly.

Please see attached file for the error message.

What is it? Can somebody help?

Thank you very much!

Limin

[ADSKDJW4]

Hello @liminma8458,

I will ask the forum moderator to move this post to the Inventor Customization Forum where this question is better served.
Best Regards,
Don

[Anonymous]

Hello,

I am running into the same issue, I wrote an iLogic rule using Inventor 2015 w/ excel 2010 which worked without any issues. Upgraded Inventor to 2016 (Excel still 2010) and now Inventor can't seem to talk to Excel. Help would be greatly appreciated.

Thanks,

'Funnybus

Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).

SyntaxEditor Code Snippet

oExcelPath = "G:\FSH_Engineering\ENGINEERING DRAWINGS\_DRAWING TEMPLATES\_INVENTOR\_START PARTS\UTILITY\GAUGE.xlsx"

Dim oListGauge As New ArrayList
Dim oListThick As New ArrayList

Dim oListHolder As String

Dim i As Integer

For i = 1 To 50 Step 1
    Dim oValGauge As String = GoExcel.CellValue(oExcelPath, "Sheet1", "A" & i)
    Dim oValThick As Double = GoExcel.CellValue(oExcelPath, "Sheet1", "B" & i)
    Dim sValThick As String = oValThick.ToString("0.0000")
        If oValThick = 0 Then
            'do nothing
        Else
            oListGauge.Add(oValGauge)
            oListThick.Add(sValThick)
            oListHolder = oListHolder & sValThick & " // " & oValGauge & vbLf
        End If
Next

'MessageBox.Show(oListHolder, "List of Gauge & Thickness")

MultiValue.List("GAUGE") = oListGauge
MultiValue.List("THICKNESS_GAUGE") = oListThick

Fails at first instance of GoExcel.CellValue 

[MechMachineMan]

Try this in a rule and report back to how it runs:

AddReference "Microsoft.Office.Interop.Excel" 'To use excel

Sub Main()

	oExcelPath = "G:\FSH_Engineering\ENGINEERING DRAWINGS\_DRAWING TEMPLATES\_INVENTOR\_START PARTS\UTILITY\GAUGE.xlsx"
	oSheetName = "Sheet1" 
	
	Dim xlApp As Microsoft.Office.Interop.Excel.Application 
	xlApp = GetObject("", "Excel.Application") 
	Dim xlwb As Microsoft.Office.Interop.Excel.WorkBook 
	xlwb = xlApp.Workbooks.Open(oExcelPath)
	Dim xlws As Microsoft.Office.Interop.Excel.Worksheet
	xlws = xlwb.Worksheets(oSheetName)
	
	MsgBox("Cell A1 is: " & xlws.Cells(1, "A").Value)
	
	xlwb.Close(SaveChanges:= False)
	xlApp.Quit
	xlApp = Nothing

End Sub 

[Owner2229]

Hey, it crashed due to an error in loading of the Excell interop library, so I don't think adding the reference manually will resolve this.

However you can still use Excel, even with broken library connection (thanks to the late binding). It's similar to what MechMachineMan suggested, except you won't cast the object to Excel types, but use them as objects.

Sub Main()
	Dim oExcelPath As String = "G:\FSH_Engineering\ENGINEERING DRAWINGS\_DRAWING TEMPLATES\_INVENTOR\_START PARTS\UTILITY\GAUGE.xlsx"
	
	Dim oExcel As Object = CreateObject("Excel.Application")
	oExcel.Visible = False
	oExcel.DisplayAlerts = False
	Dim oWB As Object = oExcel.Workbooks.Open(oExcelPath)
	Dim oWS As Object = oWB.Sheets(1) 'Sheet 1
	oWS.Activate()

	'In this sample, when you're getting a cell, the row is first
	MsgBox("Cell A2 is: " & oWS.Cells(2, 1).Value)
	
	'oWB.Save() 'Use this to save it
	oWB.Close (True)
	oExcel.Quit
	oExcel = Nothing
End Sub 

[Anonymous]

This definitely worked. If you have time, I am very curious as to why GoExcel didn't work and casting it as an object did. Thanks!

[Owner2229]

"GoExcel" just like "Microsoft.Office.Interop" uses the Office library file (DLL) to control Excel.

It looks into registers and gets the DLL location from there. Sometimes it may happen (somehow) the location stored in registers becomes invalid.

It might be due to poor un/instalation of the program or just some kind of error.

The sample I gave you "CreateObject("Excel.Application")" is also using registers, but it's not looking for the Interop library but rather directly at the registered Excel application. If you'll instal multiple Office versions the last runned one will be the one that will open using this code.

[Anonymous]

Interesting, thanks for the quick responses!

[liminma8458]

I recall that after exhausting all other effort, I solved the problem by re-install windows 7 prof, then Office 2013 prof then Inventor 2016 prof for that particular machine.  I recommend to keep software in your machine as lean as possible and do not install two versions of any of these program.  Too many add on will complicate the communication between these 3 software.

Thanks

[andy-axium]

I Have a very similar problem with some ilogic that is used to refresh values from an Excel list.

Anyone know what command is needed to replace the GoExcel statement?

This is the code in question.

SyntaxEditor Code Snippet

MultiValue.List("myFinishExt") = GoExcel.CellValues("I:\Templates 2018\myFinishExt.xlsx", "Sheet1", "A2", "A100")
MultiValue.List("myFinishInt") = GoExcel.CellValues("I:\Templates 2018\myFinishInt.xlsx", "Sheet1", "A2", "A100")
MultiValue.List("myMaterial") = GoExcel.CellValues("I:\Templates 2018\myMaterial.xlsx", "Sheet1", "A2", "A100")

I've used the solution from Owner2229
which successfully calls Excel, but I'm not sure what to use in place of GoExcel. to get my values?

Sub Main()
	Dim oExcelPath As String = "I:\Templates 2018\myFinishExt.xlsx"
	
	Dim oExcel As Object = CreateObject("Excel.Application")
	oExcel.Visible = False
	oExcel.DisplayAlerts = False
	Dim oWB As Object = oExcel.Workbooks.Open(oExcelPath)
	Dim oWS As Object = oWB.Sheets(1) 'Sheet 1
	oWS.Activate()

	'In this sample, when you're getting a cell, the row is first
	MsgBox("Cell A2 is: " & oWS.Cells(2, 1).Value)
	
	'oWB.Save() 'Use this to save it
	oWB.Close (True)
	oExcel.Quit
	oExcel = Nothing
End Sub 

[Owner2229]

You can use a function like this:

Sub Main()
	MultiValue.List("myFinishExt") = ReadValues("I:\Templates 2018\myFinishExt.xlsx", 1, 1, 2, 100)
	MultiValue.List("myFinishInt") = ReadValues("I:\Templates 2018\myFinishInt.xlsx", 1, 1, 2, 100)
	MultiValue.List("myMaterial") = ReadValues("I:\Templates 2018\myMaterial.xlsx", 1, 1, 2, 100)
End Sub

Private Function ReadValues(sExcelPath As String, Sheet As Integer, Column As Integer, StartRow As Integer, EndRow As Integer) As String()
	Dim R() As String
	Try
		Dim Range As Integer = Abs(EndRow - StartRow)
		Redim R(Range)
		Dim oExcel As Object = CreateObject("Excel.Application")
		oExcel.Visible = False
		oExcel.DisplayAlerts = False
		Dim oWB As Object = oExcel.Workbooks.Open(sExcelPath)
		Dim oWS As Object = oWB.Sheets(Sheet)
		oWS.Activate()
		Dim j As Integer = 0
		For i As Integer = StartRow To EndRow
			R(j) = oWS.Cells(i, Column).Value
			j += 1
		Next
		oWB.Close (True)
		oExcel.Quit()
		oExcel = Nothing
	Catch
	End Try
	Return R
End Function

[andy-axium]

Thanks will give this a try

[andy-axium]

Owner2229, thanks for your code however when running it errors with "Object reference not set to an instance of an object"

By adding messagebox comments it seems that the code run until the Catch statement?

Further assistance would be greatly appreciated

[andy-axium]

Just an update on the iLogic GoExcel error, after AutoDesk Support remoted on to my m/c with TeamViewer, the ilogic error was cured!

[chrisw01a]

Andy, what did they do to fix it? I am trying to fix this same issue...

I tried this but now when I try to open the file to edit it, excel just blinks on and off and I cannot do anything with the file:

Dim oExcelPath As String = "\\server1\Inventor\Job\Create Projects.xlsx"
	
	Dim oExcel As Object = CreateObject("Excel.Application")
	oExcel.Visible = False
	oExcel.DisplayAlerts = False
	Dim oWB As Object = oExcel.Workbooks.Open(oExcelPath)
	Dim oWS As Object = oWB.Sheets(1) 'Sheet 1
	oWS.Activate()

	'In this sample, when you're getting a cell, the row is first
	MsgBox("Cell A2 is: " & oWS.Cells(2, 1).Value)

'look at the rows A2 thru A50, adjust as needed
'note blank rows are ok
'Dim oList As New ArrayList
'oList = oWS.Cells(2, 1).Value

'' Set a reference to the DesignProjectManager object.
'Dim oDesignProjectMgr As DesignProjectManager
'oDesignProjectMgr = ThisApplication.DesignProjectManager
'Dim oProject As DesignProject

'For Each oNumber In oList
'	' Create a new single user project	
'	oProject = oDesignProjectMgr.DesignProjects.Add(kSingleUserMode, _
'		oNumber, oPath & oNumber & "\")
'Next

	oWB.Close (True)
	oExcel.Quit
	oExcel = Nothing

[MechMachineMan]

Have you verified the code works with .visible and .displayalerts both set to true?

[chrisw01a]

It does work but it still does the same thing. Seems like it is locking the file and not letting go of it or something.

[Curtis_Waguespack]

Hi @chrisw01a,

You might check the Task Manager ( ctrl + alt + delete) > Details tab, and see if you see a bunch of EXCEL.EXE tasks listed. If so kill them.

Often when I'm working on something like this, as I hit errors in the iLogic testing it abandons invisible Excel files and leaves them open because the code errors out before being able to close them. Really, a proper error handler to catch the errors and close the file would be best.

In any case, I think I've seen the behavior you mentioned when the file is open invisibly in the background, but I can't recall for sure. It's something to rule out though.

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

[chrisw01a]

You were correct. There were a bunch of Excels running. I'll try closing the file during the "catch" operation and see if that will solve it when an error is encountered.