Parts list export to Excel from IDW with iProperties

[gregory_nickol]

I need some help modifying some iLogic I've got for exporting IDW Parts Lists to Excel. The first change I have an idea what needs to be done but don't  know the proper functions/syntax for it. Right now on lines 10-15, it's set up to run on the first sheet of my IDW, assumes there's a Parts List on the sheet, and exports it to my documents, otherwise it returns an error. Instead I need it to run on the first parts list it finds. If there's a way to search for a parts list that's great, otherwise I was thinking it would try to run on sheet one, and continue if successful, or increment the sheet number if error. This method would need to be limited by the total number of sheets or have some other error catch so it's not just stuck incrementing up erroring out if there's no list on the document at all.

The second thing I'd like to do is in addition to exporting the parts list to an excel document, I also want to send some iProperties along to specific cells. I have no idea how to do that or if it's possible.

'define oDoc
oDoc = ThisDoc.Document

''get the path of the drawing file
'Dim oPath As String = ThisDoc.Path & "\BOM"
'If Not System.IO.Directory.Exists(oPath) Then
'System.IO.Directory.CreateDirectory(oPath)
'End If

'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
oOptions.Value("Template") = "M:\Inventor\Inventor Customization\PartsListExport.xlsx"

'specify the columns to export          
oOptions.Value("ExportedColumns") = "DESCRIPTION;PART NUMBER;QTY;ITEM"

'specify the start cell
oOptions.Value("StartingCell") = "A1"

'specify the XLS tab name
oOptions.Value("TableName") = "Rev. " & iProperties.Value("Project", "Revision Number")

'choose whether or not to include the parts list title row (False = not included)
oOptions.Value("IncludeTitle") = False          

'choose to autofit the column width in the xls file
oOptions.Value("AutoFitColumnWidth") = True
     
''export the Partslist to Excel with above options
'oPartslist.Export(oPath & "\" & iProperties.Value("Project", "Part Number") & " - " & iProperties.Value("Project", "Description") & ".xlsx", PartsListFileFormatEnum.kMicrosoftExcel, oOptions) 


'get BOM target folder path
Dim docFolder As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments
oPath = docFolder & "\BOM"
'Check for the oFolder and create it if it does not exist
If Not System.IO.Directory.Exists(oPath) Then
    System.IO.Directory.CreateDirectory(oPath)
End If

oFullname = oPath & "\" & iProperties.Value("Project", "Part Number") & ".xlsx"
'oFullname = oPath & "\" & iProperties.Value("Project", "Part Number") & " - " & iProperties.Value("Project", "Description") & ".xlsx"
'export the Partslist to Excel with above options
oPartslist.Export(oFullname, PartsListFileFormatEnum.kMicrosoftExcel, oOptions)

[A.Acheson · Accepted solution]

Hi @gregory_nickol 

Instead of targeting sheet 1 you would need to loop the sheets to find the partlist.

So remove

oSheet = oDoc.Sheets(1) ' first sheet

And replace with

For each oSheet in oDoc.Sheets 
'Insert all code that relies in the sheet object.
Next

Next I'm not certain what iproperties you want to insert in with the partslist. Are they belonging to each row of the partlist? If so then this will be a more difficult operation and be independent of the partslist excel export.

Can you share what the iproperties are and where you intend to put them in the excel document?

For this kind of operation to succeed you like will need to loop through your exported partslist in excel and find a string like partnumber/description in order to find that specific row.

The easiest method for all of this is using find row function of go excel. See help page here

i = GoExcel.FindRow("mytable.xlsx", "Sheet1", "Part Number", "=", "12345")
Messagebox.show(i,"iLogic")
'If -1 is returned value is not found

[mgrenier2]

I just did something similar here, it might work or not for your case 🙂

Sub Main()
'[Déclaration des variables
    Dim Projet As String = iProperties.Value("Design Tracking Properties", "Project") 'Projet
	Dim Dessinateur As String = iProperties.Value("Project", "Designer") 'Dessinateur
	Dim Client As String = iProperties.Value("Summary", "Company“) 'Client
	Dim Titre As String = iProperties.Value("Summary", "Title") 'Titre-Description
	Dim F75_1 As String = Parameter("F75_1") 'Fabrication/Expédition
	Dim F75_2 As String = Parameter("F75_2") 'Date requis
	Dim F75_3 As String = Parameter("F75_3") 'Date demande
	Dim filepath As String = ThisDoc.Path 'Emplacement du plan
	Dim excelTemplatePath As String = "C:\Vault WS\Fichiers importants\Formulaires ISO.xlsx" 'Emplacement du gabarit Excel
	Dim excelDestinationPath As String = filepath + "\" + iProperties.Value("Design Tracking Properties", "Project") + "-F75" + ".xlsx" 'Fichier de sortie
	Dim excelSheetName As String = "F75 - Matériel à préparer" 'Feuille du classeur Excel
	Dim oDrawDoc As DrawingDocument
	Dim oSheet As Sheet
    Dim oPartsList As PartsList
    Dim oExcelApp As Object
    Dim oExcelWorkbook As Object
    Dim oExcelWorksheet As Object
    Dim iRow As Integer
	']
'[Vérifications - Gestion d'erreurs
    ' Vérifie si un document de dessin est ouvert
    If ThisApplication.ActiveDocument.DocumentType <> kDrawingDocumentObject Then
        MsgBox("Ouvrez une mise en plan pour exporter la liste de pièces.", vbExclamation)
        Exit Sub
    End If
	' Affiche la procédure à suivre
	MessageBox.Show("S'assurer d'avoir sur la feuille Boulons une liste de pièce avec le Style de liste Formulaire F75", "Avertissement", MessageBoxButtons.OK, MessageBoxIcon.Warning)

    ' Définition des objets de dessin et de la liste de pièces
     oDrawDoc = ThisDoc.Document
	 oSheet = oDrawDoc.Sheets.Item("Boulons") 
     oPartsList = oSheet.PartsLists(1)
']
'[Exportation des données dans Excel
Try
    'Création d'une instance Excel
    oExcelApp = CreateObject("Excel.Application")
    oExcelApp.Visible = True ' Afficher Excel
	'Ouvrir le gabarit Excel
	oExcelWorkbook = oExcelApp.Workbooks.Open(excelTemplatePath)
	'Sélectionner la feuille Excel
	oExcelWorkSheet = oExcelWorkbook.Sheets(excelSheetName)
    'Définit la rangée de départ dans la feuille Excel
	iRow = 12
	'Parcours la liste de pièce et définit leurs emplacement dans la feuille Excel
    For Each oRow In oPartsList.PartsListRows
		Dim oCell As PartsListCell
        oExcelWorksheet.Cells(iRow, 4).Value = oRow.Item(1).Value
        oExcelWorksheet.Cells(iRow, 2).Value = oRow.Item(2).Value
        oExcelWorksheet.Cells(iRow, 1).Value = oRow.Item(10).Value
        iRow = iRow + 1
    Next
	'Exporte des propriétés diverses dans la Feuille Excel et définit leur emplacement    
        If F75_1 = "Pour Fabrication" Then
			oExcelWorksheet.Cells(3, 2).Value = "X"
			ElseIf F75_1 = "Pour Expédition" Then
			oExcelWorksheet.Cells(3, 6).Value = "X"
		End If
oExcelWorksheet.Cells(6, 2).Value = Client
oExcelWorksheet.Cells(8, 2).Value = Projet
oExcelWorksheet.Cells(6, 5).Value = Titre
oExcelWorksheet.Cells(87, 2).Value = Dessinateur
oExcelWorksheet.Cells(85, 2).Value = F75_2
oExcelWorksheet.Cells(87, 5).Value = F75_3
']
'[Sauvegarde du Fichier Excel
' Sauvegarder le classeur Excel
oExcelWorkbook.SaveAs(excelDestinationPath)
']
'[Gestion d'erreurs et fermeture d'Excel
Catch ex As Exception
    ' Gérer les erreurs ici, par exemple :
    MsgBox("Une erreur s'est produite : " & ex.Message)
Finally
    ' Fermer Excel
    If oExcelWorkbook IsNot Nothing Then
        oExcelWorkbook.Close(False)
    End If
    oExcelApp.Quit()
End Try
']
End Sub

[gregory_nickol · Accepted solution]

Changes I made for final code:
I removed this section (lines 10-15 in original post)  where a specific sheet and parts list on that sheet was specified.

'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)

I then combined the For loop with an If and a Try. The For loop goes sheet by sheet and tries to export the first parts list on the sheet. If it fails, it continues to the next sheet. If it succeeds, it increments the variable i, which then blocks the export attempts for subsequent sheets based on the if statement. In some cases we have multiple parts lists on a drawing, but the first is always the one I would want to export, so that's what's being exported.

I also used the GoExcel snippets to send specific drawing level iProperties to fields on my spreadsheet. Turns out that part was really easy. Also added a bit where it checks if the export already exists, and if so deletes it first. If an overwrite is done without this bit, it messes with the template formatting and it's better in my case to just start fresh if possible.

'check for existing XLS file and delete it if found
If Dir(oFullname) <> "" Then
Kill (oFullname)
End If

'Loop through each sheet in the document. Try to export the first parts list on each sheet. If successful, i increments, stopping if stating on next loop.
i = 0
For Each oSheet In oDoc.Sheets
	If i<=0 Then
		Try
			oPartslist = oSheet.PartsLists(1)
			'export the Partslist to Excel with above options
			oPartslist.Export(oFullname, PartsListFileFormatEnum.kMicrosoftExcel, oOptions)
			i = i + 1
		Catch
		End Try	
	End If
Next
If i = 0 Then
	MessageBox.Show("Script could not find Parts List on any sheet. If a Parts List is present, something weird is going on and you should probably just export it manually.", "Error")
End If
Try
	dummyvar=CDbl(iProperties.Value("Custom", "QTY"))
	GoExcel.Open(oFullname, oOptions.Value("TableName"))
	GoExcel.CellValue("H1") = CDbl(iProperties.Value("Custom", "QTY"))
	GoExcel.Save
	GoExcel.Close
Catch
	MessageBox.Show("Quantity iProperty is either blank or not a number, and has not been entered on spreadsheet.", "Error")
End Try