Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.
emanuel.c
455 Views, 6 Replies

Export Largest Parts List to Excel

I would like to export to excel the largest parts list found in a drawing. The reasoning is that if it's a weldment typically the parts list will be found on sheet 1 and it should automatically be the largest. If it's an assembly, usually at the end of the drawing we would have a parts list with a total quantity (parts only level) of all the loose parts in that assembly. As such I need to find which is the largest parts list on the drawing and export that.

 

The function RowNum returns the number of rows of each parts list on all sheets and it works well.

The Sub ExportPartsList also works well. Currently it's set to export the parts list from the 1st sheet, since PltoExport = 1.

 

I'm not sure how to capture the values from function RowNum in an array, compare them and have variable PLtoExport assigned the maximum value of that array.

 

Thank you for your help!

 

Sub Main()
	If Not ThisApplication.ActiveDocument.DocumentType = kDrawingDocumentObject Then
		MessageBox.Show("Hey this rule only runs in drawing documents!")
		Exit Sub
	End If
	
	Dim oDrawDoc As DrawingDocument
	oDrawDoc = ThisApplication.ActiveDocument
	Dim oSheet As Sheet
	i = 1
	For Each oSheet In oDrawDoc.Sheets
	For Each oPL As PartsList In oSheet.PartsLists
		RowNum(oPL)
		Dim oSheet1 = oDrawDoc.Sheets(i)
		i = i + 1
		irows = RowNum(oPL)
		'MessageBox.Show("Parts List on Sheet: " & i & " has " & irows & " rows")
	Next	
	Next

Dim PLtoExport As Integer = 1
ExportPartsList(PLtoExport)

End Sub

Public Function RowNum(oPartsList As PartsList) As Integer
	
	Dim oPartList As PartsList
	If oPartsList Is Nothing Then Exit Function
	Dim oRows As Integer
	oRows = oPartsList.PartsListRows.Count
	Return oRows
	
End Function

Sub ExportPartsList(PLtoExport As Integer)
		
	Dim odrawDoc As DrawingDocument = ThisDoc.Document
	odrawDoc.Activate
	'get the path and name of the drawing file
	Dim fileName As String  = IO.Path.GetFileNameWithoutExtension(odrawDoc.FullFileName)
	'Logger.Info("FileName: " & fileName)
	Dim fullFileName As String = odrawDoc.FullFileName
	
	'Dim FilePath As String = Left(FullFileName, InStrRev(FullFileName, "\"))
	Dim filePath As String
	Try
		filePath = IO.Path.GetDirectoryName(odrawDoc.FullFileName) & "\"
	Catch
		MessageBox.Show("You need to save the file first!", "Error: No Filename")
		Exit Sub
	End Try
	
	Dim excelName As String = filePath & "BOM for - " & fileName & ".xlsx"
	'Logger.Info("ExcelName: " & excelName)
	
	If IO.File.Exists(excelName) Then
		'Ask to overwrite Excel File
'		Dim result As MsgBoxResult = MessageBox.Show("The Excel file already exists: " & _
'									vbCr & vbCr & excelName & vbCr & vbCr & "Do you want to overwrite the file?", "File Exists", _
'									MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
'		If result = MsgBoxResult.Yes Then
			Try 'So as to overwrite.
				IO.File.Delete(excelName) 
			Catch
				MessageBox.Show("Is it perhaps opened?", "Could Not Overwrite Excel File")
				'drawDoc.Close(True)
				Exit Sub
			End Try
'		Else
'			'drawDoc.Close(True)
'			Exit Sub
'		End If
	Else
	End If

	
	'specify the drawing sheet
	oSheet = oDrawDoc.Sheets(PLtoExport) ' first sheet	
	 'say there is a Partslist on the sheet.
	oPartslist = oSheet.PartsLists(1)
     
	'Create a new NameValueMap object.
	Dim options = ThisApplication.TransientObjects.CreateNameValueMap
	
	'Specify an existing template file.
	'To use For formatting colors, fonts, etc.
	options.Value("Template") = "M:\Autodesk Inventor\Ilogic\BOM Template.xlsx"
	
	'Specify the Columns To export(all columns need to be in the partslist).
	'options.Value("ExportedColumns") = "QTY;PART NUMBER;DESCRIPTION"
	
	'Specify the start cell.
	options.Value("StartingCell") = "A" & startRow + 4
	
	'Naming the Tab in Excel	
	options.Value("TableName") = "Parts List" 
		
	'Choose to include the parts list title row.
	options.Value("IncludeTitle") = False		
	
	'Choose to autofit the column width in the xls file
	options.Value("AutoFitColumnWidth") = True
    
	'export the Partslist to Excel with options
	oPartslist.Export(excelName, PartsListFileFormatEnum.kMicrosoftExcel, options)
	'Do you want to close the document afterwards?
	'oDoc.Close(True)
	
	GoExcel.Open(excelName, options.Value("TableName"))
	GoExcel.CellValue(excelName, options.Value("TableName"), "A1") = "PARTS LIST FOR"
	GoExcel.CellValue(excelName, options.Value("TableName"), "A2") = fileName	
	GoExcel.Save
	GoExcel.Close
	
End Sub