Using iLogic to pull multiple BOM's from one drawing file into excel

[jaypieper]

Hi all,

I am having one little issue with one line of code. Here is my setup: 

I have a drawing file that has multiple drawing sheets in the drawing file. Each sheet has a BOM and i need to pull all of the BOM'S off of each sheet and export them to excel. The number of sheets will vary in each drawing file i make. Every thing works but my program is hanging when i try to pull the name of the sheet in the drawing file. I am trying to take the name of the sheet and export that name to excel and put the sheet name on the tab name in excel. Now the names of the sheets will change between each drawing file.

Here is my line of code that i am having problems with:

'specify the XLS tab name

'here the file name is used

oOptions.Value("TableName") = ActiveSheet.sheet.name(False) 'without extension

Here is the drawing fileHere is the tabs in the excel

Here is the dwg file                              here is the excel showing the tabs

Here is the whole code below:

Dim oDoc As Inventor.DrawingDocument
oDoc = ThisDoc.Document
Dim oPartslist As PartsList
Dim oSheet As Inventor.Sheet

	'look at each sheet
	For Each oSheet In oDoc.Sheets
	'get the first parts list on the sheet
	Try
	oPartslist = oSheet.PartsLists(1)
	
	'get the path and name of the drawing file
	path_and_name = ThisDoc.PathAndFileName(False) ' without extension

	' create a new NameValueMap object
	oOptions = ThisApplication.TransientObjects.CreateNameValueMap

	'get the user name from the Inventor Options
	myName = ThisApplication.GeneralOptions.UserName
	
	'specify the columns to export         
	oOptions.Value("ExportedColumns") = "ITEM;PART #;DESCRIPTION;LENGTH;ITEM QTY;CATEGORY;FINISH"
 
	'specify the start cell
	oOptions.Value("StartingCell") = "A1"
 
	'specify the XLS tab name
	'here the file name is used
	oOptions.Value("TableName") = ActiveSheet.sheet.name(False) 'without extension

	'choose to include the parts list title row
	'in this example "Ye Old List of Parts" is written to the StartingCell
	oOptions.Value("IncludeTitle") = False          

	'choose to autofit the column width in the xls file
	oOptions.Value("AutoFitColumnWidth") = True
       
	' export the Partslist to Excel with options
	oPartslist.Export("C:\Users\" & myName & "\Desktop\EAP Cutlist Master.xlsx",
	PartsListFileFormatEnum.kMicrosoftExcel, oOptions)  

	Catch
	End Try
	Next 


'tell the user the files were created
MessageBox.Show("All BOM's have been exported.")

[JelteDeJong · Accepted solution]

hi,

in the documentation i dont find any refrence to the posibility to a name without extension. (It is a displayname and not a file name so im not sure what you would mean with "without extension".) Also i think you dont want the name of the active sheet because it would be the same all the same because you dont switch sheets. you would like the name of the sheet you are on in the loop. there for i would sugjest that you change the line with the error to:

oOptions.Value("TableName") = oSheet.Name
' old: oOptions.Value("TableName") = ActiveSheet.Sheet.Name(False) 'without extension

[jaypieper]

Thank you it works perfectly. 

[G.Binl]

Hello Gents hope you are well, This code works for me except that i can not get it to populate the next tab, 1st  works great. but as you can see no second sheet. do i need a counter of some sort a novice at best. 

thanks for any help

[jaypieper]

For me when I upgraded to inventor 2020 or newer, they changed how inventor exports the bom. When I exported multiple sheets, inventor was over writing the first sheet in the excel file. I had to add a new sheet in the excel file before I exported the next bom. Once I did this it solved my issue. Try this excerpt in your ilogic:

myXLS_File = Path and name of excel file

excelWorkbook = excelApp.Workbooks.Open(myXLS_File)
excelSheet = excelWorkbook.Sheets.Add'(Before:=excelWorkbook.Worksheets(excelWorkbook.Worksheets.count))
excelSheet = excelWorkbook.Worksheets(1).activate