Read & Write (Vice versa) Excel Parameters in inventor ilogic form

[s.barge]

Hello all ! My Name is Ranjeet Navale. I am looking for a solution for subjected requirement.

Excel parameters has been linked to inventor assembly which drives certain aspects of it like Length, Width etc.

ilogic rule sets for variations of model. Idea is to create iLogic form which will fetch those excel parameters from data sheet and controlls the assembly in place. I wish end user need not open excel sheet (Mostly it will be at safe place somewhere on network drive).

Crucial part - parameters could be change from excel sheet and ilogic form also. (Say if user change it through iLogic form then same input values should be reflected in excel sheet also. And

If values changes through excel sheet then same should be reflects in iLogic form.

In short, requirement is like parameter valus should be Read-Write-Read format.

Is it possible for now. Using Autodesk Inventor Professional 2022.

Thank you for your time.

[TechInventor20]

Dim Datasheet As String = "C:\Desktop\Yourexcelfile.xlsm"
Dim Sheet As String = "Sheet1"
Dim Row As Integer = 1


Parameter("Excelvalue_1") = GoExcel.CellValue(Datasheet, Sheet, "A" & Row) 

iLogicForm.Show("Form_with_Excelvalues")
Parameter.UpdateAfterChange

If GoExcel.CellValue(Datasheet, Sheet, "A" & Row) <> Parameter("Excelvalue_1") Then
	GoExcel.CellValue(Datasheet, Sheet, "A" & Row) = Parameter("Excelvalue_1")
Else
	'Nothing
End If

[s.barge]

Thanks TechInventor20 for your prompt reply. I tried your ilogic code. It works partially for my problem.

As there are only one way data transfer through this. I can't get values in excel sheet from iLogic form.  main objective for this discussion is user need not open the excel sheet as well as assembly should be updated from both way With excel parameter values as well as with iLogic form (And offcourse form values are nothing but ultimately the excel values which linked through iLogic code.)

I have read that we can push / write values to excel sheet through iLogic code, However looking for solution of this overall discussion.

           Still the question remains here - Is it possile? and if yes then how much amount of time or efforts we have to take for it.

Any help in this topic will be most appriciated. Thanks once again TechInventor20 for your suggestion.

[TechInventor20]

You could look at this:

option 1

and this one

option 2 

And here is some code that could possibly work. But i get an error message unfortunatly...

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel
Sub Main
Dim oXLFile As String = "C:\Users\Desktop\Map1.xlsx"
Dim oSheet As String = "Sheet1"
Dim oExcel As New Microsoft.Office.Interop.Excel.ApplicationClass
Dim Row As Integer = 1

oExcel.displayalerts = False
oExcel.Visible = False

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

Dim oCells As Range = oWS.Cells
'[ Here is some code to use...
''Assuming Row 2 is first row of data
'Dim oLine2 As String = oCells.Item(2, 1).Value
'Dim oLine3 As String = oCells.Item(3, 1).Value
'Dim oLine4 As String = oCells.Item(4, 1).Value
'Dim oLine5 As String = oCells.Item(5, 1).Value
'Dim oLine6 As String = oCells.Item(6, 1).Value
'Dim oLine7 As String = oCells.Item(7, 1).Value
'Dim oLine8 As String = oCells.Item(8, 1).Value
'Dim oLine9 As String = oCells.Item(9, 1).Value
'Dim oLine10 As String = oCells.Item(10, 1).Value
'Dim oLine11 As String = oCells.Item(11, 1).Value
']

Parameter("Excelvalue_1") = oCells.Item(Row, 1).Value

iLogicForm.Show("Form_with_Excelvalues")

If oCells.Item(Row, 1).Value <> Parameter("Excelvalue_1") Then
oCells.Item(Row, 1).Value = Parameter("Excelvalue_1")
Else
'Nothing
End If

oWB.Close(False)
oExcel.Quit
oCells = Nothing
oWS = Nothing
oWB = Nothing
oExcel = Nothing
End Sub

[TechInventor20]

Just searching in my own vb.net coding and found some snippet i used for excel.

It works fine for me in ilogic.

Please give it a go and if there are issues let me know.

Imports Microsoft.Office.Interop

Dim xls = CreateObject("Excel.Application")
Dim wkb1 As Object
Dim wks1 As Object

wkb1 = xls.Workbooks.Open("C:\Users\Thomas\Desktop\Map1.xlsx")
wks1 = wkb1.Sheets("Blad1")

Parameter("Excelvalue_1") = wks1.cells(1, 1).Value

iLogicForm.show("Form_with_Excelvalues")
MsgBox(Parameter("Excelvalue_1"))

MsgBox(wks1.cells(1, 1).Value)
If wks1.cells(1, 1).Value <> Parameter("Excelvalue_1") Then
wks1.cells(1, 1).Value = Parameter("Excelvalue_1")
Else
'Nothing
End If
wkb1.save
wkb1.Close(False)
xls.Quit

[s.barge]

Hello  @TechInventor20 

I like to thank you for your suggestions. I tried it for the solution but I did't get required results. May be I am missing something from my side.

I am unable to push iLogic form values to Excel cells (Excel values can be pulled to iLogic form values). 

I am uploading sample files herewith for review. Hope it will helpful to clear the subjected issue.

Once again Thank you for your time & efforts.

[TechInventor20 · Accepted solution]

Imports Microsoft.Office.Interop
Dim xls = CreateObject("Excel.Application")
Dim wkb1 As Object
Dim wks1 As Object
iLogicForm.Show("LinkedExcelParam")
wkb1 = xls.Workbooks.Open("Y:\Engineering\Conveyor\BMSTD-S5-Conveyor\2022.08.03_Auto Assly Generation with iLogic\Lengthwise Assembly_iLogic\BlockAssemblyTrial\ExcelParameterlink1.xlsx")
wks1 = wkb1.Sheets("Sheet1")
Parameter("LNTH") = wks1.cells(1, 2).Value
Parameter("Width") = wks1.cells(2, 2).Value
Parameter("Flange") = wks1.cells(3, 2).Value

iLogicForm.Show("LinkedExcelParam")

If wks1.cells(1,2).Value <> Parameter("LNTH") Then
wks1.cells(1,2).Value = Parameter("LNTH")
Else
'Nothing
End If

If wks1.cells(2,2).Value <> Parameter("Width") Then
wks1.cells(2,2).Value = Parameter("Width")
Else
'Nothing
End If

If wks1.cells(3,2).Value <> Parameter("Flange") Then
wks1.cells(3,2).Value = Parameter("Flange")
Else
'Nothing
End If

wkb1.save
wkb1.Close(True)
xls.Quit

Put that code into Excel linking. Furthermore you could automate that you can search for the parameters in excel so you don't have to type everything out if you add a parameter.