iLogic to Import excel table (same name as drawing number) and resize the cells.

[gsinghGSFF8]

I am trying to import the BOM of assembly to idw from an excel. 

By running the code below, I am getting the BOM on the sheet but I am trying to do 2 adjustments:

1. I want the code to be general, means I want to eliminate manually typing the spreadsheet name for each assembly (TEST.XLS in this example) and want the code should detect the excel by the part name on the drawing sheet.

2. I want the cells in the table to be auto adjusted based on the text length.

I have attached the screen shot of the table I am getting by running this code.

SyntaxEditor Code Snippet

	Sub main CreateDrawingExcelTable()    
    
    Dim oDrawDoc As DrawingDocument
    oDrawDoc = ThisApplication.ActiveDocument
    
     Dim oActiveSheet As Sheet
     oActiveSheet = oDrawDoc.ActiveSheet
    
    Dim oPoint As Point2d
    oPoint = ThisApplication.TransientGeometry.CreatePoint2d(69, 58)
    
    Dim oExcelTable As CustomTable	
    oExcelTable = oActiveSheet.CustomTables.AddExcelTable _
	("C:\Temp\Test\Test.xlsx", oPoint,iProperties.Value("Project", "Part Number"))
	
End Sub

[chandra.shekar.g]

@gsinghGSFF8,

1. I want the code to be general, means I want to eliminate manually typing the spreadsheet name for each assembly (TEST.XLS in this example) and want the code should detect the excel by the part name on the drawing sheet - To achieve this task, try below iLogic code to get part name in the current drawing sheet.

Dim oSheet As Sheet
oSheet = ThisDrawing.ActiveSheet.Sheet 

Dim name As String
name = oSheet.DrawingViews.Item(1).ReferencedDocumentDescriptor.ReferencedDocument.Displayname

name = name.Substring(0, name.Length - 4)

2. I want the cells in the table to be auto adjusted based on the text length. - This task needs traversing all row values and finding a maximum text length. Width of each column can be modified based on text length (It is a trail and error method. As text font size varies, accordingly width of each column also varies).

Dim oSheet = ActiveSheet.Sheet
Dim oCustomTable = oSheet.CustomTables.Item(1)
oCustomTable.Columns.Item(1).Width=3.0

Thanks and regards,

[gsinghGSFF8]

Hi Chandra, thanks for the reply.

I think you didn't got the first point right. Let em explain it in bit detail.

What I am trying to achieve here is: I have one assembly (Test.iam). I have an iLogic to export the BOM of Test.iam and save it as Test.xls. I add more information in that excel sheet and I want the ilogic to bring all the information in that excel sheet to inventor drawing. The iLogic I currently using needs to enter the name of excel file (Test.xls) to bring the info onto the drawing sheet. 

oExcelTable = oActiveSheet.CustomTables.AddExcelTable("C:\Test\Test.xlsx", oPoint,iProperties.Value("Project", "Part Number"))

Where as I want the Ilogic to detect the part number of the opened drawing and look for the excel sheet with that name and throw the information on the inv. dwg sheet. (see image attached)

[chandra.shekar.g · Accepted solution]

@gsinghGSFF8,

Hoping that below iLogic code may help to detect excel name.

Sub Main()
	
	oPartNo = iProperties.Value("Project", "Part Number")

	excelPath = "C:\Test" 'Directory name to be serached for excel file
	
	'excelPath = ThisDoc.Path 'Path of drawing file

	Dim oFound As Boolean = False 

	For Each fileName In System.IO.Directory.GetFiles(excelPath)		 
		If fileName = excelPath + "\" + oPartNo + ".xlsx" Then
			oFound = True
			Exit For 
		End If 
	Next

	Dim excelFullName As String = ""
	If oFound = True Then 
		excelFullName = excelPath + "\" + oPartNo + ".xlsx"		 
	Else
		MessageBox.Show("Excel file not found", "Excel file")
		Exit Sub 
	End If  
	
	Dim oDrawDoc As DrawingDocument
    oDrawDoc = ThisApplication.ActiveDocument
    
	Dim oActiveSheet As Sheet
	oActiveSheet = oDrawDoc.ActiveSheet
    
    Dim oPoint As Point2d
    oPoint = ThisApplication.TransientGeometry.CreatePoint2d(69, 58)
    
    Dim oExcelTable As CustomTable	
    oExcelTable = oActiveSheet.CustomTables.AddExcelTable _
	(excelFullName, oPoint,iProperties.Value("Project", "Part Number")) 
	
End Sub 

Thanks and regards,