ilogic code to import workpoint coordinates from excel document

[alan.wedge]

Hi All,

im trying use an excel document to list coordinates gathered from navisworks and import them into inventor as workpoints (or UCS). i have found individual resources that cover parts of the solution but im not skilled enough to bring it together.

https://forums.autodesk.com/t5/inventor-ilogic-and-vb-net-forum/how-do-i-add-a-workpoint-at-an-xyz-c...

https://www.cadlinecommunity.co.uk/hc/en-us/articles/7617610993949-Reading-information-from-excel-sp...

i am trying to use a excel document like this:

to generate something like this (i made this by manually typing in the coordinates):

the excel document is in the same file path as the part. naming the points would be nice but its really not critical.

thanks

[WCrihfield · Accepted solution]

Hi @alan.wedge.  There are several ways to do this task, but below is the code for an iLogic rule I created to match the data you have shown in the image you posted.  I created a new Excel file, put that exact data into it, in the same locations, saved it, then ran this code from a part document I had open.  It created all 3 WorkPoints, and named them according to the names provided in the Excel sheet.  I've got it set-up so that you can either browse for the Excel file, or just 'hardcode' the file's path directly into the rule.  To change which way it works, just change which row is commented out, and change the file path if needed.  You may also need to change the name of the sheet, but that name is the default one in English for my system.  I am just using the iLogic shortcut snippets for accessing Excel (GoExcel), since this is a fairly simple task.

Sub Main
	Dim oDoc As Document = ThisDoc.Document
	Dim oWPoints As WorkPoints = oDoc.ComponentDefinition.WorkPoints
	Dim oCoords As New List(Of IList)
'	Dim oFile As String = BrowseForFile 'uses the custom Function below
	Dim oFile As String = "C:\Temp\WorkPoint Coords.xlsx"
	If String.IsNullOrEmpty(oFile) Then Exit Sub
	Dim oSheet As String = "Sheet1"
	GoExcel.Open(oFile, oSheet)
	GoExcel.DisplayAlerts = False
	GoExcel.TitleRow = 1
	GoExcel.FindRowStart = 2
	For oRow As Integer = 2 To 4
		Try
			oCoords.Add(GoExcel.CellValues("A" & oRow, "D" & oRow))
		Catch
			'Logger.Error("Error retrieving data from Excel.")
		End Try
	Next
	If oCoords.Count = 0 Then Exit Sub
	Dim oTG As TransientGeometry = ThisApplication.TransientGeometry
	For Each oList In oCoords
		Dim oPoint As Point = oTG.CreatePoint(oList(0), oList(1), oList(2))
		Dim oWP As WorkPoint = oWPoints.AddFixed(oPoint, False)
		oWP.Name = oList(3)
	Next
	GoExcel.Close
	GoExcel.QuitApplication
End Sub

Function BrowseForFile(Optional oInitialDirectory As String = vbNullString) As String
	Dim oFileDialog As Inventor.FileDialog
	ThisApplication.CreateFileDialog(oFileDialog)
	oFileDialog.DialogTitle = "Select an Excel file with point coordinates."
	If String.IsNullOrEmpty(oInitialDirectory) Then
		oFileDialog.InitialDirectory = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath
	Else : oFileDialog.InitialDirectory = oInitialDirectory : 	End If
	oFileDialog.Filter = "Excel Files (*.xls;*.xlsx;*.xlsm) |*.xls;*.xlsx;*.xlsm | All files (*.*)|*.*"
	oFileDialog.MultiSelectEnabled = False
	oFileDialog.OptionsEnabled = False
	oFileDialog.InsertMode = False
	oFileDialog.CancelError = True
	Try : oFileDialog.ShowOpen : 	Return oFileDialog.FileName
	Catch : Return "" : End Try
End Function

If this solved your problem, or answered your question, please click ACCEPT SOLUTION .
Or, if this helped you, please click (LIKE or KUDOS) 👍.

[A.Acheson]

I will drop in this link for UCS by Transform Method as discussed on the Inventor Forum.

[alan.wedge]

thank you so much for the help. your code works perfectly.

[alan.wedge]

just to add some edits i have made, the below code lets you pick the first and last rows to import so you can add coordinates to the same document and just import the new rows.

Sub Main
	Dim oDoc As Document = ThisDoc.Document
	Dim oWPoints As WorkPoints = oDoc.ComponentDefinition.WorkPoints
	Dim oCoords As New List(Of IList)	
	Dim oFile As String = BrowseForFile 'uses the custom Function below
	If String.IsNullOrEmpty(oFile) Then Exit Sub
	Dim oSheet As String = "Sheet1"
	GoExcel.Open(oFile, oSheet)
	GoExcel.DisplayAlerts = False
	GoExcel.TitleRow = 1
	GoExcel.FindRowStart = 2
	Dim Minrow As Integer = InputBox("Enter first row number", "Min Row", "First Row")
	Dim Maxrow As Integer = InputBox("Enter final row number", "Max Row", "Last Row")
	For oRow As Integer = Minrow To Maxrow
		Try
			oCoords.Add(GoExcel.CellValues("A" & oRow, "D" & oRow))
		Catch
			'Logger.Error("Error retrieving data from Excel.")
		End Try
	Next
	If oCoords.Count = 0 Then Exit Sub
	Dim oTG As TransientGeometry = ThisApplication.TransientGeometry
	For Each oList In oCoords
		Dim oPoint As Point = oTG.CreatePoint(oList(0), oList(1), oList(2))
		Dim oWP As WorkPoint = oWPoints.AddFixed(oPoint, False)
		oWP.Name = oList(3)
	Next
	GoExcel.Close
	GoExcel.QuitApplication
End Sub

Function BrowseForFile(Optional oInitialDirectory As String = vbNullString) As String
	Dim oFileDialog As Inventor.FileDialog
	ThisApplication.CreateFileDialog(oFileDialog)
	oFileDialog.DialogTitle = "Select an Excel file with point coordinates."
	If String.IsNullOrEmpty(oInitialDirectory) Then
		oFileDialog.InitialDirectory = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath
	Else : oFileDialog.InitialDirectory = oInitialDirectory : 	End If
	oFileDialog.Filter = "Excel Files (*.xls;*.xlsx;*.xlsm) |*.xls;*.xlsx;*.xlsm | All files (*.*)|*.*"
	oFileDialog.MultiSelectEnabled = False
	oFileDialog.OptionsEnabled = False
	oFileDialog.InsertMode = False
	oFileDialog.CancelError = True
	Try : oFileDialog.ShowOpen : 	Return oFileDialog.FileName
	Catch : Return "" : End Try
End Function