I have an iLogic routine using the ThisBOM.export function. I am using the “parts only” format for my export. The format that ends up in my excel file has the columns in alphabetical order based on the column header. This is different than the layout in the part only view. What drives the column order in the ThisBOM function and can it be altered or controlled? Thanks
Hi,
I seem to remember that the order in the UI and through the API are not kept in sync. This thread says it too:
You can do some sorting on the BOMView, but that as well might not be refelected back in the UI - it should have an effect on the Export function though.
Cheers,
Thankyou for the input. I ended up just running a sort routines in the ilogic routine i have to format the final spreadsheet as required.
HI
Can you advise how to do the sort routine on the created excel file. I am having the same issue where BOM is exported but excel columns are sorted alphabetically.
Thanks,
Bryan
Depending on how you are doing it... the attached code is VBA to reorder columns.
Note:
Private Sub ReorderXLBOM() Dim arrColOrder Dim ndx Dim Found Dim counter arrColOrder = Array("Item", "QTY", "Part Number", "Description", "Stock Number") counter = 1 On Error Resume Next For ndx = LBound(arrColOrder) To UBound(arrColOrder) 'Set Found = xlws.Rows("1:1").Find(arrColOrder(ndx),, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) Set Found = xlws.Rows("1:1").Find(arrColOrder(ndx), , -4163, 1, 2, 1, False) If Err.Number <> 0 Then MsgBox ("Error With Excel FIND function: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx) Err.Clear End If If Not Found Is Nothing Then If Found.Column <> counter Then Found.EntireColumn.Cut xlws.Columns(counter).Insert -4161 Application.CutCopyMode = False End If counter = counter + 1 End If Next If Err.Number <> 0 Then MsgBox ("Reorder Columns Rule Error: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx) Err.Clear End If End Sub
Hi Justin
My coding prowess is sketchy at best so can you please have a squizz at my code and advise how to add your VBA in to achieve the objective. The code is ilogic which I have pieced together. I basically import a custom BOM into the assembly (which has the columns as required) then export. I'm sure there is a more efficient way but it works, excepting for the reordering of columns.
Many thanks for your time.
Bryan
SyntaxEditor Code Snippet
Imports System.Windows.Forms Imports System.IO Sub Main() ' Get the active assembly Dim oDoc As AssemblyDocument = ThisApplication.ActiveDocument 'set a reference to the BOM Dim oBOM As BOM = oDoc.ComponentDefinition.BOM 'Get user path for custom BOM Dim oPath As String ' Search for the folder Dim Dialog = New FolderBrowserDialog() Dialog.ShowNewFolderButton = True Dialog.Description = "Choose Custom BOM Folder" ' Show dialog box If DialogResult.OK = Dialog.ShowDialog() Then ' User clicked 'ok' on dialog box - capture the export path oPath = Dialog.SelectedPath & "\" Else ' User clicked 'cancel' on dialog box - exit Return End If 'import custom BOM Dim ofilename As String 'assume filename exists ofilename = oPATH & "BRUCE EXPORT.xml" MessageBox.Show("Custom BOM is:" & ofilename, "Title") Call oBom.ImportBOMCustomization(ofilename) 'set the structured view to 'all levels' oBOM.StructuredViewFirstLevelOnly = False ' Make sure that the structured view is enabled. oBOM.StructuredViewEnabled = True 'set a reference to the "Structured" BOMView Dim oStructuredBOMView As BOMView oStructuredBOMView = oBOM.BOMViews.Item("Structured") 'End Sub ' Export the BOM view to an Excel file Dim ExportPath As String = ThisDoc.Path Dim oExportName = ThisDoc.FileName(False) 'without extension'get BOM Target folder path Dim oFolderBOM As String = Strings.Left(ExportPath, InStrRev(ExportPath, "\")) & "BOM" MessageBox.Show("Export Path is:" & oFolderBOM, "Title") 'Check for the BOM folder and create it if it does not exist If Not System.IO.Directory.Exists(oFolderBOM) Then System.IO.Directory.CreateDirectory(oFolderBOM) End If 'check For existing XLSX file And delete it If found If Dir(oFolderBom & "\" & oExportName & ".xlsx") <> "" Then Kill (oFolderBom & "\" & oExportName & ".xlsx") Else End If oStructuredBOMView.Export (oFolderBOM & "\" & oExportName & ".xlsx", kMicrosoftExcelFormat) 'define Excel Application object excelApp = CreateObject("Excel.Application") 'set Excel to run visibly'change to false if you want to run it invisibly excelApp.Visible = True 'open the workbook wb = excelApp.Workbooks.Open(oFolderBOM & "\" & oExportName & ".xlsx") 'set all of the columns to autofit excelApp.Columns.AutoFit 'suppress prompts (such as the compatibility checker) excelApp.DisplayAlerts = False 'save the workbook wb.Save End Sub 'close the workbook, uncomment if you want to close the xls file at the end'wb.Close
Here is a heavily reworked version of your code.
Note, the intent is for the BOM customization to be located in one location and then just imported from there.
Also note that the BOM order is set by an array of strings that corresponds to the column titles.
AddReference "Microsoft.Office.Interop.Excel" 'To use excel Imports System.Windows.Forms Imports System.IO Imports Microsoft.Office.Interop.Excel 'To use excel Sub Main() Dim oDoc As AssemblyDocument = ThisApplication.ActiveDocument 'Dim oPath As String 'oPath = oFolderDlg 'oFileName = oPath & "Bruce.xml" ExportBOM(oDoc) End Sub Private BOMCustomizationFile As String = "C:\CustFile.xml" Private excelApp As Microsoft.Office.Interop.Excel.Application Private xlws As Worksheet Sub ExportBOM(oDoc As Document) 'File Path Creation/Processing 'Note, the following line will cause an error if the document is not saved. Dim oExportPath As String = System.IO.Path.GetDirectoryName(oDoc.FullFileName) & "\BOM\" Dim oExportName As String = oExportPath & System.IO.Path.GetFileNameWithoutExtension(oDoc.FullFileName) 'without extension'get BOM Target folder path If Not System.IO.Directory.Exists(oExportPath) Then: System.IO.Directory.CreateDirectory(oExportPath): End If If Dir(oExportName & ".xlsx") <> "" Then Kill (oExportName & ".xlsx") End If 'Inventor BOM Processing Dim oBOM As BOM = oDoc.ComponentDefinition.BOM oBOM.ImportBOMCustomization(BOMCustomizationFile) oBOM.StructuredViewEnabled = True oBOM.StructuredViewFirstLevelOnly = False Dim oStructuredBOMView As BOMView oStructuredBOMView = oBOM.BOMViews.Item("Structured") oStructuredBOMView.Export (oExportName & ".xlsx", kMicrosoftExcelFormat) 'Excel processing excelApp = CreateObject("Excel.Application") excelApp.Visible = True excelApp.DisplayAlerts = False wb = excelApp.Workbooks.Open(oExportName & ".xlsx") xlws = wb.Worksheets(1) Call ReorderXLBOM() excelApp.Columns.AutoFit excelApp = Nothing End Sub Private Sub ReorderXLBOM() Dim ndx As Integer Dim Found As Range Dim counter As Integer = 1 Dim arrColOrder() As String = {"Item", "QTY", "Part Number", "Description", "Stock Number"} 'arrColOrder = Array("Item", "QTY", "Part Number", "Description", "Stock Number") On Error Resume Next For ndx = LBound(arrColOrder) To UBound(arrColOrder) 'Set Found = xlws.Rows("1:1").Find(arrColOrder(ndx),, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) Found = xlws.Rows("1:1").Find(arrColOrder(ndx), , -4163, 1, 2, 1, False) If Err.Number <> 0 Then MsgBox ("Error With Excel FIND function: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx) Err.Clear End If If Not Found Is Nothing Then If Found.Column <> counter Then Found.EntireColumn.Cut xlws.Columns(counter).Insert(-4161) excelApp.CutCopyMode = False End If counter = counter + 1 End If Next If Err.Number <> 0 Then MsgBox ("Reorder Columns Rule Error: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx) Err.Clear End If End Sub Public Function oFolderDlg Dim dialog = New FolderBrowserDialog() ' dialog.SelectedPath = Application.StartupPath dialog.ShowNewFolderButton = True ' openFileDialog1.InitialDirectory If DialogResult.OK = dialog.ShowDialog() Then oPath = dialog.SelectedPath Else MsgBox("No File Selected. Aborting Rule") oPath = "" End If Return oPath End Function
Brilliant code, I want to give you kudo's but I can click on the button all I want, nothing works (im signed in)
Edit: Ok, I had to click the icon, not the text, even though it looks like you can also click on the text. First time that has me fooled
I get this error:
Excel cannot open the file 'A00001.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.
What am I missing?
Maybe Inventor 2015 can only save to xls and not xlsx?
Try renaming the file to xls and open it in Excel.
How possible is it to modify this create from an existing XLS template instead?
If you simply mean making the above code work with XLS, then you just have to replace all the ".xlsx" strings with ".xls"
No sorry, I did that and its working as expected.
I was wondering if its possible to adapt the code to use a pre-existing excel template
Curtis' blog shows how to do it through a drawing part list:
http://inventortrenches.blogspot.co.uk/2011/06/ilogic-export-parts-list-with-options.html
But I need a rule that will export all levels of the structured BOM (ideally to a template which will have my costing columns on it)
From Curtis' code it looks like you'll just have to add a third parameter to the Export function:
oOptions = ThisApplication.TransientObjects.CreateNameValueMap 'specify an existing template file 'to use For formatting colors, fonts, etc oOptions.Value("Template") = "C:\Temp\PartListExport.xls" oStructuredBOMView.Export (oExportName & ".xlsx", kMicrosoftExcelFormat, oOptions)
Cheers,
It's really amazing how random that inventor excel export winds up, so I have reluctantly added MMM's column sorting code into my export "program":
'heavily reformat exported xls... 'On Error Resume Next Set excel_app = CreateObject("Excel.Application") 'excel_app.Visible = True excel_app.Workbooks.Open (bomname) Dim rows As Integer Dim rowbottom As String rows = excel_app.ActiveSheet.UsedRange.rows.Count rowbottom = "A1" & ":" & "A" & rows excel_app.Range("A1").EntireColumn.Insert excel_app.Range(rowbottom) = "=TRIM(LEFT(SUBSTITUTE(MID(CELL(""filename"",A1),FIND(""["",CELL(""filename"",A1))+1,255),"".xl"",REPT("" "",255)),255))" excel_app.Range("F1").EntireColumn.Insert excel_app.Range("F1") = "Assembly Qty" excel_app.Range("A1") = "Order" 'try some sorting Dim arrColOrder Dim ndx As Integer Dim Found As Range Dim counter As Integer arrColOrder = Array("Order", "Item", "Part Number", "BOM Structure", "QTY", "Assembly Qty", "A1 Router", "A1 Time", "B1 Laser", "B1 Time", "C1 Saw-AL", "C1 Time", "D1 Saw-ST", "D1 Time", "E1 C-Axis", "E1 Time", "F1 Machining", "F1 Time", "G1 Debur", "G1 Time", "H1 Brake", "H1 Time", "I1 Weld", "I1 Time", "J1 RWeld", "J1 Time", "K1 Assembly", "K1 Time", "L1 Outsource", "L1 Time", "M1 Ship", "Bulk", "Setup") counter = 1 On Error Resume Next For ndx = LBound(arrColOrder) To UBound(arrColOrder) 'was not successful setting global variable for worksheet, tried some bfi: 'Set Found = Sheet1.rows("1:1").Find(arrColOrder(ndx), , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) Set Found = Sheet1.rows("1:1").Find(arrColOrder(ndx), , -4163, 1, 2, 1, False) If Err.Number <> 0 Then MsgBox ("Error With Excel FIND function: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx) Err.Clear End If If Not Found Is Nothing Then If Found.Column <> counter Then Found.EntireColumn.Cut xlws.Columns(counter).Insert -4161 Application.CutCopyMode = False End If counter = counter + 1 End If Next If Err.Number <> 0 Then MsgBox ("Reorder Columns Rule Error: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx) Err.Clear End If excelApp.Columns.AutoFit excel_app.ActiveWorkbook.Save excel_app.ActiveWorkbook.Close
I believe if I roll back to xlws in this code instead of "Sheet1" and Public xlws as string, xlws = Sheet1, this would work, but I don't know where to declare these global variables!!
When this runs at best, I get error with excel find 424: object required - obviously the arrayed names aren't being passed.
I tried making a new otherwise empty module where I set xlws as a public string, that works, gets me a different error of invalid qualifier for the set found = xlws.rows line. I can dim xlws as Sheet1 inside of the sub, get the same result.
I'm sure I'll learn something from this... Thanks everyone.
Try this instead:
Feed it the worksheet you want to process in the call in your code, DON'T modify the code aside from the 2 bolded sections; header row indicator, and the array of the desired column order.
Private Sub ReorderXLBOM(ByVal excelapp As Application, ByVal xlws As Worksheet) Dim ndx As Integer Dim Found As Range Dim counter As Integer = 1 Dim arrColOrder() As String = {"Item", "QTY", "Part Number", "Description", "Stock Number"} 'arrColOrder = Array("Item", "QTY", "Part Number", "Description", "Stock Number") On Error Resume Next For ndx = LBound(arrColOrder) To UBound(arrColOrder) 'Set Found = xlws.Rows("1:1").Find(arrColOrder(ndx),, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) Found = xlws.Rows("1:1").Find(arrColOrder(ndx), , -4163, 1, 2, 1, False) If Err.Number <> 0 Then MsgBox ("Error With Excel FIND function: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx) Err.Clear End If If Not Found Is Nothing Then If Found.Column <> counter Then Found.EntireColumn.Cut xlws.Columns(counter).Insert(-4161) excelApp.CutCopyMode = False End If counter = counter + 1 End If Next If Err.Number <> 0 Then MsgBox ("Reorder Columns Rule Error: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx) Err.Clear End If End Sub