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

[MjDeck]

Here's a sample part with a test rule that is similar to your rule. But instead of using the GoExcel interface, it goes directly to the Excel COM API. Please try it out. Before running the rule, save the .ipt and .xlsx files to the same folder. It uses a formula in the Excel sheet to set the value of the "result1" parameter to two times "input1".

[nvmierlo]

I tried both rules and they both work! I will no go and try to edit the last one to suit my needs. my excelfile can't be in the same folder as the inventor file, could this be a problem? 

[nvmierlo]

I expanded your code and it now works for my code! However, I would like to add the following thing, I want to read a range of cells from the excel file and put them in a Multivalue parameter in Excel. I've tried the snippet below and some variantions but, thus far know luck. 

MultiValue.List("MR") = excelSheet.Range("L2:L10").Value2

[MjDeck]

Here's a modified version of the sample part and Excel file. This has a function to read a range of values and put it in a multi-value list. Like the GoExcel.CellValues function, this will stop at an empty cell. So you can give it a range that is a bit larger than the number of values that you actually have.

[nvmierlo]

That works perfectly! thank you for your help

[harvey_craig2RCUH]

Apologies, no need to reply. When I wrote this it only loaded up to message 20. After I posted it showed me the rest and the posts and the code inside message 24's part did exactly as I need. (I didn't realise the forum only loaded 20 messages at a time).

Cheers again, ignore below,

Harvey

Hi @MjDeck ,

I found this thread very useful and I am having exactly the same issue as nvmierlo. I also require the COM engine as I need my spreadsheet to make calculations before sending the numbers back. And I get an almost identical error to them:

Can you write that code where it opens a workbook, writes, reads, saves then quits? My Intellisense didn't pick up anything when I typed excelApp. after the object was created?

Many thanks,

Harvey