Problem extracting Excel data (seems not updated?)

[Daan_M]

Hi,

I made an iLogic code that does the following:

Sub Main

oExDoc = "C:\Data\ExDoc.xlsx"
oSheet1 = "Sheet1"
oSheet2 = "Sheet2"

GoExcel.Open(oExDoc, oSheet1)
GoExcel.Open(oExDoc, oSheet2)

Dim i As Integer = 1
Dim CellValue As String

GoExcel.CellValue(oExDoc, oSheet1, "A1") = i

GoExcel.Save
GoExcel.Close

GoExcel.Open(oExDoc, oSheet1)
GoExcel.Open(oExDoc, oSheet2)

CellValue = GoExcel.CellValue(oExDoc, oSheet2, "A1")
MsgBox(CellValue)
GoExcel.Close

End Sub

In the Exceldocument i have a Excelfunction that makes Cell A1 on Sheet2 is equal to Cell A1 on Sheet1, then adds 1...

(Cell A1 on Sheet2; =Sheet1!A1+1

So if A1 on Sheet1 = 1

Then A1 on Sheet2 should be: 2

The MessageBox However shows '1', which is not correct, Excel seems to not update or save?

Because when i open the Excel Document manually cell A1 on Sheet2 is; 2 as it is supposed to be

[WCrihfield]

It seems you might be having trouble interacting with Excel the way you want to.  If you access it another way, you may be able to do more with it, then maybe not have to use the text file later.

Here's something I threw together for you, to export your parameters to an Excel file in perhaps a different way.

See if this works better for you.

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel

Dim ExLoc As String = "C:\Data\TestExcel.xlsx"
	
Dim oExcelApp As New Microsoft.Office.Interop.Excel.ApplicationClass
oExcelApp.DisplayAlerts = False
oExcelApp.Visible = True
Dim oWB As Workbook = oExcelApp.Workbooks.Open(ExLoc)
Dim oWS1 As Worksheet = oWB.Sheets.Item("Sheet1")
Dim oCells1 As Range = oWS1.Cells
'Dim oWS2 As Worksheet = oWB.Sheets.Item("Sheet2")
'Dim oCells2 As Range = oWS2.Cells

'Create column headers, if wanted
'First number is Row index, second number is Column Index
oCells1.Item(1, 1).Value = "NAME"
oCells1.Item(1, 2).Value = "VALUE"

'Start writing data on row 2
Dim i As Integer = 2
'assuming this document is either a part or assembly document (needs check)
For Each oUParam As UserParameter In ThisDoc.Document.ComponentDefinition.Parameters.UserParameters
	oCells1.Item(i,1).Value = oUParam.Name '<<<Replace This With Actual Name
	oCells1.Item(i, 2).Value =  oUParam.Value '<<<Replace This Width Actual Value
	i = i + 1
Next
oWS1.Columns.AutoFit

oWS1 = Nothing
oWB.Save
oWB.Close
oWB = Nothing
oExcelApp.Quit
oExcelApp = Nothing

[Daan_M]

Hi @WCrihfield, i think you're right, the interaction with Excel is different from what i assumed it to be.

I appreciate the answer, for this case however i don't think i can make it work. I simplefied the code and Excelfile to the most basic level understand how to solve the problem. In reality this Excelfile is quite complex and can have thousands of referenced cells, the iLogic uses some loops aswell to place the paramaters i have and i can't really adjust to form you're offering. I need another way to acces Excel or get the result i want, without having to redo my code entirely

I don't understand why saving, then closing, then opening the file via iLogic isn't doing the trick

[WCrihfield]

Saving and closing a file, then trying to immediately open that file again definitely sounds like a case where you would have to wait a while between the closing and reopening, to allow processing time.  Especially if some (any) of the files involved may be on a network location, or open by multiple apps (processes).  I know there is a way to wait for process to finish, before doing the next thing (other than just a dumb thread.wait technique).  I think it looks something like oProcess.WaitForExit(oInteger), but I can't remember at this moment.  Actually, I think you can leave the integer variable out (not specify it) and it will wait as long as necessary.

[J-Camper · Accepted solution]

@Daan_M,

What release of Inventor are you using?  According to this forum post 2021 Inventor interacts with Excel differently. 

If you are in an earlier release [2020 or earlier], there may be some other issue.

[Daan_M]

@WCrihfield @J-Camper , i'm using inventor 2021.

I did some testing and concluded the following:

• Theading for a certain time does not solve the problem, it's not time related.
• To get the right data out of Excel i must preform the following 2 actions manually:
  • Open the Excelfile manually and save the opened file manually (if i open the Excel manually after running my code i see the correct data is there, but if i close it again without saving, it still outputs wrong data, which i find strange....)

Test

Sheet 1, A1 = 0 in my Excelfile

*Run code*

Output message = 1  (1 is wrong, it is supposed to be 2)

Open file manually and save it, then close file again

*Run code*

Output message = 2 (which is correct)

[JhoelForshav]

@Daan_M 

And you are using the COM-engine for excel?

[Daan_M]

Our server for inventor does not have Excel installed so it was set default to Internal Inventor Engine, COM was not available. I'm assuming this will solve it, thanks for the help @J-Camper for the article link, would have never figured this out myself.