topic Export Parts List to Excel [VBA] in Inventor Programming Forum

Export Parts List to Excel [VBA]

JoãoASilva — Mon, 25 Nov 2019 10:35:20 GMT

Hello!

I stumbled upon a VBA code to export a parts list to excel, using the iLogic Interop.excel reference.

The code is really good, and I adjusted it to my needs, but there is one thing I can't figure out how to change.

I need to export the parts list as it is, but not including the first column.

I should include that the macro uses a company template.

My parts list in the drawing:

My parts list on the drawing.

Outcome of the macro:

Result of the current macro.

What i want from the macro:

What i want from the macro.

This is my code:

AddReference "Microsoft.Office.Interop.Excel"

Dim oExcel As New Microsoft.Office.Interop.Excel.Application

' For debugging
'oExcel.Visible = True

'get PDF target folder path
ooFolder = ThisDoc.PathAndFileName

'get drawing name
oName = ThisDoc.FileName

'Check for the PDF folder and create it if it does not exist
If Not System.IO.Directory.Exists(ooFolder) Then
    System.IO.Directory.CreateDirectory(ooFolder)
End If

Dim oWB As Object
oWB = oExcel.Workbooks.Open("Z:\MecanicaDoc\MEC_PROJ\Requisição Interna XXXxx.xxxx.xlsm")

Dim oWS As Object
oWS = oWB.ActiveSheet

Dim oDoc As DrawingDocument
oDoc = ThisApplication.ActiveDocument

Dim oSheet As Sheet
oSheet = oDoc.ActiveSheet

' Export the first PartsList
Dim oPL As PartsList
oPL = oSheet.PartsLists(1)

' Starting cell position on the Excel sheet
Dim iRowStart As Integer: iRowStart = 9
Dim iColStart As Integer: iColStart = 1

' Export headers
Dim iRow As Integer: iRow = iRowStart
Dim iCol As Integer: iCol = iColStart
Dim oCol As PartsListColumn
For Each oCol In oPL.PartsListColumns
  oWS.Cells(iRow, iCol).Value = oCol.Title
  iCol = iCol + 1
Next
iRow = iRow + 1

' Export content
Dim oRow As PartsListRow
For Each oRow In oPL.PartsListRows
  iCol = iColStart
  Dim oCell As PartsListCell
  For Each oCell In oRow
    oWS.Cells(iRow, iCol).Value = oCell.Value
    iCol = iCol + 1
  Next
  iRow = iRow + 1
Next


'For Each oCell In oPL.PartsListColumns
'	oWS.Cells(iRow, iCol).Value = ""
'	iRow = iRow + 1
'Next

' Save it
' We disable the confirmation dialog
' in case the file already exists and
' needs to be overwritten
oExcel.DisplayAlerts = False
Call oWB.SaveAs(ooFolder & "\" & oName & ".xlsm")

' Close excel
Call oExcel.Quit

Thanks in advance!

Re: Export Parts List to Excel [VBA]

bradeneuropeArthur — Mon, 25 Nov 2019 12:20:17 GMT

This could be an option:

 If OInvdrawdoc.DocumentType = Inventor.DocumentTypeEnum.kDrawingDocumentObject Then
            Dim oSheet As Inventor.Sheet
            oSheet = OInvdrawdoc.Sheets.Item(1)

            Dim strFileNameNoExt As String = System.IO.Path.GetFileNameWithoutExtension(OInvdrawdoc.FullFileName)


            Dim opl As PartsList

            If oDrawSheet.PartsLists.Count = 0 Then
                Exit Sub
            End If

            opl = oDrawSheet.PartsLists.Item(1)

            Dim oOptions As NameValueMap
            oOptions = AppInventor.TransientObjects.CreateNameValueMap 

            oOptions.Value("StartingCell") = "a1"
            oOptions.Value("ExportedColumns") = "QTY;UNID;DESCRIPTION;VENDOR;PART NUMBER;FABRICANTE"
            oOptions.Value("Template") = "Template file location"
            oOptions.Value("ApplyCellFormatting") = True
            oOptions.Value("ForceCellToText") = False

            oXlsExportFilePath = WriteToDirectoryNamePartlist & "\_Xls\" & strFileNameNoExt & " (PART-LIST).xls"

            Dim Informatie As FileInfo
            Informatie = New FileInfo(oXlsExportFilePath)

            If System.IO.File.Exists(oXlsExportFilePath) Then
                Informatie.IsReadOnly = False
                Kill(oXlsExportFilePath)
                opl.Export(oXlsExportFilePath, PartsListFileFormatEnum.kMicrosoftExcel, oOptions)
            Else

                opl.Export(oXlsExportFilePath, PartsListFileFormatEnum.kMicrosoftExcel, oOptions)
            End If
   
End if

        Exit Sub

    End Sub

Re: Export Parts List to Excel [VBA]

JoãoASilva — Mon, 25 Nov 2019 14:21:18 GMT

I had a piece of code similar to that one, but it stopped working, so I had to find another way.

The code I provided works almost perfectly, but to make it work exactly like I want, probably needs a lot of changes.

What did you define "FileInfo" as?

Re: Export Parts List to Excel [VBA]

bradeneuropeArthur — Mon, 25 Nov 2019 14:43:57 GMT

System.IO.fileinfo

Dim Informatie As System.IO.FileInfo
            Informatie = New System.IO.FileInfo(oXlsExportFilePath)

Re: Export Parts List to Excel [VBA]

JoãoASilva — Mon, 25 Nov 2019 16:57:27 GMT

Unfortunately, no sucess!

Same error as before:

"O parâmetro está incorreto" > "The parameter is incorrect"

That's the reason I'm triyng to switch to VBA, to see if I can achieve the same results I had before.

Re: Export Parts List to Excel [VBA]

Anonymous — Tue, 17 Dec 2019 18:24:30 GMT

Your code is working smooth and perfect. I took my template and save as xlsm in temp folder just for testing. I tried with couple parts list and working with all of them same. Without parts list in drawing will be error. Thank you