import ucs or 3d points from excel

[alan.wedge]

hi all,

i really could do with a way of generating UCS or work points faster than manually typing in coordinates, import from excel would be ideal.

i have to create a model that connects to multiple places on a clients navisworks model. the only good data i can collect from navisworks is the xyz coordinates. i am able to manually type it out in inventor but this is very long winded. i would like to be able to generate these coordinates from an excel document to make life a little easier.

the clients model is 500000 meters from zero so i cant use a 3d sketch as the geometry becomes completely unusable at that distance. work features are able to be brought back to zero in a parent assembly without issue.

[Gabriel_Watson]

https://www.cadstudio.cz/en/apps/importcoord/

As seen here:
https://www.cadforum.cz/en/how-to-import-named-workpoints-from-excel-to-inventor-tip9788

Also tagging the genius @A.Acheson, who might just happen to have a snippet in Inventor API for this...

[alan.wedge]

i tried this earlier today,unfortunately it doesn't appear to work in inventor 2023. i have tried every combination of being in and out of 2d and 3d sketches using the sample document and my own document.

any ideas?

[A.Acheson]

Hi @alan.wedge 

Without seeing the source code behind that error there isn't really any advice that can be given. Do you have the code that is in the vba editor and can you post it ? 

In addition maybe try these samples here to create 

Createworkpoint(ilogic)

Create UCS(VBA API Sample)

I would be interested if there is a way to pick up the ucs of the navismodel within inventor. This way you don't need to use excel as the medium to pass the coordinates. In addition, I would have this post moved over to the ilogic/VBA forum as well as you might get better assistance in troubleshooting. 

@Gabriel_Watson, I just know where to look and what to look for, kudos to the creators putting the information there in the first place.  

[alan.wedge]

thanks for the reply

importcoord's vba project is password protected so i cant share the code. im very new to ilogic and vba and have no idea how to read and write code samples you linked.

[A.Acheson]

Hi @alan.wedge 

The ilogic workpoint is simply a matter of copy paste and run. If it produces a work point it worked. Just remember vba and ilogic are different code editors. What geometry do you want to work with a workpoint or ucs? 

[alan.wedge]

both workpoint and UCS work as they are work features and not sketch geometry. ideally i want to import from an excel document or something like that.

the idea is i can copy a load of coordinates from navisworks and import the entire set into inventor instead of manually typing every coordinate.

this is one i created manually. it was time consuming but its working great. i want to speed its creation up.

[A.Acheson]

The below code is converted from VBA API sample and will add 1 UCS us8ng the ilogic environment. It hasn't been tested so save all work and then test. Here is a quick video on setting up an external rule, set the folder to store the rule then create a new rule and paste the code. Alternatively paste to internal rule which saves it directly to the part document. 

If you can get this to run then it will then need a loop to add more than 1 and then to retrieve the xyz coordinates from excel. But to not overcomplicate the steps try out below first.

Sub Main
    CreateUCSBy3Points
End Sub
Sub CreateUCSBy3Points()
    ' Create a new part document.
    Dim oDoc As PartDocument = ThisDoc.Document
    
    ' Set a reference to the PartComponentDefinition object.
    Dim oCompDef As PartComponentDefinition = oDoc.ComponentDefinition

    Dim oTG As TransientGeometry = ThisApplication.TransientGeometry

    ' Create 3 workpoints to define the origin, x-direction and y-direction points.
    Dim oWorkPoint1 As WorkPoint = oCompDef.WorkPoints.AddFixed(oTG.CreatePoint(2, 0, 0))

    Dim oWorkPoint2 As WorkPoint = oCompDef.WorkPoints.AddFixed(oTG.CreatePoint(4, 0, 0))

    Dim oWorkPoint3 As WorkPoint = oCompDef.WorkPoints.AddFixed(oTG.CreatePoint(2, 2, 0))

    ' Create an empty definition object.
    Dim oUCSDef As UserCoordinateSystemDefinition = oCompDef.UserCoordinateSystems.CreateDefinition

    ' Set it to be based on the 3 points.
    oUCSDef.SetByThreePoints(oWorkPoint1, oWorkPoint2, oWorkPoint3)

    ' Create the UCS.
    Dim oUCS As UserCoordinateSystem = oCompDef.UserCoordinateSystems.Add(oUCSDef)
End Sub

[alan.wedge]

this code creates 3 workpoints and a UCS. red point is 0,0,0

i really dont understand. i have tried to pick apart the code you wrote but it doesnt make sense to me. i also couldnt find any resources on making the code read from excel in the way i need.

i have attached an example of my workspace. i need the coordinates from the excel sheet to be imported as workpoints or UCS.

[A.Acheson]

Here is a sample using a loop and go excel to read the excel data. Modify the excel filepath and sheet name as needed. The Coordinates for the workpoints might need tweaking still. The workpoints I believe are used to give direction to the UCS. 

Sub Main
    CreateUCSBy3Points
End Sub
Sub CreateUCSBy3Points()

GoExcel.Open("C\temp\..............xlsx","Sheet1")

GoExcel.DisplayAlerts = False
	
	For i As Integer = 2 To 10
	MessageBox.Show(i, "Title")

	    ' Create a new part document.
	    Dim oDoc As PartDocument = ThisDoc.Document
	    
	    ' Set a reference to the PartComponentDefinition object.
	    Dim oCompDef As PartComponentDefinition = oDoc.ComponentDefinition
	
	    Dim oTG As TransientGeometry = ThisApplication.TransientGeometry
			
	    ' Create 3 workpoints to define the origin, x-direction and y-direction points.
	    Dim oWorkPoint1 As WorkPoint = oCompDef.WorkPoints.AddFixed(oTG.CreatePoint(GoExcel.CellValue("A"& i), 0, 0))
	
	    Dim oWorkPoint2 As WorkPoint = oCompDef.WorkPoints.AddFixed(oTG.CreatePoint(GoExcel.CellValue("B"& i), 0, 0))
	
	    Dim oWorkPoint3 As WorkPoint = oCompDef.WorkPoints.AddFixed(oTG.CreatePoint(GoExcel.CellValue("C"& i), 2, 0))
	
	    ' Create an empty definition object.
	    Dim oUCSDef As UserCoordinateSystemDefinition = oCompDef.UserCoordinateSystems.CreateDefinition
	
	     'Set it To be based On the 3 points.
	    oUCSDef.SetByThreePoints(oWorkPoint1, oWorkPoint2, oWorkPoint3)
	
	    ' Create the UCS.
	    Dim oUCS As UserCoordinateSystem = oCompDef.UserCoordinateSystems.Add(oUCSDef)
	
	Next
	GoExcel.Close
End Sub

[A.Acheson]

Hi @alan.wedge 

In further testing of the samples I have found that the UCS by Transform gives the cleanest version of a UCS with no extra work points. It also give the option of rotation of the UCS. Original API Help VBA code sample here 

Sub Main
     CreateUCSByTransformMatrix
End Sub

Sub CreateUCSByTransformMatrix()

	GoExcel.Open("C:\Users\WorkpointCreation.xlsx","Sheet1")

	GoExcel.DisplayAlerts = False
	
	For i As Integer = 2 To 10
		
		' Detect empty cells and escape the rule, we are done.
		If GoExcel.CellValue("A" & i) Is Nothing  Then Exit Sub
	    
		' Create a new part document.
	    Dim oDoc As PartDocument = ThisDoc.Document
		    
		' Set a reference to the PartComponentDefinition object.
	    Dim oCompDef As PartComponentDefinition = oDoc.ComponentDefinition

	    Dim oTG As TransientGeometry = ThisApplication.TransientGeometry

	    ' Create an identity matrix.
	    Dim oMatrix As Matrix = oTG.CreateMatrix

	    ' Rotate about Z-Axis by 45 degrees.
	    'Call oMatrix.SetToRotation(3.14159 / 4, oTG.CreateVector(0, 0, 1), oTG.CreatePoint(0, 0, 0))
 		
	    ' Translate the origin to (2, 2, 2).
	    Dim oTranslationMatrix As Matrix = oTG.CreateMatrix
	    Call oTranslationMatrix.SetTranslation(oTG.CreateVector(GoExcel.CellValue("A" & i), GoExcel.CellValue("B" & i), GoExcel.CellValue("C" & i)))

	    oMatrix.TransformBy(oTranslationMatrix)

	    ' Create an empty definition object.
	    Dim oUCSDef As UserCoordinateSystemDefinition = oCompDef.UserCoordinateSystems.CreateDefinition

	    ' Set it to be based on the defined matrix.
	    oUCSDef.Transformation = oMatrix

	    ' Create the UCS.
	    Dim oUCS As UserCoordinateSystem = oCompDef.UserCoordinateSystems.Add(oUCSDef)
		
		' Set the Name of UCS.
		oUCS.Name = GoExcel.CellValue("D" & i)
	Next
	
	GoExcel.Close
	
End Sub

[alan.wedge · Accepted solution]

thank you for the help. i have ended up using the solution posted to the ilogic and vbnet forum: 

https://forums.autodesk.com/t5/inventor-ilogic-and-vb-net-forum/ilogic-code-to-import-workpoint-coor...