topic iLogic to create general table from excel file in Inventor Forum

iLogic to create general table from excel file

ivan.liu — Fri, 18 Aug 2017 19:46:13 GMT

Hello,

I am new to iLogic programming and would like to simply replicate the action to insert a general table in an Inventor Drawing that is created from an excel file that i have previously made. The file table i have in excel already filled and formatted. I would like to do this because when i place a excel table object and link it, it does not update automatically when i update the drawing and if i add rows to the excel file table, the table in the drawing does not follow through.

Is there any chance someone can help me get started? I've seen many posts go the other way (exporting to excel) but all i need to do is just insert the general table.

Thanks,

Re: iLogic to create general table from excel file

MechMachineMan — Fri, 18 Aug 2017 19:51:54 GMT

The sample / programming help is your buddy here...

http://help.autodesk.com/view/INVNTOR/2018/ENU/?guid=GUID-5EEA9BEE-3236-40D5-B3AA-840238C1E3AE

Directly from the Sample in the help:

*Note, this is coded in vba; thus must be used in the rule editor.

* conversion to vb.net for use in the rule environment simply involves removing get/set statements in this case.

Public Sub CreateDrawingExcelTable()    
    ' Set a reference to the active drawing document
    Dim oDrawDoc As DrawingDocument
    Set oDrawDoc = ThisApplication.ActiveDocument
    
    ' Set a reference to the active sheet
    Dim oActiveSheet As Sheet
    Set oActiveSheet = oDrawDoc.ActiveSheet
    
    ' Create the placement point for the table
    Dim oPoint As Point2d
    Set oPoint = ThisApplication.TransientGeometry.CreatePoint2d(25, 25)
    
    ' Create the table by specifying the source Excel file
    ' This assumes that the first row in the Excel sheet specifies the column headers
    ' You can specify a different row using the last argument of the AddExcelTable method
    Dim oExcelTable As CustomTable
    Set oExcelTable = oActiveSheet.CustomTables.AddExcelTable("C:\Temp\test.xls", oPoint, "Excel Table")
End Sub

Re: iLogic to create general table from excel file

Curtis_W — Fri, 18 Aug 2017 20:05:54 GMT

@ivan.liu wrote:

...I would like to do this because when i place a excel table object and link it, it does not update automatically when i update the drawing and if i add rows to the excel file table, the table in the drawing does not follow through.

Hi ivan.liu,

Let's make sure we understand the built in functionality first. See the information below. Does this do what you are wanting?

After placing a table that is linked to and Excel file, you should have a 3rd Party node in the browser. If you expand this and then right cliick on the Excel file link you should see Edit, which will open the file and allow you to change or add data.

Then you can right click on the table in the drawing and choose Update to see the results of the change.

(note that you do not need to edit the Excel file using the 3rd part node for the Update option to work. You can edit it using Excel as you normally would, and Inventor should detect the change and give you the Update option)

Also just as a tip, you can search and ask programming questions of this type on the Inventor Customization forum too:
http://forums.autodesk.com/t5/Autodesk-Inventor-Customization/bd-p/120

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

Re: iLogic to create general table from excel file

ivan.liu — Fri, 18 Aug 2017 20:06:47 GMT

Yes! Thanks MechMachineMan!

Just a few follow up questions:

1. How do I set the path of the excel file to be the Drawing File Name plus "_Barlist"?

2. How do I set the sheet to be a specific sheet?

3. How do i run this in macro iLogic so that it will be run before i save?

Thanks a lot!

Re: iLogic to create general table from excel file

ivan.liu — Fri, 18 Aug 2017 22:36:02 GMT

So I was able to answer my own questions and end up with the following:

------------------------------------------------

Public Sub InsertQuantitiesTable()
'Set a reference to the active drawing document
Dim oDrawDoc As DrawingDocument
Set oDrawDoc = ThisApplication.ActiveDocument

'Find Drawing File Path without Extension
Dim Path As String
Path = ThisApplication.ActiveDocument.FullFileName
Path = Left(Path, Len(Path) - 4)

'Set a reference to the sheet
Dim oActiveSheet As Sheet
Set oActiveSheet = oDrawDoc.Sheets("SEGMENT DIMENSION I:2")

'Set the placement point for the table
Dim oPoint As Point2d
Set oPoint = ThisApplication.TransientGeometry.CreatePoint2d(24, 10)

'Create the table by specifying the source Excel file
'This assumes that the first row in the Excel sheet specifies the column headers
'You can specify a different row using the last argument of the AddExcelTable method
Dim oExcelTable As CustomTable
Set oExcelTable = oActiveSheet.CustomTables.AddExcelTable(Path & "_QUANTITIES.xlsx", oPoint, "ESTIMATED QUANTITIES PER SEGMENT")

End Sub

--------------------------------------------------

However, my table is not formatted as it is in excel. my first column needs to be widened. is there any way to add a input for the column width in the code above?

Thanks,

Re: iLogic to create general table from excel file

MechMachineMan — Sat, 19 Aug 2017 06:15:55 GMT

http://help.autodesk.com/view/INVNTOR/2018/ENU/?guid=GUID-B8D1B7BF-6534-4E72-809C-7887FE8B9A09

Public Sub InsertQuantitiesTable()
'Set a reference to the active drawing document
Dim oDrawDoc As DrawingDocument
Set oDrawDoc = ThisApplication.ActiveDocument

'Find Drawing File Path without Extension
Dim Path As String
Path = ThisApplication.ActiveDocument.FullFileName
Path = Left(Path, Len(Path) - 4)

'Set a reference to the sheet
Dim oActiveSheet As Sheet
Set oActiveSheet = oDrawDoc.Sheets("SEGMENT DIMENSION I:2")

'Set the placement point for the table
Dim oPoint As Point2d
Set oPoint = ThisApplication.TransientGeometry.CreatePoint2d(24, 10)

'Create the table by specifying the source Excel file
'This assumes that the first row in the Excel sheet specifies the column headers
'You can specify a different row using the last argument of the AddExcelTable method
Dim oExcelTable As CustomTable
Set oExcelTable = oActiveSheet.CustomTables.AddExcelTable(Path & "_QUANTITIES.xlsx", oPoint, "ESTIMATED QUANTITIES PER SEGMENT")

'Use the integer of the column
oExcelTable.Columns(1).Width = 22

End Sub