Unable to export 3d point co-ordinates data to excel

Unable to export 3d point co-ordinates data to excel

Anonymous
Not applicable
5,416 Views
11 Replies
Message 1 of 12

Unable to export 3d point co-ordinates data to excel

Anonymous
Not applicable

Hello All,

 

I want to export 3D sketch point data to excel format.

I had found macro to do this task, please click on link https://knowledge.autodesk.com/support/inventor-products/troubleshooting/caas/sfdcarticles/sfdcartic...

I had followed the instructions to run the macro.

It will generate .csv file, but it is blank, no data on co-ordinates of points.

I am using Microsoft Office 2007 version.

Please find attached sample file and macro and part file.

I am trying to run macro file sketch-3d-line-coordinates.zip

Please let me know where am i going wrong.

0 Likes
Accepted solutions (1)
5,417 Views
11 Replies
Replies (11)
Message 2 of 12

martinhoos
Advocate
Advocate
Accepted solution

Hi, we have a similar code, may be it would give you an idea.

Regards

 

 

Sub ExportArbeitspunkte()
    Dim oDoc As PartDocument
    Set oDoc = ThisApplication.ActiveDocument
   
    Dim oDef As PartComponentDefinition
    Set oDef = oDoc.ComponentDefinition
   
    Dim oWorkpoints As WorkPoints
    Dim oWP As WorkPoint
    Dim oP As Point
       
    'get all workpoints in this part
    Set oWorkpoints = oDef.WorkPoints
    
    'Create a new Excel instance
    Dim oExcelApplication As Excel.Application
    Set oExcelApplication = New Excel.Application

    'create a new excel workbook
    Dim oBook As Excel.Workbook
    Set oBook = oExcelApplication.Workbooks.Add()
    Dim oSheet As Excel.WorkSheet
    Set oSheet = oBook.ActiveSheet
   
    Dim nRow As Integer
    nRow = 1

    'write the coordinates into separate columns, one workpoint each row
    For Each oWP In oWorkpoints
        Set 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
    Set oBook = Nothing
    Set oSheet = Nothing
    Set 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
    Set oPartDoc = ThisApplication.Documents.Add(kPartDocumentObject, ThisApplication.FileManager.GetTemplateFile(kPartDocumentObject))

    
    
    
End Sub
0 Likes
Message 3 of 12

Anonymous
Not applicable

Thanks a lot

0 Likes
Message 4 of 12

mes063
Contributor
Contributor

Hi! I am new to Inventor and didn't know there was a place to code/a command window. Where do I put this code in order to export the coordinates to Excel?

0 Likes
Message 5 of 12

janelson33
Collaborator
Collaborator

I'm in Inventor 2024 Professional trying to run this script and I'm being told 'let' and 'set' are no longer supported?? Any advice on how to make this script work in newer versions of Inventor?

There is no power but what the people allow you to take.
0 Likes
Message 6 of 12

WCrihfield
Mentor
Mentor

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 Sub

Wesley Crihfield

EESignature

(Not an Autodesk Employee)

0 Likes
Message 7 of 12

janelson33
Collaborator
Collaborator

I really appreciate you explaining the change from VBA to VB.net systems in Inventor. I'd bet that was a real fun rollout for the IT teams within companies that heavily rely on iLogic haha.

 

The code you provided now works again. I've changed the German to English, updated the Excel book to .xlsx, and deleted the lines that create a new part to best suit my needs.

 

I've tried to search for how to call upon my UCS, but I'm not really finding much. I am overall coding illiterate though.

 

My goal was to use the UCS origin point as the part origin, and then have the XYZ values of my Work Points start from the UCS origin.

There is no power but what the people allow you to take.
0 Likes
Message 8 of 12

WCrihfield
Mentor
Mentor

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

EESignature

(Not an Autodesk Employee)

0 Likes
Message 9 of 12

WCrihfield
Mentor
Mentor

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 Sub

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

Wesley Crihfield

EESignature

(Not an Autodesk Employee)

Message 10 of 12

janelson33
Collaborator
Collaborator

I appreciate that explanation too. I ended up simply using the X-Y-Z points with respect to the global origin. It provides our vendor with more data than a drawing without it, plus we used this strategy on similar parts in the past with great results.

 

Perhaps what I asked for probably isn't possible in Inventor, primarily because we can't make a WorkPoint that we can force to reference a UCS? I'm sure with some intensive coding it might be possible, but that's not in my skill set right now.

There is no power but what the people allow you to take.
0 Likes
Message 11 of 12

janelson33
Collaborator
Collaborator

This is also a great code! I ended up structuring the table myself, but that's exactly how I structured it!

There is no power but what the people allow you to take.
0 Likes
Message 12 of 12

WCrihfield
Mentor
Mentor

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

EESignature

(Not an Autodesk Employee)

0 Likes