Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.

Browse For Excel File

estringer
Advocate

Browse For Excel File

estringer
Advocate
Advocate

We have iLogic that uses an Excel file to populate iProperties of Part files. There will be different Excel workbooks for each of our projects. I was wondering if it is possible to put code in that would allow the user to browse to the file location and then that would fill/replace the location within the code. This would allow us to use the same template for all of our projects rather than making project specific templates where the file location has to be changed in the Ilogic.  I have included the current code below. 

 

Dim AssyDoc As AssemblyDocument = ThisDoc.Document
Dim oAsmCompDef As AssemblyComponentDefinition = AssyDoc.ComponentDefinition

Dim oDocFile As Document, oDocFileName As String

For Each oOcc  As ComponentOccurrence In oAsmCompDef.Occurrences
	If Not TypeOf oOcc.Definition Is VirtualComponentDefinition Then
		On Error Resume Next
		oDocFile = oOcc.Definition.Document
		oDocFileName = oDocFile.displayname
		'Set your delimiter
		Delimiter = InStr(oDocFileName, "-")
		Description = Left(oDocFileName, Delimiter - 1)
				
		'Open the Excel file
		GoExcel.Open("C:\Users\estringer\Documents\Work\Inventor_modeling\iLogic\Unit_Drawing_Tests\Part_iProperties.xlsx", "Parts")
					
		'Look at the first 15 rows of the Excel file
		'Start at row 2, since row 1 contains headings
		For rowCheck = 2 To 15
			'Compare the part name to the value in column A
    		If GoExcel.CellValue("A" & rowCheck) = Description Then
       			iProperties.Value(oDocFileName, "Custom", "Extrusion") = GoExcel.CellValue("B" & rowCheck)      
       			iProperties.Value(oDocFileName, "Custom", "Alloy-Temper") = GoExcel.CellValue("C" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Material") = GoExcel.CellValue("D" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Finish") = GoExcel.CellValue("E" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Color") = GoExcel.CellValue("F" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Hydro Code") = GoExcel.CellValue("G" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Routing") = GoExcel.CellValue("H" & rowCheck)
       			iProperties.Value(oDocFileName, "Project", "Description") = GoExcel.CellValue("I" & rowCheck)
			End If
		Next

		'Open the Excel file
		GoExcel.Open("C:\Users\estringer\Documents\Work\Inventor_modeling\iLogic\Unit_Drawing_Tests\Part_iProperties.xlsx", "Infills")

		'Look at the first 15 rows of the Excel file
		'Start at row 2, since row 1 contains headings
		For rowCheck = 2 To 15
			'Compare the part name to the value in column A
    		If GoExcel.CellValue("A" & rowCheck) = Description Then
       			iProperties.Value(oDocFileName, "Custom", "Routing") = GoExcel.CellValue("B" & rowCheck)      
       			iProperties.Value(oDocFileName, "Project", "Description") = GoExcel.CellValue("C" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Make-up") = GoExcel.CellValue("D" & rowCheck)
			End If
		Next
	End If
Next oOcc

GoExcel.close

 

0 Likes
Reply
Accepted solutions (1)
638 Views
2 Replies
Replies (2)

JhoelForshav
Mentor
Mentor
Accepted solution

Hi @estringer 

Are you looking for something like this?

Dim AssyDoc As AssemblyDocument = ThisDoc.Document
Dim oAsmCompDef As AssemblyComponentDefinition = AssyDoc.ComponentDefinition
Dim oDocFile As Document, oDocFileName As String
Dim oFileDlg As Inventor.FileDialog
ThisApplication.CreateFileDialog(oFileDlg)
oFileDlg.Filter = "Microsoft Excel Files (*.xlsx; *.xls)|*.xlsx;*.xls|"
oFileDlg.ShowOpen()
If oFileDlg.FileName = "" 
	MessageBox.Show("No excel file selected", "Excel file", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
	Exit Sub
End If
Dim oExcelFile As String = oFileDlg.FileName

For Each oOcc  As ComponentOccurrence In oAsmCompDef.Occurrences
	If Not TypeOf oOcc.Definition Is VirtualComponentDefinition Then
		On Error Resume Next
		oDocFile = oOcc.Definition.Document
		oDocFileName = oDocFile.displayname
		'Set your delimiter
		Delimiter = InStr(oDocFileName, "-")
		Description = Left(oDocFileName, Delimiter - 1)
				
		'Open the Excel file
		GoExcel.Open(oExcelFile, "Parts")
					
		'Look at the first 15 rows of the Excel file
		'Start at row 2, since row 1 contains headings
		For rowCheck = 2 To 15
			'Compare the part name to the value in column A
    		If GoExcel.CellValue("A" & rowCheck) = Description Then
       			iProperties.Value(oDocFileName, "Custom", "Extrusion") = GoExcel.CellValue("B" & rowCheck)      
       			iProperties.Value(oDocFileName, "Custom", "Alloy-Temper") = GoExcel.CellValue("C" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Material") = GoExcel.CellValue("D" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Finish") = GoExcel.CellValue("E" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Color") = GoExcel.CellValue("F" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Hydro Code") = GoExcel.CellValue("G" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Routing") = GoExcel.CellValue("H" & rowCheck)
       			iProperties.Value(oDocFileName, "Project", "Description") = GoExcel.CellValue("I" & rowCheck)
			End If
		Next

		'Open the Excel file
		GoExcel.Open(oExcelFile, "Infills")

		'Look at the first 15 rows of the Excel file
		'Start at row 2, since row 1 contains headings
		For rowCheck = 2 To 15
			'Compare the part name to the value in column A
    		If GoExcel.CellValue("A" & rowCheck) = Description Then
       			iProperties.Value(oDocFileName, "Custom", "Routing") = GoExcel.CellValue("B" & rowCheck)      
       			iProperties.Value(oDocFileName, "Project", "Description") = GoExcel.CellValue("C" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Make-up") = GoExcel.CellValue("D" & rowCheck)
			End If
		Next
	End If
Next oOcc

GoExcel.close

I used Inventors own FileDialog here. If you want to customize it more you can use system.windows.forms.OpenFileDialog.

 

Hope this helps 🙂

 

estringer
Advocate
Advocate

Thank you for the help, that was exactly what I was looking for!