I have recently upgraded from 2019 to 2021. When I was using inventor 2019, I could export 2 to 3 different boms to the same excel file and each bom would be placed in its own tab in the excel file. see attached
Now with upgrading to 2021 when I export multiple boms to the same excel file, invenor does not create a new tab in the excel file and just over writes the first tab in excel. Is there a work around for having inventor export the bom to a new tab in excel like it did in 2019?
This definitely sounds doable. There are multiple ways to do this. For one, you could check to see if a tab (sheet) with that name already exists in the target Excel file, and if it does, create a new sheet with a slightly different name, before moving forward, and write to that new sheet. You could just add an Integer to the end of the sheet name (or add 1 to the Integer if it is already in the name). Or you could simply set your code up to always 'Add' a new sheet to the existing Excel workbook, then write to that new sheet each time (there are likely options where Excel will automatically give the new sheet a default incremented new name). We may have to review your existing code if you want a more precise, ready to use solution. Which programming language are you using for this...iLogic (vb.net), VBA, or other?
Wesley Crihfield
(Not an Autodesk Employee)
See if this works any better for you.
Just a few fairly minor tweaks. Nothing major yet. We're still not really digging into the Excel application with this process. But we can if you think you may need it. Here is a link to one of my contribution posts, which shows you how to access and work with the Excel application and its objects, similarly to how you would with Inventor. It includes the proper 'Header' lines to reference and import the necessary things so iLogic will recognize the Excel objects and keep providing the little pop-up helpers.
If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kDrawingDocumentObject Then
MsgBox("This rule '" & iLogicVb.RuleName & "' only works for Drawing Documents.",vbOKOnly, "WRONG DOCUMENT TYPE")
Exit Sub
End If
Dim oDDoc As DrawingDocument = ThisDrawing.Document
Dim oUParams As UserParameters = oDDoc.Parameters.UserParameters
Dim oUParam As UserParameter
Try
oUParam = oUParams.Item("lSheet")
Catch
oUParam = oUParams.AddByValue("lSheet", "", UnitsTypeEnum.kTextUnits)
MultiValue.SetList("lSheet", "MT", "MESS")
End Try
Dim oSheetNames As New ArrayList
Dim oSheet As Inventor.Sheet
For Each oSheet In oDDoc.Sheets
oSheetNames.Add(oSheet.Name)
Next
MultiValue.List("lSheet") = oSheetNames
Repeat :
Dim oSheetName As String= InputListBox("Select which sheet to get BOM", MultiValue.List("lSheet"), Parameter("ISheet"), Title := "Selct BOM", ListName := "Sheet List")
'get the path and name of the drawing file
'Dim oPathAndName As String = Left(oDDoc.FullDocumentName,Len(oDDoc.FullDocumentName) - 4)
oSheet = oDDoc.Sheets(oSheetName) ' sheet by name
If oSheet.PartsLists.Count > 0 Then
Dim oPartslist As PartsList = oSheet.PartsLists(1)
Dim oDesktop As String = System.Environment.GetFolderPath(System.Environment.SpecialFolder.DesktopDirectory)
Dim oOptions As NameValueMap = ThisApplication.TransientObjects.CreateNameValueMap
'oOptions.Value("Template") = oDesktop & "\EAP Cutlist Master.xlsx"
oOptions.Add("ExportedColumns", "ITEM;EXTRUSION;DESCRIPTION;LENGTH;ITEM QTY;CATEGORY;FINISH")
oOptions.Add("StartingCell", "A1")
oOptions.Add("TableName", IO.Path.GetFileNameWithoutExtension(oDDoc.FullFileName))
oOptions.Add("IncludeTitle", False)
oOptions.Add("AutoFitColumnWidth", True)
oPartslist.Export(oDesktop & "\EAP Cutlist Master.xlsx",PartsListFileFormatEnum.kMicrosoftExcel, oOptions)
Else
oRetry = MsgBox("That sheet doesn't contain any PartsLists." & vbCrLf & _
"Would you Like To Try another Sheet ? ", vbYesNo + vbQuestion, " NO P - LIST FOUND ")
If oRetry = True Then GoTo Repeat
End If
If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click 'LIKE' 👍.
Wesley Crihfield
(Not an Autodesk Employee)
Also, just checking but you may need to read up on the newer changes to how Inventor and iLogic interact with Excel.
Here are a couple of links, just in case they may be partly the reason in the changed functionality of your code.
https://help.autodesk.com/view/INVNTOR/2021/ENU/?guid=GUID-4F161053-30A5-4966-9921-988D36EB28CE
https://help.autodesk.com/view/INVNTOR/2021/ENU/?guid=GUID-A65240D6-4854-42B9-B9A6-15453ACB5BC0
If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click 'LIKE' 👍.
Wesley Crihfield
(Not an Autodesk Employee)
I would stick with iLogic if I were you. It is based on VB.NET. VB.NET and VBA are both based on Visual Basic coding language, but the VB.NET system is newer, more dynamic, has more documentation available, and is less restrictive. It's up to you though, and what fits you and your style best.
I think those Excel COM engine settings mainly effect when you use iLogic's built-in GoExcel functionality. Since we are not using any of that, it may not effect this. This code interacts directly with Excel using vb.net code, instead of the GoExcel stuff.
Here is an updated version of the code, in which I have incorporated some additional code to ensure it uses a new sheet (tab) in the Excel document each time. I put our earlier code into the middle of this new code, mostly as its own Sub. I designed the sub to accept the DrawingDocument, the Excel file name, and the Excel sheet name as input variables, so I don't have to redefine them within the sub. I wanted the drawing document defined outside of the sub, so I could use that variable to rename the Excel sheet (tab) after the sub runs.
We may have to do something different with the 'Repeat' loop, depending on how you are using it. Are you trying to export multiple PartsLists from the same drawing, or is this loop primarily just to find the PartsList? If you are attempting to export multiple PartsLists from the same drawing, then we may need to include drawing sheet name to keep it unique, and move the loop to just outside of where the Sub is ran, so it will repeat the whole sub, not just the export process, because a new Excel sheet (with a new name) will need to be created for each loop. If the loop is only to find the PartsList, I would suggest eliminating this loop and simply looping through each sheet (near the beginning), checking the PartsList.Count of each until you find the first one, then resume the rest of the code.
Here's what I've got so far:
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel
Sub Main
'Specify the Excel file's (Workbook) full path & file name (with extension)
'It is calling a Windows OpenFileDialog to allow the user to do this
Dim oDesktop As String = System.Environment.GetFolderPath(System.Environment.SpecialFolder.DesktopDirectory)
Dim oFileName As String = oDesktop & "\EAP Cutlist Master.xlsx"
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
oExcelApp.DisplayAlerts = False
oExcelApp.Visible = False
'Attempt to open the specified Workbook (file) using the supplied file name
Dim oWB As Workbook
Try
oWB = oExcelApp.Workbooks.Open(oFileName)
Catch oEx As Exception
MsgBox("The attempt to open the Excel file named '" & oFileName & "' failed." & vbCrLf & _
"The error message for this failure is as follows:" & vbCrLf & _
oEx.Message & vbCrLf & vbCrLf & _
"Its 'StackTrace is as follows:" & vbCrLf & _
oEx.StackTrace & vbCrLf & vbCrLf & _
"Its Source is as follows:" & vbCrLf & _
oEx.Source, vbOKOnly + vbCritical, "Couldn't Open File")
Exit Sub
End Try
'Add a new Worksheet (tab) to the open Workbook
'all input options are optional, so its name will be automated
'(its name can be changed later, if needed)
Dim oWS As Worksheet = oWB.Worksheets.Add()
Dim oWSName As String = oWS.Name
'<<<< HERE'S WHERE WE'RE RUNNING YOUR CODE (MOSTLY AS A SUB) >>>
'We're defining the document outside of the sub, so we can use its name after the sub
If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kDrawingDocumentObject Then
MsgBox("This rule '" & iLogicVb.RuleName & "' only works for Drawing Documents.",vbOKOnly, "WRONG DOCUMENT TYPE")
Exit Sub
End If
Dim oDDoc As DrawingDocument = ThisDrawing.Document
'we're giving it the name of the new sheet (tab) for use in the Export settings
ExportBOM(oDDoc, oFileName, oWSName)
'Now we can attempt to rename the tab in Excel (if the name isn't already being used)
Dim oSheetName As String = IO.Path.GetFileNameWithoutExtension(oDDoc.FullFileName)
Try
oWS.Name = oSheetName
oWB.Save
oWB.Close
oExcelApp.Quit
Catch
MsgBox("Unable to change the Excel sheet name to:" & vbCrLf & _
oSheetName & vbCrLf & _
"Either the name isn't usable or it is already being used." & vbCrLf & _
"If multiple PartsLists are being exported from the same drawing document," & vbCrLf & _
"you may need to add the sheet name (or similar) to the name of the Excel worksheet to make it unique." & vbCrLf & _
"Making the Excel document visible, so the sheet name can be manually edited.", vbOKOnly, " ")
oExcelApp.Visible = True
End Try
'we shouldn't need this due to Export settings
'oWS.Columns.AutoFit
End Sub
Sub ExportBOM(oDrDoc As DrawingDocument, oExcelFile As String, oTableName As String)
Dim oUParams As UserParameters = oDrDoc.Parameters.UserParameters
Dim oUParam As UserParameter
Try
oUParam = oUParams.Item("lSheet")
Catch
oUParam = oUParams.AddByValue("lSheet", "", UnitsTypeEnum.kTextUnits)
MultiValue.SetList("lSheet", "MT", "MESS")
End Try
Dim oSheetNames As New ArrayList
Dim oSheet As Inventor.Sheet
For Each oSheet In oDrDoc.Sheets
oSheetNames.Add(oSheet.Name)
Next
MultiValue.List("lSheet") = oSheetNames
Repeat :
Dim oSheetName As String= InputListBox("Select which sheet to get BOM", MultiValue.List("lSheet"), Parameter("ISheet"), Title := "Selct BOM", ListName := "Sheet List")
'get the path and name of the drawing file
'Dim oPathAndName As String = Left(oDDoc.FullDocumentName,Len(oDDoc.FullDocumentName) - 4)
oSheet = oDrDoc.Sheets(oSheetName) ' sheet by name
If oSheet.PartsLists.Count > 0 Then
Dim oPartslist As PartsList = oSheet.PartsLists(1)
Dim oDesktop As String = System.Environment.GetFolderPath(System.Environment.SpecialFolder.DesktopDirectory)
Dim oOptions As NameValueMap = ThisApplication.TransientObjects.CreateNameValueMap
'oOptions.Value("Template") = oDesktop & "\EAP Cutlist Master.xlsx"
oOptions.Add("ExportedColumns", "ITEM;EXTRUSION;DESCRIPTION;LENGTH;ITEM QTY;CATEGORY;FINISH")
oOptions.Add("StartingCell", "A1")
'This is where we're specifying the name of the new sheet (tab) we created
oOptions.Add("TableName", oTableName)
oOptions.Add("IncludeTitle", False)
oOptions.Add("AutoFitColumnWidth", True)
oPartslist.Export(oExcelFile, PartsListFileFormatEnum.kMicrosoftExcel, oOptions)
Else
oRetry = MsgBox("That sheet doesn't contain any PartsLists." & vbCrLf & _
"Would you Like To Try another Sheet ? ", vbYesNo + vbQuestion, " NO P - LIST FOUND ")
If oRetry = True Then GoTo Repeat
End If
End Sub
If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click 'LIKE' 👍.
Wesley Crihfield
(Not an Autodesk Employee)
The repeat function was if you selected the wrong sheet then it would loop back to the beginning so you can select the correct sheet. It never worked anyways. You just had to start the rule over if you selected the wrong sheet. I do have another ilogic program that will search all of the sheets for all bom's and export them. The reason I do have separate programs is that there are certain documents that have multiple boms but I want to just pick just one bom to export. Then there is other drawings that have multiple boms and i want to export all boms to excel. At the time it was just easier for me to write 2 separate programs to export the boms as I needed. I have tried your program but I am getting an error. See Below. I know you said that the COM in the inventor ilogic configuration will not affect this since we are running it with VB.NET. I am still trying to dig into why this is happening. Thank you for your help it is much appreciated.
It looks like it is having trouble right at the top where it is attempting to start Excel.
Maybe try changing this line in the code:
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Applicationwith this line:
Dim oExcelApp As New Microsoft.Office.Interop.Excel.ApplicationClassI have used this line many times before too, and it works for me, but that's maybe no guarantee it will work for you.
There are multiple ways to open the Excel app using code, but it's not all those ways are the most user friendly. Another way I have started Excel in the past is using the Process method, but it's not as easy to obtain the Application object using this method.
Here's the Process method for starting Excel: (likely not the best fit for this situation)
Dim oProcess As System.Diagnostics.Process
Dim oStartInfo As New System.Diagnostics.ProcessStartInfo("C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE")
oStartInfo.WindowStyle = ProcessWindowStyle.Maximized
oProcess.Start(oStartInfo)Then there is the old generic way. This way may work for you, where others may not.
However, keep in mind, when getting the Excel application this way, always remember to destroy the reference to it when done using it, so it doesn't keep running in the background.
Dim oExcelApp = CreateObject("Excel.Application")
'use this variable throughout your code as usual
'then use this near the end, after your done using
'any of the Excel references
oExcelApp = NothingLet me know how these suggestions work out for you.
If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click 'LIKE' 👍.
Wesley Crihfield
(Not an Autodesk Employee)
