Hello my dear Friends,
New Challenge here and i need some tweaking to make it run.
The goal is to refresh all thumbnails then export assembly bom parts only to excel file in specific location and with
a custom column template.
Kudos to @Curtis_Waguespack for most of the code which works amazing.
Problem number one. Some of the parts are beams/profiles that i would like to change to front view before getting a
screenshot but only for those, the rest i would like to have the isometric view.
I would like to do that with a check on an iproperty that i have on all my models and has the name "Materialtype"
if that iproperty has value equal to "PROFIL" then i would like to face the front view before taking the shot.
I can't seem to find the correct command for the if to check.
I get this error:
Problem number two. I would like to put a check before running the code to check if it is an assembly because if it isn't i get a fatal error
instead to normally abord.
Problem number three. The excel exports at the desired path/name only problem is that i want to make it friendly reading so i would like to have a template then populate the columns of the bom without the name of the columns,
lets say start from A2 of excel file leaving the first line to the template that i will make. How do i do that? How i write inside an excel template then save as document name.
Here is the code now:
'get current color scheme name oColorScheme = ThisApplication.ActiveColorScheme.Name 'get current color scheme background type oBackGroundType = ThisApplication.ColorSchemes.BackgroundType 'Change to Presentation (white background) ThisApplication.ColorSchemes.Item("Presentation").Activate 'set to use one color background type ThisApplication.ColorSchemes.BackgroundType = _ BackgroundTypeEnum.kOneColorBackgroundType ' i would like some code here to check if it is assembly, because if it is not i get fatal error instead of close. Dim oAsmDoc As AssemblyDocument oAsmDoc = ThisApplication.ActiveDocument Dim oDoc As Document Dim oRefFile As FileDescriptor For Each oDoc In oAsmDoc.AllReferencedDocuments ThisApplication.Documents.Open(oDoc.FullFileName, True) For Each oSketch In oDoc.ComponentDefinition.Sketches oSketch.Visible = False Next 'set work plane visibility For Each oWorkPlane In oDoc.ComponentDefinition.WorkPlanes oWorkPlane.Visible = False Next 'set work axis visibility For Each oWorkAxis In oDoc.ComponentDefinition.WorkAxes oWorkAxis.Visible = False Next 'set work point visibility For Each oWorkPoint In oDoc.ComponentDefinition.WorkPoints oWorkPoint.Visible = False Next If String.IsNullOrEmpty(oDoc.PropertySets.Item("PROFIL").Item("Materialtype").Value) 'problem in this line ThisApplication.CommandManager.ControlDefinitions.Item("AppFrontViewCmd").Execute 'set front view 'set iproperty to use current view on save oDoc.SetThumbnailSaveOption _ (ThumbnailSaveOptionEnum.kActiveWindow) Else 'Set iproperty To use ISO View On save oDoc.SetThumbnailSaveOption _ (ThumbnailSaveOptionEnum.kActiveComponentIsoViewOnSave) End If 'set iproperty to use ISO view on save oDoc.SetThumbnailSaveOption _ (ThumbnailSaveOptionEnum.kActiveComponentIsoViewOnSave) 'save and close the file oDoc.Save oDoc.Close Next 'Change back to original scheme ThisApplication.ColorSchemes.Item(oColorScheme).Activate 'Change back to original back ground type ThisApplication.ColorSchemes.BackgroundType = oBackGroundType Dim oDoc2 As AssemblyDocument = ThisDoc.Document Dim oBOM As BOM = oDoc2.ComponentDefinition.BOM oBOM.PartsOnlyViewEnabled=True ThisBOM.Export("Parts Only", "C:\Users\KTrifonidisNyfan\Documents\Bom\" & (Left(ThisDoc.FileName(False), 15))& ".xlsx", FileFormatEnum.kMicrosoftExcelFormat)
Thanks a lot in advance.
Solved! Go to Solution.
Solved by K.TRYFONIDIS. Go to Solution.
I think this will resolve questions 1 and 2, I did this real quick, so if you see any issues post back.
I had a quick look but did not see a way to specify a template and start cell using the BOM export, but maybe I missed something, and someone else can help with that.
I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com
'get current color scheme name
oColorScheme = ThisApplication.ActiveColorScheme.Name
'get current color scheme background type
oBackGroundType = ThisApplication.ColorSchemes.BackgroundType
'Change to Presentation (white background)
ThisApplication.ColorSchemes.Item("Presentation").Activate
'set to use one color background type
ThisApplication.ColorSchemes.BackgroundType = _
BackgroundTypeEnum.kOneColorBackgroundType
' i would like some code here to check if it is assembly, because if it is not i get fatal error instead of close.
If Not ThisApplication.ActiveDocument.DocumentType = DocumentTypeEnum.kAssemblyDocumentObject Then
MsgBox("Must be an assembly", , "iLogic")
Return 'exit rule
End If
Dim oAsmDoc As AssemblyDocument
oAsmDoc = ThisApplication.ActiveDocument
Dim oDoc As Document
Dim oRefFile As FileDescriptor
For Each oDoc In oAsmDoc.AllReferencedDocuments
ThisApplication.Documents.Open(oDoc.FullFileName, True)
For Each oSketch In oDoc.ComponentDefinition.Sketches
oSketch.Visible = False
Next
'set work plane visibility
For Each oWorkPlane In oDoc.ComponentDefinition.WorkPlanes
oWorkPlane.Visible = False
Next
'set work axis visibility
For Each oWorkAxis In oDoc.ComponentDefinition.WorkAxes
oWorkAxis.Visible = False
Next
'set work point visibility
For Each oWorkPoint In oDoc.ComponentDefinition.WorkPoints
oWorkPoint.Visible = False
Next
oPropset = oDoc.PropertySets.Item("User Defined Properties")
Try
oProp = oPropset.Item("Materialtype").Value
Catch
oProp = ""
End Try
If oProp = "" Then
ThisApplication.CommandManager.ControlDefinitions.Item("AppFrontViewCmd").Execute 'set front view
'set iproperty to use current view on save
oDoc.SetThumbnailSaveOption _
(ThumbnailSaveOptionEnum.kActiveWindow)
ElseIf oProp = "PROFIL" Then
'Set iproperty To use ISO View On save
oDoc.SetThumbnailSaveOption _
(ThumbnailSaveOptionEnum.kActiveComponentIsoViewOnSave)
End If
'set iproperty to use ISO view on save
oDoc.SetThumbnailSaveOption _
(ThumbnailSaveOptionEnum.kActiveComponentIsoViewOnSave)
'save and close the file
oDoc.Save
oDoc.Close
Next
'Change back to original scheme
ThisApplication.ColorSchemes.Item(oColorScheme).Activate
'Change back to original back ground type
ThisApplication.ColorSchemes.BackgroundType = oBackGroundType
Dim oDoc2 As AssemblyDocument = ThisDoc.Document
Dim oBOM As BOM = oDoc2.ComponentDefinition.BOM
oBOM.PartsOnlyViewEnabled = True
'oPath = "C:\Users\KTrifonidisNyfan\Documents\Bom\"
oPath = "C:\Temp\"
ThisBOM.Export("Parts Only", oPath & _
(Left(ThisDoc.FileName(False), 15)) & ".xlsx", FileFormatEnum.kMicrosoftExcelFormat)
Hi @K.TRYFONIDIS. @Curtis_Waguespack is correct, that there is no way to specify and use a Template when using either if the built-in methods for exporting a BOMView (Links to the online help pages: ThisBOM.Export ; BOMView.Export). The PartsList.Export method allows it, but not on the BOM side. So when a Template is needed, most folks just abandon using Inventor's built-in export methods, and create their own custom code routine for doing the whole export task the way they want it done. It is certainly more complex to create your own custom export process, but usually not impossible, if you think it would be worthwhile. Some others here on the forums have gone down the same route, due to certain limitations of the built-in methods, and having more custom needs that were important enough to them.
Wesley Crihfield
(Not an Autodesk Employee)
3.
If your feeling paticularly lazy you can do something like this after your code
'Dim filename As String 'filename = oPath & (Left(ThisDoc.FileName(False), 15)) & ".xlsx" GoExcel.Open(filename) GoExcel.CellValue(filename, "Sheet1", "A2") = "Part Number" GoExcel.CellValue(filename, "Sheet1", "A2") = "Description" GoExcel.Save()
Firstly, thank you all for taking the time to respond and help me with the issues. @Curtis_Waguespack Problem one and two seems solved! 🙂
About problem number three, i came up with this post ( @Curtis_Waguespack again ) which i tried it a bit and it works like a charm only that i have to somehow change this line with my bom from inventor assembly without the columns names, since i have made a template ready to welcome the data. I could just copy paste the cells to the template but this is not so professional.
'Insert data into Excel.
With excelApp
.Range("A1").Select
.ActiveCell.Value = "Hello, " & myName
End With
With this command i can choose to start from A2 cell, because first line has my desired names from template.
Now i would like to know how can put the data from the bom inside this template starting from A2 cell and not transfering column names.
I will try to find something tommorow in office like:
.Range("A2").Select
.ActiveCell.Value =ThisBOM.Export
if copy paste works it should be something arround that works.
I will see how it goes in the morning.
Thank you all again in advance.
Hey this is not so bad either! I don't care about the way we do it as long as i get the result i want! 😄
Actually i find it very clever.
Thank you
I just did something similar for a customer recently, as far as the copy and paste in excel after exporting from Inventor. It was dealing with drawings, but the copy and paste from excel part of this was about the same, so I had a look at it and came up with this example.
This example exports the BOM, then opens it and the template, and copies from the exported into the template, then closes the exported spreadsheet, and saves a copy of the template as the exported spreadsheet name.
I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com
'get current color scheme name
oColorScheme = ThisApplication.ActiveColorScheme.Name
'get current color scheme background type
oBackGroundType = ThisApplication.ColorSchemes.BackgroundType
'Change to Presentation (white background)
ThisApplication.ColorSchemes.Item("Presentation").Activate
'set to use one color background type
ThisApplication.ColorSchemes.BackgroundType = _
BackgroundTypeEnum.kOneColorBackgroundType
' i would like some code here to check if it is assembly, because if it is not i get fatal error instead of close.
If Not ThisApplication.ActiveDocument.DocumentType = DocumentTypeEnum.kAssemblyDocumentObject Then
MsgBox("Must be an assembly", , "iLogic")
Return 'exit rule
End If
Dim oAsmDoc As AssemblyDocument
oAsmDoc = ThisApplication.ActiveDocument
Dim oDoc As Document
Dim oRefFile As FileDescriptor
For Each oDoc In oAsmDoc.AllReferencedDocuments
ThisApplication.Documents.Open(oDoc.FullFileName, True)
For Each oSketch In oDoc.ComponentDefinition.Sketches
oSketch.Visible = False
Next
'set work plane visibility
For Each oWorkPlane In oDoc.ComponentDefinition.WorkPlanes
oWorkPlane.Visible = False
Next
'set work axis visibility
For Each oWorkAxis In oDoc.ComponentDefinition.WorkAxes
oWorkAxis.Visible = False
Next
'set work point visibility
For Each oWorkPoint In oDoc.ComponentDefinition.WorkPoints
oWorkPoint.Visible = False
Next
oPropset = oDoc.PropertySets.Item("User Defined Properties")
Try
oProp = oPropset.Item("Materialtype").Value
Catch
oProp = ""
End Try
If oProp = "" Then
ThisApplication.CommandManager.ControlDefinitions.Item("AppFrontViewCmd").Execute 'set front view
'set iproperty to use current view on save
oDoc.SetThumbnailSaveOption _
(ThumbnailSaveOptionEnum.kActiveWindow)
ElseIf oProp = "PROFIL" Then
'Set iproperty To use ISO View On save
oDoc.SetThumbnailSaveOption _
(ThumbnailSaveOptionEnum.kActiveComponentIsoViewOnSave)
End If
'save and close the file
oDoc.Save
oDoc.Close
Next
'Change back to original scheme
ThisApplication.ColorSchemes.Item(oColorScheme).Activate
'Change back to original back ground type
ThisApplication.ColorSchemes.BackgroundType = oBackGroundType
Dim oDoc2 As AssemblyDocument = ThisDoc.Document
Dim oBOM As BOM = oDoc2.ComponentDefinition.BOM
oBOM.PartsOnlyViewEnabled = True
oRowCount = oBOM.BOMViews.Item("Parts Only").BOMRows.Count
'oPath = "C:\Users\KTrifonidisNyfan\Documents\Bom\"
'oFile = oPath & Left(ThisDoc.FileName(False), 15) & ".xlsx"
oPath = "C:\Temp\"
oFile = oPath & ThisDoc.FileName(False) & ".xlsx"
oTemplate = oPath & "Test_Template.xlsx"
ThisBOM.Export("Parts Only", oFile, FileFormatEnum.kMicrosoftExcelFormat)
' Create the Excel application.
oExcel = CreateObject("Excel.Application")
'open the exported workbook.
oWBook1 = oExcel.Workbooks.Open(oFile)
wSheet1 = oExcel.ActiveSheet
' Open the template workbook.
oWBook2 = oExcel.Workbooks.Open(oTemplate)
wSheet2 = oExcel.ActiveSheet
oExcel.DisplayAlerts = False
'' Make Excel visible
'oExcel.Visible = True
'copy the rows from the temp sheet to the active sheet
'thie example is using n+1 to paste into row 2 of the template file
With wSheet1
For n = 1 To oRowCount+1
.Cells(n, "A").EntireRow.Copy(wSheet2.Cells(n + 1, "A"))
Next
End With
'close the exported workbook
oWBook1.Close(SaveChanges :=False)
'save the template as the original (overwriting the original exported)
oWBook2.SaveAs(oFile)
'close excel
oExcel.Quit
Hello, good morning.
Three issues with the latest code. @Curtis_Waguespack
Issue 1. I get the name of the columns from assembly bom into the excel file.
Issue 2. Thumbnails don't transfer to the excel.
Issue 3. When pasting data from bom excel to template the bom excel overwrites the cell format.
I guess i have to stick with @dalton98 idea.
Possible help for issues.
Issue 1. Possible delete the row with a command?
Issue 2. I see that thumbnails have names like Picture 1, Picture 2, maybe there is a code to transfer images too? Also i know that thumbnails will always be at column N ( if that helps) since i have standard column template
I found this small code but i don't know if it helps or if i can use it.
Dim pictMove As Excel.Range = Globals.ThisAddIn.Application.Sheets("Sheet2").Range("A1:A1") Try For Each shp In Globals.ThisAddIn.Application.ActiveSheet.Shapes If (shp.Name = "Picture 1") Then shp.copy() End If Next pictMove.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll) Catch ex As Exception End Try
Issue 3. Possible to transfer only pure data and leave the format alone? I have to use something like
.PasteSpecial paste:=xlPasteValues
Ok after a lot of searching on the internet and motivation by you guys i found the solution to my problem, i used a wide combination of random codes and i made a code that does exactly what i needed without using any template.
You actually making the template inside ilogic.
The GoExcel command didn't worked out good, had many errors and there aren't many commands to customize to exported excel.
So.. here is the code good and Running. Is there better codes? Probably, but this one works. Hoping to get out of trouble some people. 🙂
Sub main '---- check if it is part doc = ThisDoc.ModelDocument If doc.DocumentType = kPartDocumentObject Then MessageBox.Show("bla bla bla") Exit Sub Else End If '--- making the thumbnails 'get current color scheme name oColorScheme = ThisApplication.ActiveColorScheme.Name 'get current color scheme background type oBackGroundType = ThisApplication.ColorSchemes.BackgroundType 'Change to Presentation (white background) ThisApplication.ColorSchemes.Item("Presentation").Activate 'set to use one color background type ThisApplication.ColorSchemes.BackgroundType = _ BackgroundTypeEnum.kOneColorBackgroundType Dim oAsmDoc As AssemblyDocument oAsmDoc = ThisApplication.ActiveDocument Dim oDoc As Document Dim oRefFile As FileDescriptor For Each oDoc In oAsmDoc.AllReferencedDocuments ThisApplication.Documents.Open(oDoc.FullFileName, True) ' Verify that the document is a part. If oDoc.DocumentType = kPartDocumentObject Then Dim oPartDoc As PartDocument = oDoc 'Manipulate part Dim model As String = oPartDoc.DisplayName For Each oSketch In oDoc.ComponentDefinition.Sketches oSketch.Visible = False Next 'set work plane visibility For Each oWorkPlane In oDoc.ComponentDefinition.WorkPlanes oWorkPlane.Visible = False Next 'set work axis visibility For Each oWorkAxis In oDoc.ComponentDefinition.WorkAxes oWorkAxis.Visible = False Next 'set work point visibility For Each oWorkPoint In oDoc.ComponentDefinition.WorkPoints oWorkPoint.Visible = False Next If iProperties.Value(model, "Custom", "Materialtype") = "bla bla bla" Then ThisApplication.CommandManager.ControlDefinitions.Item("AppFrontViewCmd").Execute 'set front view 'set iproperty to use current view on save oDoc.SetThumbnailSaveOption _ (ThumbnailSaveOptionEnum.kActiveWindow) Else 'Set iproperty To use ISO View On save oDoc.SetThumbnailSaveOption _ (ThumbnailSaveOptionEnum.kActiveComponentIsoViewOnSave) End If End If 'save and close the file oDoc.Save oDoc.Close Next 'Change back to original scheme ThisApplication.ColorSchemes.Item(oColorScheme).Activate 'Change back to original back ground type ThisApplication.ColorSchemes.BackgroundType = oBackGroundType '------- export bom Dim oDoc2 As AssemblyDocument = ThisDoc.Document Dim oBOM As BOM = oDoc2.ComponentDefinition.BOM oBOM.PartsOnlyViewEnabled = True oRowCount = oBOM.BOMViews.Item("Parts Only").BOMRows.Count oPath = "C:\bla bla bla" oFile = oPath & ThisDoc.FileName(False) & ".xlsx" ThisBOM.Export("Parts Only", oFile, FileFormatEnum.kMicrosoftExcelFormat) myXLS_File = oFile '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 'suppress prompts (such as the compatibility checker) excelApp.DisplayAlerts = False excelWorkbook = excelApp.Workbooks.Open(myXLS_File) excelSheet = excelWorkbook.Worksheets(1).activate 'Insert data into Excel. With excelApp .Range("A1").Value = "bla bla bla" .Range("B1").Value = "bla bla bla" .Range("C1").Value = "bla bla bla" .Range("D1").Value = "bla bla bla" .Range("E1").Value = "bla bla bla" .Range("F1").Value = "bla bla bla" .Range("G1").Value = "bla bla bla" .Range("H1").Value = "bla bla bla" .Range("I1").Value = "bla bla bla" .Range("J1").Value = "bla bla bla" .Range("K1").Value = "bla bla bla" .Range("L1").Value = "bla bla bla" .Range("M1").Value = "bla bla bla" .Range("N1").Value = "bla bla bla" .Range("A1:P1").Font.Bold = True .Range("A1:P1").Font.Size = 16 With .columns("A:P") .Range("A1:P1000").WrapText = True .Range("A1:P1000").RowHeight = 100 .Range("A1:P1000").ColumnWidth = 30 .VerticalAlignment = -4108 .HorizontalAlignment = -4108 End With End With 'set all of the columns to autofit excelApp.Columns.AutoFit 'save the file excelWorkbook.SaveAs(myXLS_File) excelWorkbook.Close excelApp.Quit excelApp = Nothing End Sub
Is there a way to answer "Yes" when Inventor asks if i wanna overwrite the existing table in the workbook?
There shouldn't be a message like this.
Are you sure you are exporting the excel file from Bom, inside assembly file?
Sorry I'm not, i'm using a similar code where the parts list is exported to a xlsx file, however the command almost the same as the BoM export, what happens is that the template file already has a sheet named as the same sheet name selected to export, then inventor warns you about this. I already used another codes to answer similar questions but i'm not been able to get rid of this one.
Maybe you can use a different name for the sheet.
Unless you post the code, we cant be sure of what is happening.
I can't use a different name because the template has formulas that get values from the "LISTA MATERIAIS" tab, and that is meant to be overriden, i just need to don't have to confirm in every code run.
Thats the code i'm adapting now.
'-------------Start of ilogic ------------------------------------------------ 'get the path and name of the drawing file path_and_name = ThisDoc.PathAndFileName(False) ' without extension 'define oDoc oDoc = ThisDoc.Document 'specify the drawing sheet 'oSheet = oDoc.Sheets("Sheet:1") ' sheet by name oSheet = oDoc.Sheets(1) ' first sheet ' say there is a Partslist on the sheet. oPartslist = oSheet.PartsLists(1) ' create a new NameValueMap object oOptions = ThisApplication.TransientObjects.CreateNameValueMap 'specify an existing template file 'to use For formatting colors, fonts, etc DESKTOP = System.Environment.GetFolderPath(Desktop) oOptions.Value("Template") = DESKTOP & "\TEMPLATE_ESTRUTURA.xlsx" 'specify the columns to export 'oOptions.Value("ExportedColumns") = "QTY;PART NUMBER;DESCRIPTION" 'specify the start cell oOptions.Value("StartingCell") = "A1" 'specify the XLS tab name 'here the file name is used oOptions.Value("TableName") = "LISTA MATERIAIS" 'ThisDoc.FileName(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(path_and_name & ".xlsx", _ PartsListFileFormatEnum.kMicrosoftExcel, oOptions) '-------------End of ilogic ------------------------------------------------
Ok, i think the only line you need is the
oOptions.Value("ReplaceExisting") = True
just before export.
So it should be like this:
oOptions.Value("AutoFitColumnWidth") = True
oOptions.Value("ReplaceExisting") = True
oPartslist.Export(path_and_name & ".xlsx", PartsListFileFormatEnum.kMicrosoftExcel, oOptions)
Unfortunately, I don't think that will work either, because that option is not available when exporting a PartsList object...at least it is not documented in their online documentation for that method.
https://help.autodesk.com/view/INVNTOR/2024/ENU/?guid=PartsList_Export
You might try using a couple of GoExcel lines, just as a quick test, to see if it helps any.
GoExcel.Open(sFile, sSheet) GoExcel.DisplayAlerts = False 'your line to export here GoExcel.Save GoExcel.Close GoExcel.QuitApplication
Wesley Crihfield
(Not an Autodesk Employee)
Another thought would be to attempt to find and delete the existing sheet within the Excel file, just before exporting the PartsList to that same Excel file. Or as suggested before, name the new 'table' a slightly different name in the export options, then use some extra code to delete the original sheet, then rename the new sheet the same as the previous one, after the export. Either way, you would probably need to access Excel's own API & Object Model to do so, instead of being able to simply use those GoExcel lines. Just a couple more ideas. There are a few settings within the Excel application itself that you can play around with also.
Wesley Crihfield
(Not an Autodesk Employee)
Can't find what you're looking for? Ask the community or share your knowledge.