Inventor does not read the correct values from excel parameters

[nvmierlo]

Dear all,

I have compiled to code below that aims to do the following thing:
-Write the values of certain parameters to certain cells in the excel file
-The excel file automatically calculates the values in two other cells
-2 parameters in Inventor should be filled with the two values from these cells.
The code does put the values from Inventor into the input cells in Excel, which then calculates the new values.
But then something strange happens, the code doesn't take the newly calculated values back to Inventor but the old ones that were previously there. If I open the worksheet between two runs it does display the newly calculated values but, somehow Inventor still takes the old ones
When I re-run the code once more, It still takes the values that were there before I first ran the code. The only solution to change the output values is to open the excel file and save the file. Saving the file via the Ilogic strong does not work.

Any help is appreciated! 

Kind regards,

Niek 

InventorVb.DocumentUpdate()
iLogicVb.UpdateWhenDone = True

Dim sFile As String = "C:\Users\niek\Documents\Map 1.xlsx"

'Writing of Inventor parameter to Excel
GoExcel.Open(sFile, "Sheet:1")
GoExcel.CellValue("F3") = "Type 1"
GoExcel.CellValue("F4") = Parameter("MA_C") 
GoExcel.CellValue("F5") = Parameter("L") 
GoExcel.CellValue("F6") = Parameter("B") 
GoExcel.CellValue("F7") = Parameter("α") 
GoExcel.CellValue("F8") = Parameter("BG") 
GoExcel.CellValue("F9") = Parameter("BS") 
GoExcel.CellValue("F10") = Parameter("BS_GO") 
GoExcel.CellValue("F11") = Parameter("AT") 

GoExcel.CellValue("F13") = Parameter("RE_AD")

GoExcel.CellValue("F17") = Parameter("BS") 
GoExcel.CellValue("F18") = Parameter("BS_GO") 
GoExcel.CellValue("F19") = Parameter("TB_MA") 

'Reading of Inventor parameters from Excel
Parameter("BN_TT") = GoExcel.CellValue("L3")
Parameter("BN_KO") = GoExcel.CellValue("L5")

GoExcel.Save
GoExcel.Close

[basautomationservices]

You should probably update the sheet before reading the values. Is there any update method? 

[nvmierlo]

Hello, 

Nope, it doesn't seem there are any. 

[nvmierlo]

I did some more digging and found the following Forum thread, I think the solution to my problem lies in this area. 

https://forums.autodesk.com/t5/inventor-ilogic-and-vb-net-forum/new-default-for-ilogic-excel-functio...

I tried to switch to the COM option in the Ilogic configuration window but it throws an error: 

[basautomationservices]

The application object gives access to the excel application. Thete you should be able to refresh the workbook/activesheet.

[nvmierlo]

I tried adding it to the code as below, that's what I get when I double click the string in the browser on the right. But it doesn't work. How should i rewrite the string? 

InventorVb.DocumentUpdate()
iLogicVb.UpdateWhenDone = True
excelApp = GoExcel.Application
Dim sFile As String = "C:\Users\niek\Documents\Map 1.xlsx"

'Writing of Inventor parameter to Excel
GoExcel.Open(sFile, "Sheet:1")
GoExcel.CellValue("F3") = "Type 1"
GoExcel.CellValue("F4") = Parameter("MA_C") 
GoExcel.CellValue("F5") = Parameter("L") 
GoExcel.CellValue("F6") = Parameter("B") 
GoExcel.CellValue("F7") = Parameter("α") 
GoExcel.CellValue("F8") = Parameter("BG") 
GoExcel.CellValue("F9") = Parameter("BS") 
GoExcel.CellValue("F10") = Parameter("BS_GO") 
GoExcel.CellValue("F11") = Parameter("AT") 

GoExcel.CellValue("F13") = Parameter("RE_AD")

GoExcel.CellValue("F17") = Parameter("BS") 
GoExcel.CellValue("F18") = Parameter("BS_GO") 
GoExcel.CellValue("F19") = Parameter("TB_MA") 

'Reading of Inventor parameters from Excel
Parameter("BN_TT") = GoExcel.CellValue("L3")
Parameter("BN_KO") = GoExcel.CellValue("L5")

GoExcel.Save
GoExcel.Close

[basautomationservices]

I see the COM option. I think this is indeed what you need. As explained in the link you posted the LibXL just reads/writes to excel files but does not actually control the application, so formulas etc will not be updated.

excelApp = GoExcel.Application

This line probably errors because there is no 'Dim' in front. Can you try again with that line removed, since it is not necessary. You are trying to assign something to this variable but it does not exist.

[nvmierlo]

Hi,
I tried to Dim excelApp as type. but that did not work either. I think I do need the line as I need to use COM libary because my sheet does perform calculations.

[basautomationservices]

You do not need the excelApp variable. It's for getting access to the API of excel. For example to set the visibility of the application. I was wrong about the 'dim' in my previous post. 

	GoExcel.Open(excelFile, "Sheet1")
	excelApp = GoExcel.Application
	excelApp.Visible =True
	GoExcel.CellValue("A1") = "Test"
	GoExcel.Save
	GoExcel.Close

[nvmierlo]

Alright, But what do I do need then to use the COM method in my code so it does calculate my output parameters, any clue?

Or maybe @MjDeck could you help?

[basautomationservices]

That should be the changing of the setting to COM.

[nvmierlo]

I have the engine set to COM the entire time, that is what gives me the error

[MjDeck]

@nvmierlo , are you still getting the "Unable to cast COM object" error that appears in Message 4 above?
If so, I think the best thing to do is to reinstall Microsoft Office. It doesn't take very long and it will probably fix the problem.

[nvmierlo]

@MjDeck 

Hi, I reinstalled Office but it did nog fix the problem. I have also the latest version of Inventor 2021 installed. 

[MjDeck]

Which version of Microsoft Office are you using?
Did you reboot after reinstalling Office?
Are you on Windows 10 or 11?
Can you try commenting out all the code that uses GoExcel.Application in your rule? It doesn't look like you need it.
If you get the "Unable to cast COM object" error again, please click on the More Info tab and post a screenshot of that.

[nvmierlo]

Hi Mike, 

I'm using Windows 10 and Office 365 Version 2204 build 15128.20178. I had already removed those parts, my current code is below. (and still does not work)

InventorVb.DocumentUpdate()
iLogicVb.UpdateWhenDone = True
Dim sFile As String = "C:\Users\niek\Documents\Map 1.xlsx"
GoExcel.Open(sFile, "Sheet:1")

GoExcel.CellValue("F3") = "Type 1"
GoExcel.CellValue("F4") = Parameter("MA_C") 
GoExcel.CellValue("F5") = Parameter("L") 
GoExcel.CellValue("F6") = Parameter("B") 
GoExcel.CellValue("F7") = Parameter("α") 
GoExcel.CellValue("F8") = Parameter("BG") 
GoExcel.CellValue("F9") = Parameter("BS") 
GoExcel.CellValue("F10") = Parameter("BS_GO") 
GoExcel.CellValue("F11") = Parameter("AT") 

GoExcel.CellValue("F13") = Parameter("RE_AD")

GoExcel.CellValue("F17") = Parameter("BS") 
GoExcel.CellValue("F18") = Parameter("BS_GO") 
GoExcel.CellValue("F19") = Parameter("TB_MA") 

Parameter("BN_TT") = GoExcel.CellValue("L3")
Parameter("BN_KO") = GoExcel.CellValue("L5")

GoExcel.Save
GoExcel.Close

[MjDeck]

Did you reboot after reinstalling Office?
If you get the "Unable to cast COM object" error again, please click on the More Info tab and post a screenshot of that.

[nvmierlo]

I did not reboot windows after the reinstall of Office. So, I just did it. After the reboot I still get the error.
Should I have rebooted Immediately after the instalment of Office or is this no problem? I have been using my computer for the past hours since.

[MjDeck]

It probably doesn't make much difference whether you rebooted immediately or later.
Thanks for posting the error message details.
I will put together a test rule that uses the Excel COM API in a different way and post it soon.

[MjDeck · Accepted solution]

Here's a very simple test rule. Please try it out. If it works for you, I can post another one that opens a worksheet and writes and reads some values.

Dim excelApp = CreateObject("Excel.Application", String.Empty)

Dim versionMsg = String.Format("Excel version : {0}", excelApp.Version)
MessageBox.Show(versionMsg, "Excel")
'Logger.Info(versionMsg)

excelApp.Quit()