Import custom properties from excel sheet

kresh.bell · ‎03-11-2025

Hi,

I have a great code to import custom properties from excel sheet, it works great.

Sometimes I need them only in the assembly, not in all parts. Is it possible to edit it to import data only in the assembly?

Sub Main
	
Dim fileDlg As Inventor.FileDialog = Nothing
	InventorVb.Application.CreateFileDialog(fileDlg)

	fileDlg.Filter = "Spredsheet Files (*.xls) (*.xlsx) (*.xlsm) (*.xlsb)|*.xls; *.xlsx; *.xlsm; *.xlsb"

	'Set open location using one of these 2 options below, "1)Hard coded" or "2)Project location":
	'fileDlg.InitialDirectory = "D:\INVENTOR DATA\PROJEKTI\"  'Hard Coded path:
	fileDlg.InitialDirectory = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath

	'Show File Open Dialogue
	fileDlg.DialogTitle = "Browse for XLS File"
	fileDlg.ShowOpen()
	
	Dim xlFile As String  = fileDlg.FileName
	If xlFile = "" Then : Exit Sub: End If

	'Open Excel file.
	GoExcel.Open(xlFile, "Job Sheet")

	Dim doc As Document = ThisDoc.Document
	Dim trans As Transaction = ThisApplication.TransactionManager.StartTransaction(doc, "Custom Properties Import") 'Make this a single transaction
	Dim filename As String  = Nothing
	
	processDocument(filename,xlFile)
	
	If doc.DocumentType = DocumentTypeEnum.kAssemblyDocumentObject Then 
		
		'Traverse all referenced documents.
		For Each refDoc As Document In doc.AllReferencedDocuments 
			
			'Avoid readonly files like CC and Library.
			If refDoc.IsModifiable Then
				filename = IO.Path.GetFileName(refDoc.FullFileName)
				processDocument(filename,xlFile)
			End If
			
		Next
	End If
	
	'Close Excel file.
	GoExcel.Close
	
	trans.End 'End the trasaction
	
	MessageBox.Show("Job Sheet Successfully Imported", "Job Sheet")

End Sub

Sub processDocument(filename As String, xlFile As String)

	'Read from Excel file
	iProperties.Value(filename,"Custom", "Referenced Spreadsheet File Name") = xlFile
	iProperties.Value(filename,"Custom", "AreaCode") = GoExcel.CellValue("H4")
	iProperties.Value(filename,"Custom", "AreaName") = GoExcel.CellValue("C4")
	iProperties.Value(filename,"Custom", "ComponentCode") = GoExcel.CellValue("H7")
	iProperties.Value(filename,"Custom", "ComponentName") = GoExcel.CellValue("C7")
	iProperties.Value(filename,"Custom", "Priority") = GoExcel.CellValue("P7")
	iProperties.Value(filename,"Custom", "D365WoNumber") = GoExcel.CellValue("H8")
	iProperties.Value(filename,"Custom", "DWGName") = GoExcel.CellValue("C6")
	iProperties.Value(filename,"Custom", "DWGRef") = GoExcel.CellValue("H6")
	iProperties.Value(filename,"Custom", "FSCReg") = GoExcel.CellValue("N20")
	iProperties.Value(filename,"Custom", "HealthSafetySheets") = GoExcel.CellValue("H19")
	iProperties.Value(filename,"Custom", "IssuedTo") = GoExcel.CellValue("P6")
	iProperties.Value(filename,"Custom", "ItemCode") = GoExcel.CellValue("H5")
	iProperties.Value(filename,"Custom", "ItemName") = GoExcel.CellValue("C5")
	iProperties.Value(filename,"Custom", "LoadList") = GoExcel.CellValue("C20")
	iProperties.Value(filename,"Custom", "ProjectCode") = GoExcel.CellValue("H3")
	iProperties.Value(filename,"Custom", "Project_Name") = GoExcel.CellValue("C3")
	iProperties.Value(filename,"Custom", "RefDrawing") = GoExcel.CellValue("C6")
	iProperties.Value(filename,"Custom", "TehnicalDataSheets") = GoExcel.CellValue("P19")
	iProperties.Value(filename,"Custom", "LoadList") = GoExcel.CellValue("C20")
	iProperties.Value(filename,"Custom", "UnitQty1") = GoExcel.CellValue("A22")
	iProperties.Value(filename,"Custom", "UnitQty2") = GoExcel.CellValue("A23")
	iProperties.Value(filename,"Custom", "UnitQty3") = GoExcel.CellValue("A24")
	iProperties.Value(filename,"Custom", "UnitName1") = GoExcel.CellValue("B22")
	iProperties.Value(filename,"Custom", "UnitName2") = GoExcel.CellValue("B23")
	iProperties.Value(filename,"Custom", "UnitName3") = GoExcel.CellValue("B24")
	iProperties.Value(filename,"Custom", "UnitEmpty1") = GoExcel.CellValue("C22")
	iProperties.Value(filename,"Custom", "UnitEmpty2") = GoExcel.CellValue("C23")
	iProperties.Value(filename,"Custom", "UnitEmpty3") = GoExcel.CellValue("C24")
	iProperties.Value(filename,"Custom", "UnitDescription1") = GoExcel.CellValue("D22")
	iProperties.Value(filename,"Custom", "UnitDescription2") = GoExcel.CellValue("D23")
	iProperties.Value(filename,"Custom", "UnitDescription3") = GoExcel.CellValue("D24")
	iProperties.Value(filename,"Custom", "Designer") = GoExcel.CellValue("P5")
	iProperties.Value(filename,"Custom", "DateReq") = GoExcel.CellValue("P4")
	iProperties.Value(filename,"Custom", "Client") = GoExcel.CellValue("C9")
	iProperties.Value(filename,"Custom", "Disney01") = GoExcel.CellValue("R3")
	iProperties.Value(filename,"Custom", "Disney02") = GoExcel.CellValue("R4")
	iProperties.Value(filename,"Custom", "Disney03") = GoExcel.CellValue("R5")
	iProperties.Value(filename,"Custom", "Disney04") = GoExcel.CellValue("R6")
	iProperties.Value(filename,"Custom", "Disney05") = GoExcel.CellValue("R7")
	iProperties.Value(filename,"Custom", "Disney06") = GoExcel.CellValue("R8")
	iProperties.Value(filename,"Custom", "Disney07") = GoExcel.CellValue("R9")
End Sub

Ivan_Sinicyn · ‎03-11-2025

Do you want a dialog box at startup asking you to go through all files or just the current assembly?

Sub Main
    ' Display dialog box to choose import scope
    Dim importChoice As Integer
    importChoice = MessageBox.Show("Import data to all assembly components?" & vbCrLf & _
                                   "Yes - all components, No - current assembly only", _
                                   "Select Import Mode", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

    ' Define current document
    Dim doc As Document = ThisDoc.Document
    
    ' Check if document is an assembly
    If doc.DocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
        MessageBox.Show("This script is intended for assemblies only.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Exit Sub
    End If

    ' Create file dialog for selecting Excel file
    Dim fileDlg As Inventor.FileDialog = Nothing
    InventorVb.Application.CreateFileDialog(fileDlg)
    fileDlg.Filter = "Spreadsheet Files (*.xls;*.xlsx;*.xlsm;*.xlsb)|*.xls;*.xlsx;*.xlsm;*.xlsb"
    
    ' Set open location using one of these 2 options: "1) Hard coded" or "2) Project location"
    'fileDlg.InitialDirectory = "D:\INVENTOR DATA\PROJEKTI\"  ' Hard Coded path
    fileDlg.InitialDirectory = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath  ' Project location
    
    fileDlg.DialogTitle = "Select Excel File"
    fileDlg.ShowOpen()

    ' Check if a file was selected
    Dim xlFile As String = fileDlg.FileName
    If String.IsNullOrEmpty(xlFile) Then Exit Sub

    ' Open Excel file
    GoExcel.Open(xlFile, "Job Sheet")

    ' Start transaction for changes
    Dim trans As Transaction = ThisApplication.TransactionManager.StartTransaction(doc, "Custom Properties Import")

    ' Process current document (assembly)
    ProcessDocument(Nothing, xlFile)

    ' Process all referenced documents if "Yes" was selected
    If importChoice = vbYes Then
        For Each refDoc As Document In doc.AllReferencedDocuments
            If refDoc.IsModifiable Then
                Dim filename As String = IO.Path.GetFileName(refDoc.FullFileName)
                ProcessDocument(filename, xlFile)
            End If
        Next
    End If

    ' Close Excel file and end transaction
    GoExcel.Close
    trans.End

    ' Display success message based on import scope
    Dim scopeMsg As String = If(importChoice = vbYes, "all components", "current assembly only")
    MessageBox.Show("Data successfully imported to " & scopeMsg, "Import Complete", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

Sub ProcessDocument(filename As String, xlFile As String)
    ' Define mapping of Excel cells to custom property names
    Dim propertyMap As New Dictionary(Of String, String) From {
        {"Referenced Spreadsheet File Name", xlFile},  ' Special case: uses file path directly
        {"AreaCode", "H4"},
        {"AreaName", "C4"},
        {"ComponentCode", "H7"},
        {"ComponentName", "C7"},
        {"Priority", "P7"},
        {"D365WoNumber", "H8"},
        {"DWGName", "C6"},
        {"DWGRef", "H6"},
        {"FSCReg", "N20"},
        {"HealthSafetySheets", "H19"},
        {"IssuedTo", "P6"},
        {"ItemCode", "H5"},
        {"ItemName", "C5"},
        {"LoadList", "C20"},
        {"ProjectCode", "H3"},
        {"Project_Name", "C3"},
        {"RefDrawing", "C6"},
        {"TehnicalDataSheets", "P19"},
        {"UnitQty1", "A22"},
        {"UnitQty2", "A23"},
        {"UnitQty3", "A24"},
        {"UnitName1", "B22"},
        {"UnitName2", "B23"},
        {"UnitName3", "B24"},
        {"UnitEmpty1", "C22"},
        {"UnitEmpty2", "C23"},
        {"UnitEmpty3", "C24"},
        {"UnitDescription1", "D22"},
        {"UnitDescription2", "D23"},
        {"UnitDescription3", "D24"},
        {"Designer", "P5"},
        {"DateReq", "P4"},
        {"Client", "C9"},
        {"Disney01", "R3"},
        {"Disney02", "R4"},
        {"Disney03", "R5"},
        {"Disney04", "R6"},
        {"Disney05", "R7"},
        {"Disney06", "R8"},
        {"Disney07", "R9"}
    }

    ' Write data from Excel to custom properties using the mapping
    With iProperties
        For Each kvp As KeyValuePair(Of String, String) In propertyMap
            If kvp.Key = "Referenced Spreadsheet File Name" Then
                .Value(filename, "Custom", kvp.Key) = kvp.Value
            Else
                .Value(filename, "Custom", kvp.Key) = GoExcel.CellValue(kvp.Value)
            End If
        Next
    End With
End Sub

INV 2025.3

kresh.bell · ‎03-11-2025

Hi,

no, without dialog box

Ivan_Sinicyn · ‎03-11-2025

Sub Main
    ' Define current document
    Dim doc As Document = ThisDoc.Document
    
    ' Check if document is an assembly
    If doc.DocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
        MessageBox.Show("This script is intended for assemblies only.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Exit Sub
    End If

    ' Create file dialog for selecting Excel file
    Dim fileDlg As Inventor.FileDialog = Nothing
    InventorVb.Application.CreateFileDialog(fileDlg)
    fileDlg.Filter = "Spreadsheet Files (*.xls;*.xlsx;*.xlsm;*.xlsb)|*.xls;*.xlsx;*.xlsm;*.xlsb"
    
    ' Set open location using one of these 2 options: "1) Hard coded" or "2) Project location"
    'fileDlg.InitialDirectory = "D:\INVENTOR DATA\PROJEKTI\"  ' Hard Coded path
    fileDlg.InitialDirectory = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath  ' Project location
    
    fileDlg.DialogTitle = "Select Excel File"
    fileDlg.ShowOpen()

    ' Check if a file was selected
    Dim xlFile As String = fileDlg.FileName
    If String.IsNullOrEmpty(xlFile) Then Exit Sub

    ' Open Excel file
    GoExcel.Open(xlFile, "Job Sheet")

    ' Start transaction for changes
    Dim trans As Transaction = ThisApplication.TransactionManager.StartTransaction(doc, "Custom Properties Import")

    ' Process only the current assembly
    ProcessDocument(Nothing, xlFile)

    ' Close Excel file and end transaction
    GoExcel.Close
    trans.End

    ' Display success message
    MessageBox.Show("Data successfully imported to current assembly", "Import Complete", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

Sub ProcessDocument(filename As String, xlFile As String)
    ' Define mapping of Excel cells to custom property names
    Dim propertyMap As New Dictionary(Of String, String) From {
        {"Referenced Spreadsheet File Name", xlFile},  ' Special case: uses file path directly
        {"AreaCode", "H4"},
        {"AreaName", "C4"},
        {"ComponentCode", "H7"},
        {"ComponentName", "C7"},
        {"Priority", "P7"},
        {"D365WoNumber", "H8"},
        {"DWGName", "C6"},
        {"DWGRef", "H6"},
        {"FSCReg", "N20"},
        {"HealthSafetySheets", "H19"},
        {"IssuedTo", "P6"},
        {"ItemCode", "H5"},
        {"ItemName", "C5"},
        {"LoadList", "C20"},
        {"ProjectCode", "H3"},
        {"Project_Name", "C3"},
        {"RefDrawing", "C6"},
        {"TehnicalDataSheets", "P19"},
        {"UnitQty1", "A22"},
        {"UnitQty2", "A23"},
        {"UnitQty3", "A24"},
        {"UnitName1", "B22"},
        {"UnitName2", "B23"},
        {"UnitName3", "B24"},
        {"UnitEmpty1", "C22"},
        {"UnitEmpty2", "C23"},
        {"UnitEmpty3", "C24"},
        {"UnitDescription1", "D22"},
        {"UnitDescription2", "D23"},
        {"UnitDescription3", "D24"},
        {"Designer", "P5"},
        {"DateReq", "P4"},
        {"Client", "C9"},
        {"Disney01", "R3"},
        {"Disney02", "R4"},
        {"Disney03", "R5"},
        {"Disney04", "R6"},
        {"Disney05", "R7"},
        {"Disney06", "R8"},
        {"Disney07", "R9"}
    }

    ' Write data from Excel to custom properties using the mapping
    With iProperties
        For Each kvp As KeyValuePair(Of String, String) In propertyMap
            If kvp.Key = "Referenced Spreadsheet File Name" Then
                .Value(filename, "Custom", kvp.Key) = kvp.Value
            Else
                .Value(filename, "Custom", kvp.Key) = GoExcel.CellValue(kvp.Value)
            End If
        Next
    End With
End Sub

INV 2025.3

kresh.bell · ‎03-11-2025

@Ivan_Sinicyn , thanks, that's great

kresh.bell · ‎03-11-2025

@Ivan_Sinicyn, I have one problem, it works for assembly but it stopped working in drawing. Is it possible for it to work also in drawing?

Ivan_Sinicyn · ‎03-11-2025

The source code was an indiscriminate traversal of all referenced documents. Now it will be necessary to customize the filter if you want it to be applied to the drawing file. Do I understand correctly that the import should be performed only for the main assembly and its main drawing, which have identical file name?

INV 2025.3

kresh.bell · ‎03-11-2025

Sometimes I need the data from the excel sheet to be applied only to the assembly in which I import it, not to the parts within that assembly. Your new code works great. I also need to use the same iLogic to import that data into the drawing. My first iLogic does that. Unfortunately, your new one does not. These data in the drawing are not connected to the assemblies in it, they are custom properties of that drawing

Ivan_Sinicyn · ‎03-11-2025

@kresh.bell
If you want the code to work just with the active document, it is enough to remove the document type check:

' Check if document is an assembly
    If doc.DocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
        MessageBox.Show("This script is intended for assemblies only.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Exit Sub
    End If

Here is the final version:

Sub Main
    ' Define current document
    Dim doc As Document = ThisDoc.Document
    
    ' Create file dialog for selecting Excel file
    Dim fileDlg As Inventor.FileDialog = Nothing
    InventorVb.Application.CreateFileDialog(fileDlg)
    fileDlg.Filter = "Spreadsheet Files (*.xls;*.xlsx;*.xlsm;*.xlsb)|*.xls;*.xlsx;*.xlsm;*.xlsb"
    
    ' Set open location using one of these 2 options: "1) Hard coded" or "2) Project location"
    'fileDlg.InitialDirectory = "D:\INVENTOR DATA\PROJEKTI\"  ' Hard Coded path
    fileDlg.InitialDirectory = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath  ' Project location
    
    fileDlg.DialogTitle = "Select Excel File"
    fileDlg.ShowOpen()

    ' Check if a file was selected
    Dim xlFile As String = fileDlg.FileName
    If String.IsNullOrEmpty(xlFile) Then Exit Sub

    ' Open Excel file
    GoExcel.Open(xlFile, "Job Sheet")

    ' Start transaction for changes
    Dim trans As Transaction = ThisApplication.TransactionManager.StartTransaction(doc, "Custom Properties Import")

    ' Process only the current assembly
    ProcessDocument(Nothing, xlFile)

    ' Close Excel file and end transaction
    GoExcel.Close
    trans.End

    ' Display success message
    MessageBox.Show("Data successfully imported", "Import Complete", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

Sub ProcessDocument(filename As String, xlFile As String)
    ' Define mapping of Excel cells to custom property names
    Dim propertyMap As New Dictionary(Of String, String) From {
        {"Referenced Spreadsheet File Name", xlFile},  ' Special case: uses file path directly
        {"AreaCode", "H4"},
        {"AreaName", "C4"},
        {"ComponentCode", "H7"},
        {"ComponentName", "C7"},
        {"Priority", "P7"},
        {"D365WoNumber", "H8"},
        {"DWGName", "C6"},
        {"DWGRef", "H6"},
        {"FSCReg", "N20"},
        {"HealthSafetySheets", "H19"},
        {"IssuedTo", "P6"},
        {"ItemCode", "H5"},
        {"ItemName", "C5"},
        {"LoadList", "C20"},
        {"ProjectCode", "H3"},
        {"Project_Name", "C3"},
        {"RefDrawing", "C6"},
        {"TehnicalDataSheets", "P19"},
        {"UnitQty1", "A22"},
        {"UnitQty2", "A23"},
        {"UnitQty3", "A24"},
        {"UnitName1", "B22"},
        {"UnitName2", "B23"},
        {"UnitName3", "B24"},
        {"UnitEmpty1", "C22"},
        {"UnitEmpty2", "C23"},
        {"UnitEmpty3", "C24"},
        {"UnitDescription1", "D22"},
        {"UnitDescription2", "D23"},
        {"UnitDescription3", "D24"},
        {"Designer", "P5"},
        {"DateReq", "P4"},
        {"Client", "C9"},
        {"Disney01", "R3"},
        {"Disney02", "R4"},
        {"Disney03", "R5"},
        {"Disney04", "R6"},
        {"Disney05", "R7"},
        {"Disney06", "R8"},
        {"Disney07", "R9"}
    }

    ' Write data from Excel to custom properties using the mapping
    With iProperties
        For Each kvp As KeyValuePair(Of String, String) In propertyMap
            If kvp.Key = "Referenced Spreadsheet File Name" Then
                .Value(filename, "Custom", kvp.Key) = kvp.Value
            Else
                .Value(filename, "Custom", kvp.Key) = GoExcel.CellValue(kvp.Value)
            End If
        Next
    End With
End Sub

INV 2025.3

kresh.bell · ‎03-11-2025

@Ivan_Sinicyn perfect, thanks!!

Community

Import custom properties from excel sheet

Import custom properties from excel sheet

Import custom properties from excel sheet