Community
Inventor Forum
Welcome to Autodesk’s Inventor Forums. Share your knowledge, ask questions, and explore popular Inventor topics.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

How to export 3D points to excel?

16 REPLIES 16
Reply
Message 1 of 17
a.sarmukaddam
7277 Views, 16 Replies

How to export 3D points to excel?

Please help.

Thanks in advance.

16 REPLIES 16
Message 2 of 17
Hochenauer
in reply to: a.sarmukaddam

Open the VBA Editor from Inventor/Tools.

 

Inside the Vba editor, select Tools-> References from the menu.

Check the Microsoft Excel Object Library

 

 

 

Sub ExportWorkpoints()
   
    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 workbook
    Dim oBook As Excel.Workbook
    Set oBook = Excel.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
        oSheet.Cells(nRow, 2) = oP.Y
        oSheet.Cells(nRow, 3) = oP.Z
        nRow = nRow + 1
    Next
       
    oBook.SaveAs ("c:\test.xls")
   
End Sub

 

 

 

 

 

 

Hope this helps,

Gerald

 



Gerald Hochenauer(gerald.hochenauer@autodesk.com)
Inventor Principal Software Engineer

Manufacturing Group
Autodesk, Inc.

 



Gerald Hochenauer
Senior Principal Engineer, Inventor
Autodesk, Inc.

Message 3 of 17
dansharpy
in reply to: a.sarmukaddam

Hi,

 

Id like to know more about this if possible? Could you give more detailed instructions please? Ive opened the VBA editor, checked excel object library but when i enter the text in a new module and try to run it, it comes up with an error. 

 

Thanks, Dan

Message 4 of 17
dansharpy
in reply to: dansharpy

Could anyone please give me some help with this as i'm completely stuck.

 

Thanks in advance.

Message 5 of 17
Hochenauer
in reply to: a.sarmukaddam

Dan,

 

I need some info on the error you are seeing in order to be able to help. What line of the code? What's the error message?

 

Gerald

 



Gerald Hochenauer
Senior Principal Engineer, Inventor
Autodesk, Inc.

Message 6 of 17
dansharpy
in reply to: Hochenauer

Gerald,

 

It says "Compile error: User-defined type not defined"

 

If i click "ok" it then highlights the line of code "oBook As Excel.Workbook"

 

Does that help?

 

Im creating the module under "Document Project"

 

Thanks.

 

 

Message 7 of 17
dansharpy
in reply to: dansharpy

Sorry, that was the wrong one actually! Id forgotten to check excel object library.

 

With that checked it now reads "run-time error '13': Type mismatch" If i click debug, it highlights the line "Set oDoc = ThisApplication.ActiveDocument"

 

Any ideas?

 

Thanks, Dan

Message 8 of 17
Hochenauer
in reply to: dansharpy

Are you running this from within a part document?



Gerald Hochenauer
Senior Principal Engineer, Inventor
Autodesk, Inc.

Message 9 of 17
dansharpy
in reply to: Hochenauer

It's an assembly, but only with <20 parts. Will it only work on a part document then?

Message 10 of 17
Hochenauer
in reply to: a.sarmukaddam

The example I wrote is for a single part document - it is a generic example that takes workpoints within this part and exports their locations.

 

 Dim oDoc As PartDocument

 

as opposed to

 

 Dim oDoc As AssemblyDocument

 

 

 

In order to extend the sample to assemblies, you'd have to iterate over the assembly component Definition structure(Assemblies can contain sub assemblies,....) .

You'd need to know about Component Occurrences and transformations in order to get all the points to show up at the assembled locations.

 

Unfortunately I can't spare the time to write it - I need to wrap up things before leaving on a 3 week business trip. Maybe somebody else can pick it up or you get some training. It is quite a good example to learn how programming Inventor with VBA works.

 

 

Regards,

Gerald Hochenauer

 

 

 

 

 

 



Gerald Hochenauer
Senior Principal Engineer, Inventor
Autodesk, Inc.

Message 11 of 17
Mike_Maenpaa
in reply to: dansharpy

You could do a Derive Part and choose the assembly. Then you could run the code on the Derived Part.

 

Mike

 

Message 12 of 17
kyra
in reply to: Hochenauer

I have tried to apply this module to single part document however an error comes up:

 

Run Time Error 91

Object variable or with block variable not set

 

and after debug this line is highlighted: Set oDef = oDoc.ComponentDefinition

 

Could you please explain what i'm doing wrong.

 

Thanks 

Message 13 of 17
Hochenauer
in reply to: kyra

From your description it sounds like oDoc has not been assigned.

 

Set oDoc = ThisApplication.ActiveDocument

Please make sure that oDoc is spelled the same everywhere. It might be case sensitive if 'Option Exlicit' is not stated on top of the page.

 

Gerald



Gerald Hochenauer
Senior Principal Engineer, Inventor
Autodesk, Inc.

Message 14 of 17
kyra
in reply to: Hochenauer

Dear Sir,

 

I am now getting another error stating:

 

error.png

 

Upon debugging the following line is highlighted:

 

oBook.SaveAs ("c:\test.xls")

 

Thank you for you support,

Kyra

Message 15 of 17
Hochenauer
in reply to: kyra

The save operation fails for probably one of the reasons outlined in the error dialog message.

Please make sure that the .xls file does not exist prior to running the vba macro, and manually delete it before each new run. Close excel as well. Alternatively you can extend the code to handle such cases - it should be possible with some VBA training.

 

kind regards,

Gerald



Gerald Hochenauer
Senior Principal Engineer, Inventor
Autodesk, Inc.

Message 16 of 17
kyra
in reply to: Mike_Maenpaa

Problem Solved, Thanks for your support.

Message 17 of 17
Cadguy_2014
in reply to: Hochenauer

Nice

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report