Having some trouble with a BOM export and I'm not really sure where this is going wrong. Below is the error I get when running the iLogic that utilizes a reference to Excel. I don't want to use the iLogic built in GoExcel because it lacks usage. At the minute my script is just trying to open the file, but constantly errors out. If anyone has any suggestions I'd appreciate it.
AddReference "microsoft.office.interop.excel.dll"
Imports XL = Microsoft.Office.Interop.Excel
Sub Main()
' https://clintbrown.co.uk/2019/09/21/bom-export-with-ilogic/
'Adapted from Inventor API Samples by Clint Brown @ClintBrown3D
'iLogic code Originally posted at https://clintbrown.co.uk/bom-export-with-ilogic
Dim oDoc As Document
oDoc = ThisDoc.ModelDocument
'Ensure that we are in an Assembly file - Exit if not
If oDoc.DocumentType = kPartDocumentObject Then
MessageBox.Show("You need to be in an Assembly to Export a BOM", "@ClintBrown3D iLogic")
Return
End If
oDoc = ThisApplication.ActiveDocument
Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM
Dim projectNumber As String = Left(oDoc.DisplayName, 4)
Dim pathMod As String = ""
Dim oPath As String = System.Environment.GetFolderPath(Desktop) & "\Export BOMs\" & projectNumber & "\"
If Not System.IO.Directory.Exists(oPath) Then
System.IO.Directory.CreateDirectory(oPath)
End If
Dim refDoc As Document
Dim exportVer1, exportVer2, exportVer3, exportVer4, fileName As String
exportVer1 = "Export All BOMS for Epicor"
'exportVer2 = "Export Project BOMS for Epicor"
'exportVer3 = "Export Single Level Structured BOM"
'exportVer4 = "Parts Only - (Shows components in a flat list)"
'**************************************************************************************
'You can change the output path by editing CSVpath below - by default the path is the same as the assembly file
'CSVpath = ("C:\Inventor\") 'If you change the path, remember to keep a \ at the end
CSVpath = oPath
'**************************************************************************************
'Get user input for Export Type:
Dim MyArrayList As New ArrayList
MyArrayList.Add("")
MyArrayList.Add(exportVer1) ' All BOMS
'MyArrayList.Add("")
'MyArrayList.Add(exportVer2) ' Project BOMS
'MyArrayList.Add("")
'MyArrayList.Add(exportVer3) ' Single Level BOM
'MyArrayList.Add("")
'MyArrayList.Add(exportVer4) ' Parts Only
ClintsBoMExporter = InputListBox("Choose a BoM type to Export: " & ClintBrown3D , MyArrayList, d0, Title := "@ClintBrown3D: BoM Export ", ListName := "BoM Type")
If ClintsBoMExporter = exportVer1 Then : GoTo GoEpicorExport : End If
If ClintsBoMExporter = exportVer2 Then : GoTo GoProjectBOMExport : End If
If ClintsBoMExporter = exportVer3 Then :GoTo GoAllLevelsExport : End If
If ClintsBoMExporter = exportVer4 Then : GoTo GoPartExport : End If
If ClintsBoMExporter = "" Then : Return : End If
'STRUCTURED BoM ALL LEVELS:
GoAllLevelsExport:
' the structured view to 'all levels'
oBOM.StructuredViewFirstLevelOnly = False
' Make sure that the structured view is enabled.
oBOM.StructuredViewEnabled = True
Dim oStructuredBOMView As BOMView
oStructuredBOMView = oBOM.BOMViews.Item("Structured")
' Export the BOM view to an Excel file
fileName = CSVpath + pathMod + ThisDoc.FileName(False) + "-Export"+ ".xls"
oStructuredBOMView.Export(fileName, kMicrosoftExcelFormat)
GoTo GoLaunch :
' Export project BOMS
GoProjectBOMExport :
MsgBox("Not ready")
Exit Sub
' Error's in this function
'STRUCTURED BoM for Epicor
GoEpicorExport :
oBOM.StructuredViewFirstLevelOnly = True
oBOM.StructuredViewEnabled = True
oStructuredBOMView = oBOM.BOMViews.Item("Structured")
fileName = CSVpath + ThisDoc.FileName(False) + "-Epicor" + ".xls"
oStructuredBOMView.Export(fileName, kMicrosoftExcelFormat)
Dim refname As String
Dim refBom As BOM
'Dim excelApp As XL.Application
For Each refDoc In oDoc.AllReferencedDocuments
If refDoc.DocumentType = kassemblydocumentobject
refname = Left(refDoc.DisplayName, Len(refDoc.DisplayName) -4)
If InStr(Left(refname, 4), "-") > 0 Then
Continue For
End If
refBom = refDoc.ComponentDefinition.BOM
refBom.StructuredViewFirstLevelOnly = True
refBom.StructuredViewEnabled = True
oStructuredBOMView = refBom.BOMViews.Item("Structured")
fileName = CSVpath + refname + "-Epicor" + ".xls"
oStructuredBOMView.Export(fileName, kMicrosoftExcelFormat)
'GoExcel.Open(fileName)
Dim excelApp As XL.Application = CreateObject("Excel.Application")
Dim oWB As xl.Workbook '= excelApp.Workbooks.Open(fileName)
oWB = excelApp.Workbooks.Open(fileName)
'Dim oWS As xl.Worksheet = oWB.Worksheets("Sheet1")
'excelApp = CreateObject("Excel.Application")
'Dim excelApp = CreateObject("Excel.Application")
'excelWorkbook = excelApp.Workbooks.Open(fileName)
excelApp.Visible = True
excelApp.DisplayAlerts = False
'oWS.Columns(1).Delete
'End With
excelApp.Workbooks.Close
End If
Next
GoTo Golaunch:
'PARTS ONLY BoM
GoPartExport:
oBOM.PartsOnlyViewEnabled = True
Dim oPartsOnlyBOMView As BOMView
oPartsOnlyBOMView = oBOM.BOMViews.Item("Parts Only")
MsgBox(CSVpath + ThisDoc.FileName(False) + ".xls")
fileName = CSVpath + ThisDoc.FileName(False) +"-Parts" + ".xls"
oPartsOnlyBOMView.Export (fileName, kMicrosoftExcelFormat)
GoTo GoLaunch:
'Get user input - do you want to see the BoM?
GoLaunch:
'i = MessageBox.Show("Preview the BOM?", "@ClintBrown3D iLogic", MessageBoxButtons.YesNo)
'If i = vbYes Then : launchviewer = 1 : Else : launchviewer = 0 : End If
launchviewer = 0
If launchviewer = 1 Then ThisDoc.Launch(CSVpath + ThisDoc.FileName(False) + ".xls")
Exit Sub
End Sub
