Unable to export 3d point co-ordinates data to excel
Hi @janelson33. The code example earlier in this discussion was written in the older VBA system, where they were required to use the 'Set' keywords when setting the values of most types of variables. They haven't even included the VBA editor in Inventor installations since around 2021 version, due to the inherent security issues, since Microsoft stopped all development and maintenance on that coding system. Some folks still use it today though, because they have used it for a long time, are used to it, and have a lot of code based resources developed in that system. Plus it had a much more advanced user interface than what we have available in the iLogic rule editor, within Inventor. Our iLogic rules use the newer vb.net coding system, which has a lot of similarities to the VBA system, but much further, and ongoing development. I copied the code from the example above, and converted it into vb.net, so that you can use it within an iLogic rule, if you want to give it a try. I have not tested it yet myself, because I have no use for it. The VBA system had a different way of setting up external references, such as providing recognition of the object types within the Excel Object Model API system, which is an entirely different application. In iLogic rules, we must include some special lines of code ('AddReference lines) within the 'Header' of the rule that are used to enable that type of object type recognition. Then we can also include 'Imports' lines within the Header of our rules, which provide coding shortcuts to deeper elements within an external reference. The first 3 lines of the example code below will automatically get put into the Header of your rule, when you cut & paste the overall code into the iLogic rule editor.
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Sub Main
'call the custom Sub routine below to run
ExportArbeitspunkte()
End Sub
Sub ExportArbeitspunkte()
Dim oDoc As PartDocument = ThisApplication.ActiveDocument
Dim oDef As PartComponentDefinition = oDoc.ComponentDefinition
Dim oWorkpoints As WorkPoints = oDef.WorkPoints
Dim oWP As WorkPoint
Dim oP As Inventor.Point
'Create a new Excel instance
Dim oExcelApplication As Excel.Application = New Excel.Application
'create a new excel workbook
Dim oBook As Excel.Workbook = oExcelApplication.Workbooks.Add()
Dim oSheet As Excel.WorkSheet = oBook.ActiveSheet
Dim nRow As Integer = 1
'write the coordinates into separate columns, one workpoint each row
For Each oWP In oWorkpoints
oP = oWP.Point
oSheet.Cells(nRow, 1) = oP.X * 10
oSheet.Cells(nRow, 2) = oP.Y * 10
oSheet.Cells(nRow, 3) = oP.Z * 10
nRow = nRow + 1
Next
Dim OutputFile As String
OutputFile = Left(ThisApplication.ActiveDocument.FullFileName, _
Len(ThisApplication.ActiveDocument.FullFileName) -4) + "_Arbeitspunkte.xls"
On Error Resume Next
oBook.SaveAs(OutputFile)
oBook.Close()
oBook = Nothing
oSheet = Nothing
oExcelApplication = Nothing
MsgBox("Es wurde eine Excel Tabelle im aktuellen Verzeichnis erstellt und eine neue IPT für den Import geöffnet!")
'Make a new part file
Dim oPartDoc As PartDocument = ThisApplication.Documents.Add(kPartDocumentObject, ThisApplication.FileManager.GetTemplateFile(kPartDocumentObject))
End SubWesley Crihfield
(Not an Autodesk Employee)
OK. Well, the very first WorkPoint object [WorkPoints.Item(1)] in every model file (part or assembly) is always the 'Origin' point of the model. All others after that one are custom ones. Similarly, the first 3 WorkAxis objects, and first 3 WorkPlane objects are the ones defining the origin of every model file, and all the ones after those will be custom ones. However, we can actually create new UCS (UserCoordinateSystem) objects within our assemblies and parts. Each custom UCS will have its own origin point, 3 axes, and 3 planes. So using the UCS term can be a bit ambiguous in this context. If you are talking about the original origin of the model (the one that already exists in 'new' models), then that would be relatively simple, but if you are talking about a specific custom UCS, then all other WorkPoints locations somehow being in reference to that custom one, then that would have to involve some geometry related math to calculate. Naturally, all custom WorkPoints that are defined in a model file will always be in reference to the model's original origin point, and origin axes, so if that was the case, no calculations would be needed...unless some 'units conversion' may be needed, which is common. Inventor uses the metric system behind the scenes, so every distance is in centimeters by default, until the units get converted, so that's what you get naturally when doing things by code, instead of 'document units'.
Wesley Crihfield
(Not an Autodesk Employee)
Here is another example code you can try out in an iLogic rule. It does mostly the same task, but includes the name of the WorkPoint while recording the data, and writes the name of the WorkPoint in the first column in the Excel sheet, then its coordinates in the next 3 columns after that. It also divides the overall task up into 3 parts (Main, Get Data, & Write Data), using 3 code routines. It must have a 'Sub Main' area, if it will have other routines, and the main area gets the WorkPoints from the current document, then calls the other two routines to run. The second routine extracts all the data from the WorkPoints, and puts it into a Dictionary. Then the third routine writes that data to Excel. There are lots of ways this type of code could be laid-out. I have not tested it yet though, since I have no use for it myself, and don't have a good example model for it to work on. Plus I am leaving for the day. Let me know if it works OK for you.
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Sub Main
'get current document
Dim oDoc As Inventor.Document = ThisDoc.Document
'check which type of document we got...if not a part or assembly, then exit rule
If (Not TypeOf oDoc Is PartDocument) AndAlso (Not TypeOf oDoc Is AssemblyDocument) Then
MessageBox.Show("This code only works for a Part or Assembly.", "Wrong Document Type", MessageBoxButtons.OK, MessageBoxIcon.Stop)
Return 'this exit the code routine, and the rule in this case
End If
'get the ComponentDefinition (where all geometry & coordinate system exists)
'this is getting a generic version of that type of object
Dim oCD As Inventor.ComponentDefinition = oDoc.ComponentDefinition
'get the WorkPoints collection for this document
Dim oWPoints As Inventor.WorkPoints = oCD.WorkPoints
'call the custom Function defined after this routine to run
'it is designed to return a Dictionary of entries, where each entry has two parts
'Each entry's Key is the name of the WorkPoint
'each entry's Value is an array of Double type values (the Point's 3 coordinates)
Dim oPointCoordsData As Dictionary(Of String, Double()) = GetWorkPointsData(oWPoints)
'now we should check the returned data, to see if it is valid
If (oPointCoordsData Is Nothing) OrElse (oPointCoordsData.Count = 0) Then
'if not, then let user know, before exiting this code routine
MessageBox.Show("No data was collected from the WorkPoints!", "No Data Collected", _
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Return 'exit this code routine
End If
'<<< now we can focus on the task of writing that data to Excel >>>
'call a custom Sub routine defined below to write that data to Excel
WriteCoordsDataToExcel(oPointCoordsData)
End Sub
Function GetWorkPointsData(WPoints As Inventor.WorkPoints) As Dictionary(Of String, Double())
'create a collection type variable to hold names and point coordinates data
'this is a Dictionary, which is a 2-factor list (each entry has 2 parts)
'each entry has a 'Key' and a 'Value'
'the Key here will be a String (text), the name of the WorkPoint
'the Value will be an array of Double values, the X,Y,Z coordinates of the Point
Dim oPointCoordsData As New Dictionary(Of String, Double())
'iterate through all WorkPoints, in their 'natural' order (oldest existing to newest)
For i As Integer = 1 To WPoints.Count
'get the WorkPoint at this Index
Dim oWPoint As Inventor.WorkPoint = WPoints.Item(i)
'get the transient mathematical Point object from it
Dim oPoint As Inventor.Point = oWPoint.Point
'create variable to supply to next method
'this is an array of Double type values (Double is a decinal type numerical value)
Dim oCoords() As Double = {}
'this method sets the value of previous variable
oPoint.GetPointData(oCoords)
'check if the Dictionary already contains an entry for this WorkPoint
If Not oPointCoordsData.ContainsKey(oWPoint.Name) Then
'if not, then add one, with its name, and its coordinates
oPointCoordsData.Add(oWPoint.Name, oCoords)
End If
Next 'i 'this is where it goes to the next Index of WorkPoints, if there are any
'return the collected data to the proceedure that called this Function to run
Return oPointCoordsData
End Function
Sub WriteCoordsDataToExcel(PointCoordsData As Dictionary(Of String, Double()))
'create an instance of the Excel.Application object
Dim oExcel As Object = CreateObject("Excel.Application")
'make Excel visible
oExcel.Visible = True
'create a new Excel Workbook
Dim oWB As Object = oExcel.Workbooks.Add()
'create a new Excel Worksheet
Dim oWS As Object = oWB.Worksheets.Add()
Dim iRow As Integer = 1
Dim iCol As Integer = 1
'write column headers/labels
oWS.Cells(iRow, 1) = "Name"
oWS.Cells(iRow, 2) = "X"
oWS.Cells(iRow, 3) = "Y"
oWS.Cells(iRow, 4) = "Z"
'start iterating through the data that we collected earlier, in its natural order
For i As Integer = 0 To PointCoordsData.Count - 1
iRow += 1
'get the entry at this Index
Dim oEntry As KeyValuePair(Of String, Double()) = PointCoordsData.ElementAt(i)
'get the name of the WorkPoint from the entry
Dim sName As String = oEntry.Key
'get the coordinates of the Point from the entry
Dim oCoords() As Double = oEntry.Value
'write the WorkPoint's name to the first column in the row
oWS.Cells(iRow, 1) = sName
'write the X coordinate to the second column
oWS.Cells(iRow, 2) = oCoords(0)
'write the Y coordinate to the third column
oWS.Cells(iRow, 3) = oCoords(1)
'write the Z coordinate to the fourth column
oWS.Cells(iRow, 4) = oCoords(2)
Next 'i 'go to next entry in the Dictionary
'negate the variables, without closing the Workbook or Excel
oWS = Nothing
oWB = Nothing
oExcel = Nothing
End SubIf this solved your problem, or answered your question, please click ACCEPT SOLUTION .
Or, if this helped you, please click (LIKE or KUDOS) 👍.
Wesley Crihfield
(Not an Autodesk Employee)
Maybe I misunderstood the challenge. I assumed you meant the built-in 'origin' UCS that already pre-exits for every part document that we create, but I guess that maybe you meant that you want the location (coordinates offset) of every 'custom' WorkPoint to be in reference to a specific 'custom' UCS that was created within the part, instead of the original origin. Yes, I believe that would be possible. We can get to those 'custom' UCSs the following way:
PartDocument.ComponentDefinition.UserCoordinateSystems which gives us access to the UserCoordinateSystems collection object. It will not have anything in it by default, unless the 'template' part had one in it, or you created a new one in the part. Then we can use its Item property to get one of the UserCoordinateSystem objects, if one already exists, or create a new one using the UserCoordinateSystems.CreateDefinition method, then supply its resulting UserCoordinateSystemDefinition object as input into the UserCoordinateSystems.Add method. Once we have a UCS, we can access its 'origin point' through its UserCoordinateSystem.Origin property, which gives us a WorkPoint as its value. Then we can get the Inventor.Point object from that through its WorkPoint.Point property.
Now, when we have one Inventor.Point type object instance, and we want to get its coordinate type location, in reference to another Inventor.Point object instance, we can use the Point.VectorTo method, which will give us an Inventor.Vector type object instance. A Vector also just has the 3 coordinates X, Y, & Z. These are interpreted this way: Imagine a line starting from 0,0,0 coordinate, and its end is at the coordinates of that Vector. That line tells is a 'distance' and 'direction'. We can get the 'distance' part from the Vector.Length property. But 'direction' is specified by a 'UnitVector' in Inventor. That is essentially the same exact thing, but is always just '1 unit' in length, because length does not matter. We can get that from the Vector.AsUnitVector method, if needed.
In summary, you should be able to use the coordinates of the Vector object as the 'perspective' based offset, rather than their 'absolute' coordinates positions of the point object.
Edit: I just attached a text file containing some code that you can copy & paste into a new/empty iLogic rule, to test with. It attempts to just get the 'first' custom UCS it can find in the current document, then passes that on to the second routine, which I modified to receive it and use it. It will now attempt to get the 'vector' from the custom UCS's origin Point to the WorkPoint's Point, then uses the coordinates of that vector, instead of the absolute coordinates of each WorkPoint from the model's original origin point. I hope this works out OK, because once again, I have not tested this yet. By the way, that association can be reversed, if necessary. What I mean is getting the vector from the WorkPoint to the UCS origin point, instead of from the UCS origin point to the WorkPoint, which would reverse the coordinates by changing the perspective.
Wesley Crihfield
(Not an Autodesk Employee)