- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Dear all,
I would like to make an iLogic rule that creates “empty parts“ within an assembly and fill their iProperties from an excel sheet.
I do not want to create “virtual parts” but “empty parts (.ipt)“.
(I need the .ipt files later in Vault to follow an established process that brings articles (information of electric and hydraulic parts) into an ERP system. That’s why I do not want to use virtual parts)
My main problem is that I am still just a beginner in iLogic/Vba/VB, so I do not know how to fit my snippets together.
I found a rule that creates virtual parts in an assembly based on an excel sheet. It works fine.
(adjusting this rule to my own excel sheet requirements is doable for me).
'[ Browse for the Excel file
oMsg = "Select a Project Information Excel File"
'update the status bar
ThisApplication.StatusBarText = oMsg
Dim oFileDlg As Inventor.FileDialog = Nothing
InventorVb.Application.CreateFileDialog(oFileDlg)
oFileDlg.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx"
oFileDlg.DialogTitle = oMsg
oFileDlg.InitialDirectory = ThisDoc.Path
oFileDlg.CancelError = True
Try
oFileDlg.ShowOpen()
Catch
'catch error when no file is selected
Return 'exit rule
End Try
If Err.Number <> 0 Then
MessageBox.Show("A problem occured when getting the Excel file.", "iLogic",MessageBoxButtons.OK,MessageBoxIcon.Error)
Return 'exit if file not selected
ElseIf oFileDlg.FileName <> "" Then
myXLS = oFileDlg.FileName
'update the status bar with the Excel file name
ThisApplication.StatusBarText = "...reading info from " & myXLS
End If
']
Dim MyArrayList As New ArrayList
MyArrayList = GoExcel.CellValues(myXLS, "Sheet1", "A2", "A200")
'define assembly
Dim asmDoc As AssemblyDocument
asmDoc = ThisApplication.ActiveDocument
'define assembly Component Definition
Dim oAsmCompDef As AssemblyComponentDefinition
oAsmCompDef = ThisApplication.ActiveDocument.ComponentDefinition
Dim occs As ComponentOccurrences
occs = asmDoc.ComponentDefinition.Occurrences
Dim identity As Matrix
identity = ThisApplication.TransientGeometry.CreateMatrix
Dim sVirtPart As String
'get info from the XLS file
For MyRow = 2 To 200 'index row 2 through 200
iQTY = GoExcel.CellValue("A" & MyRow)
oProp1 = GoExcel.CellValue("B" & MyRow)
oProp2 = GoExcel.CellValue("C" & MyRow)
oProp3 = GoExcel.CellValue("D" & MyRow)
oProp4 = GoExcel.CellValue("E" & MyRow)
sVirtPart = oProp1 'defines the virtual part name
'update the status bar with the name
ThisApplication.StatusBarText = sVirtPart
'Iterate through all of the occurrences in the assembly
Dim asmOcc As ComponentOccurrence
For Each asmOcc In oAsmCompDef.Occurrences
'get name of occurence only (sees only everything left of the colon)
Dim oOcc As Object
oOcc = asmOcc.Name.Split(":")(0)
'look at only virtual components
If TypeOf asmOcc.Definition Is VirtualComponentDefinition Then
'compare name selected from list to the
'existing virtual parts
If oOcc = sVirtPart Then
'delete existing virtual parts if name matches
asmOcc.Delete
End If
End If
Next
'create first instance of the virtual part
Dim virtOcc As ComponentOccurrence
If iQTY >= 1 Then
virtOcc = occs.AddVirtual(sVirtPart, identity)
Try
iProperties.Value(sVirtPart & ":1", "Project", "Description") = oProp1
Catch 'catch error when oProp1 = nothing
End Try
Try
iProperties.Value(sVirtPart & ":1", "Project", "Part Number") = oProp2
Catch 'catch error when oProp2 = nothing
End Try
Try
iProperties.Value(sVirtPart & ":1", "Project", "Vendor") = oProp3
Catch 'catch error when oProp4 = nothing
End Try
Try
iProperties.Value(sVirtPart & ":1", "Summary", "Comments") = oProp4
Catch 'catch error when oProp5 = nothing
End Try
End If
'add next instance starting at instance2 (if applicable)
Dim index As Integer
index = 2
Do While index <= iQTY
occs.AddByComponentDefinition(virtOcc.Definition, identity)
index += 1
Loop
Next
'update the status bar
ThisApplication.StatusBarText = "Virtual components added!"
This rule comes from here:
(from post #12 in that thread)
But as I said I cannot use virtual parts but I need real (empty) parts.
So I found another rule that creates one empty part (.ipt) in an existing assembly.
So I wonder if it is possible to replace parts in the first rule by the second rule in order to create real parts but not virtual parts.
And at which point a save command for new created parts (.ipt) should be added in the merged rule.
Does anybody have an advice for me?
This is the second rule which creates one empty part (.ipt) in an existing assembly:
Dim oAssDoc As AssemblyDocument
oAssDoc = ThisApplication.ActiveDocument
Dim oAssDef As AssemblyComponentDefinition
oAssDef = oAssDoc.ComponentDefinition
Dim oNewPart As PartDocument
oNewPart = ThisApplication.Documents.Add(kPartDocumentObject, , False)
Dim oNewPartDef As PartComponentDefinition
oNewPartDef = oNewPart.ComponentDefinition
'do what you need to create in this part
'e.g. create a block
'place without any transformation
'can adjust the matrix with your requirement
Dim oMartrix As Matrix
oMatrix = ThisApplication.TransientGeometry.CreateMatrix()
'add the new part to the assembly
Dim oNewOcc As ComponentOccurrence
oNewOcc = oAssDef.Occurrences.AddByComponentDefinition(oNewPartDef, oMatrix)
That rule comes from here:
https://forums.autodesk.com/t5/inventor-customization/creation-of-part-using-ilogic/td-p/6334536
Does anybody have an advice for me?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
I think I may have a solution for you. I included several comments within the code to help guide you through it. If you have any questions before or after running it, just let me know.
Beware: Near the end of the Sub Main...End Sub block, I am attempting to save each new Part file (one per row in Excel) to the same directory as the assembly. If you don't want to save the Parts out yet, just delete that whole block of code (there are multiple comments above that line of code), or change it however you want (perhaps specify a different directory if you want).
Also, you will notice a couple of special lines at the top of this code. Once you paste all this code into a Rule, those top two lines should automatically pop into to the Header portion of the rule editor's dialog. They are needed so Inventor's iLogic environment can recognize all the objects defined within the Excel application.
Here's the code:
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel
Sub Main
'Specify the Excel file's (Workbook) full path & file name (with extension)
'It is calling a Windows OpenFileDialog to allow the user to do this
Dim oFileName As String = GetExcelFile()
'Specify the Sheet name within the workbook
'perhaps this name could be selected from a list of all available sheets
'in the Excel Workbook once opened, of you don't want to manually specify it here?
Dim oSheetName As String = "Sheet1"
'Start a new instance of the Excel application
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
oExcelApp = New Microsoft.Office.Interop.Excel.ApplicationClass
oExcelApp.DisplayAlerts = False
oExcelApp.Visible = False
'[ Attempt to open the specified Workbook (file) using the supplied file name
Dim oWB As Workbook
Try
oWB = oExcelApp.Workbooks.Open(oFileName)
Catch oEx As Exception
MsgBox("The attempt to open the Excel file named '" & oFileName & "' failed." & vbCrLf & _
"The error message for this failure is as follows:" & vbCrLf & _
oEx.Message & vbCrLf & vbCrLf & _
"Its 'StackTrace is as follows:" & vbCrLf & _
oEx.StackTrace & vbCrLf & vbCrLf & _
"Its source is as follows:" & vbCrLf & _
oEx.Source, vbOKOnly + vbCritical, "Couldn't Open File")
Exit Sub
End Try
'Attempt to get the Worksheet
Dim oWS As Worksheet
Try
oWS = oWB.Sheets.Item(oSheetName)
Catch
oWS = oWB.ActiveSheet
Catch
oWS = oWB.Worksheets.Item(1)
Catch
MsgBox("No Worksheet was found in the specified Excel file. Exiting.", vbOKOnly + vbCritical, " ")
Exit Sub
End Try
Dim oCells As Range = oWS.Cells
'Define the active Assembly and all related variables
If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
MsgBox("This rule '" & iLogicVb.RuleName & "' only works for Assembly Documents.",vbOKOnly, "WRONG DOCUMENT TYPE")
Exit Sub
End If
Dim oADoc As AssemblyDocument = ThisAssembly.Document
Dim oDir As String = System.IO.Path.GetDirectoryName(oADoc.FullFileName)
Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
Dim oOccs As ComponentOccurrences = oADef.Occurrences
Dim oOcc As ComponentOccurrence
Dim oPDoc As PartDocument
Dim oDProps As Inventor.PropertySet 'Design Tracking Properties (Project)
Dim oSProps As Inventor.PropertySet 'Inventor Summary Information (Summary)
Dim oMatrix As Inventor.Matrix = ThisApplication.TransientGeometry.CreateMatrix
'Find the last row and last column being used to limit our loop ranges
Dim oLastRow As Integer = oWS.UsedRange.Rows.Count
'Dim oLastCol As Integer = oWS.UsedRange.Columns.Count
'Dim oColumnHeadersRow As Integer = 1
Dim o1stDataRow As Integer = 2
Dim oRow, oQty As Integer
Dim oPName, oPNum, oVendor, oNotes As String
For oRow = o1stDataRow To oLastRow
'First number is Row index, second number is Column Index
oQty = oCells.Item(oRow, 1).Value
oPName = oCells.Item(oRow, 2).Value
oPNum = oCells.Item(oRow, 3).Value
oVendor = oCells.Item(oRow, 4).Value
oNotes = oCells.Item(oRow, 5).Value
'Create the new Part
oPDoc = ThisApplication.Documents.Add(DocumentTypeEnum.kPartDocumentObject, , False)
'Set the iProperties within the new Part
oDProps = oPDoc.PropertySets.Item("Design Tracking Properties")
oSProps = oPDoc.PropertySets.Item("Inventor Summary Information")
oDProps.Item("Description").Value = oPName
oDProps.Item("Part Number").Value = oPNum
oDProps.Item("Vendor").Value = oVendor
oSProps.Item("Comments").Value = oNotes
'<<<< !!! CHECK IF THIS IS OK BEFORE RUNNING !!! >>>>
'Saving each new Part to the same directory as the Assembly
'if this is not OK, you could specify a different directory
'or you could use a SaveFileDialog for this
'or just skip saving the parts at this stage altogether
oPDoc.SaveAs(oDir & "\" & oPName & ".ipt", False)
'Add that many of this part to the assembly
For i = 1 To oQty
oOccs.AddByComponentDefinition(oPDoc.ComponentDefinition, oMatrix)
Next
Next
'Close the Workbook (file)
oWB.Close
'Close this instance of the Excel application
oExcelApp.Quit
End Sub
Function GetExcelFile() As String
Dim oFileName As String
Dim oOpenDlg As New System.Windows.Forms.OpenFileDialog
oOpenDlg.Title = "Browse To And Select Your Excel File."
oOpenDlg.InitialDirectory = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath
oOpenDlg.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx"
oOpenDlg.Multiselect = False
oOpenDlg.RestoreDirectory = False
Dim oResult = oOpenDlg.ShowDialog
If oResult = vbOK Then
If oOpenDlg.FileName <> vbNullString Then
oFileName = oOpenDlg.FileName
Else
MsgBox("No file was selected. Exiting.", vbOKOnly + vbExclamation, "FILE NOT SELECTED")
End If
ElseIf oResult = vbCancel Then
MsgBox("The dialog was Canceled. Exiting.", vbOKOnly + vbInformation, "CANCELED")
End If
GetExcelFile = oFileName
End Function
If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click 'LIKE'
.
If you have time, please... Vote For My IDEAS
and Explore My CONTRIBUTIONS
Inventor 2021 Help | Inventor Forum | Inventor Customization Forum | Inventor Ideas Forum
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Thank you so much, WCrihfield !
I am quite sure your code is the solution.
But unfortunately the code is not working for me, yet
There comes an error right in the beginning after selecting the excel file.
I think it might be because I have a German Excel version.
In my test.xlsx file I renamed the sheet from “Tabelle1” to “Sheet1”, so I had no problem because of language version in the rule that creates virtual parts.
But maybe this rule uses more excel…
I will try the code on a computer with english version of excel hopefully soon.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Sorry for false alarm.
there is no error.
I just had an empty excel cell in my test file. that empty cell caused the problems.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hello all,
This might be interesting for people who are using a Vault and want to use the above rule with numbering scheme from Vault:
I added some code to the rule, so the automatically created files are saved with filenames according to a vault numbering scheme.
(The rule above uses filenames from the excel sheet.)
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel
AddReference "Autodesk.Connectivity.WebServices"
AddReference "Autodesk.DataManagement.Client.Framework.Forms"
AddReference "Autodesk.DataManagement.Client.Framework.Vault"
AddReference "Autodesk.DataManagement.Client.Framework.Vault.Forms"
AddReference "Connectivity.InventorAddin.EdmAddin"
Imports ACW = Autodesk.Connectivity.WebServices
Imports VDF = Autodesk.DataManagement.Client.Framework
Imports Autodesk.DataManagement.Client.Framework.Vault.Services
Imports Autodesk.DataManagement.Client.Framework.Vault.Currency.Connections
Imports edm = Connectivity.InventorAddin.EdmAddinSub Main
'Specify the Excel file's (Workbook) full path & file name (with extension)
'It is calling a Windows OpenFileDialog to allow the user to do this
Dim oFileName As String = GetExcelFile()
'Specify the Sheet name within the workbook
'perhaps this name could be selected from a list of all available sheets
'in the Excel Workbook once opened, of you don't want to manually specify it here?
Dim oSheetName As String = "Sheet1"
'Start a new instance of the Excel application
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
oExcelApp = New Microsoft.Office.Interop.Excel.ApplicationClass
oExcelApp.DisplayAlerts = False
oExcelApp.Visible = False
'[ Attempt to open the specified Workbook (file) using the supplied file name
Dim oWB As Workbook
Try
oWB = oExcelApp.Workbooks.Open(oFileName)
Catch oEx As Exception
MsgBox("The attempt to open the Excel file named '" & oFileName & "' failed." & vbCrLf & _
"The error message for this failure is as follows:" & vbCrLf & _
oEx.Message & vbCrLf & vbCrLf & _
"Its 'StackTrace is as follows:" & vbCrLf & _
oEx.StackTrace & vbCrLf & vbCrLf & _
"Its source is as follows:" & vbCrLf & _
oEx.Source, vbOKOnly + vbCritical, "Couldn't Open File")
Exit Sub
End Try
'Attempt to get the Worksheet
Dim oWS As Worksheet
Try
oWS = oWB.Sheets.Item(oSheetName)
Catch
oWS = oWB.ActiveSheet
Catch
oWS = oWB.Worksheets.Item(1)
Catch
MsgBox("No Worksheet was found in the specified Excel file. Exiting.", vbOKOnly + vbCritical, " ")
Exit Sub
End Try
Dim oCells As Range = oWS.Cells
'Define the active Assembly and all related variables
If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
MsgBox("This rule '" & iLogicVb.RuleName & "' only works for Assembly Documents.",vbOKOnly, "WRONG DOCUMENT TYPE")
Exit Sub
End If
Dim oADoc As AssemblyDocument = ThisAssembly.Document
Dim oDir As String = System.IO.Path.GetDirectoryName(oADoc.FullFileName)
Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
Dim oOccs As ComponentOccurrences = oADef.Occurrences
Dim oOcc As ComponentOccurrence
Dim oPDoc As PartDocument
Dim oDProps As Inventor.PropertySet 'Design Tracking Properties (Project)
Dim oSProps As Inventor.PropertySet 'Inventor Summary Information (Summary)
Dim oMatrix As Inventor.Matrix = ThisApplication.TransientGeometry.CreateMatrix
'Find the last row and last column being used to limit our loop ranges
Dim oLastRow As Integer = oWS.UsedRange.Rows.Count
'Dim oLastCol As Integer = oWS.UsedRange.Columns.Count
'Dim oColumnHeadersRow As Integer = 1
Dim o1stDataRow As Integer = 2
Dim oRow, oQty As Integer
Dim oPName, oPNum, oVendor, oNotes As String
Dim oPFilename As String
For oRow = o1stDataRow To oLastRow
'First number is Row index, second number is Column Index
oQty = oCells.Item(oRow, 1).Value
oPName = oCells.Item(oRow, 2).Value
oPNum = oCells.Item(oRow, 3).Value
oVendor = oCells.Item(oRow, 4).Value
oNotes = oCells.Item(oRow, 5).Value
'Create the new Part
oPDoc = ThisApplication.Documents.Add(DocumentTypeEnum.kPartDocumentObject, , False)
'Set the iProperties within the new Part
oDProps = oPDoc.PropertySets.Item("Design Tracking Properties")
oSProps = oPDoc.PropertySets.Item("Inventor Summary Information")
oDProps.Item("Description").Value = oPName
oDProps.Item("Part Number").Value = oPNum
oDProps.Item("Vendor").Value = oVendor
oSProps.Item("Comments").Value = oNotes
'<<<< !!! CHECK IF THIS IS OK BEFORE RUNNING !!! >>>>
'Saving each new Part to the same directory as the Assembly
'if this is not OK, you could specify a different directory
'or you could use a SaveFileDialog for this
'or just skip saving the parts at this stage altogether
oPFilename = getFilenamesFromVaultNamingScheme("CT", "", 1)
'note: CT is the name of my numbering scheme in vault has to be adjusted to personal needs!!
'oPDoc.SaveAs(oDir & "\" & oPName & ".ipt", False)
oPDoc.SaveAs(oDir & "\" & oPFilename & ".ipt", False)
'Add that many of this part to the assembly
For i = 1 To oQty
oOccs.AddByComponentDefinition(oPDoc.ComponentDefinition, oMatrix)
Next
Next
'Close the Workbook (file)
oWB.Close
'Close this instance of the Excel application
oExcelApp.Quit
End Sub
Function GetExcelFile() As String
Dim oFileName As String
Dim oOpenDlg As New System.Windows.Forms.OpenFileDialog
oOpenDlg.Title = "Browse To And Select Your Excel File."
oOpenDlg.InitialDirectory = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath
oOpenDlg.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx"
oOpenDlg.Multiselect = False
oOpenDlg.RestoreDirectory = False
Dim oResult = oOpenDlg.ShowDialog
If oResult = vbOK Then
If oOpenDlg.FileName <> vbNullString Then
oFileName = oOpenDlg.FileName
Else
MsgBox("No file was selected. Exiting.", vbOKOnly + vbExclamation, "FILE NOT SELECTED")
End If
ElseIf oResult = vbCancel Then
MsgBox("The dialog was Canceled. Exiting.", vbOKOnly + vbInformation, "CANCELED")
End If
GetExcelFile = oFileName
End Function
Function getFilenamesFromVaultNamingScheme(RequiredSchemeName As String, RequiredSchemeString As String, numberOfNames As Integer)
Dim oPFilename As String
Dim Connection As VDF.Vault.Currency.Connections.Connection = edm.EdmSecurity.Instance.VaultConnection()
Dim genNum As String = String.Empty
If Not Connection Is Nothing Then
Dim entityClassId = VDF.Vault.Currency.Entities.EntityClassIds.Files
Dim numSchemes As ACW.NumSchm() = Connection.WebServiceManager.NumberingService.GetNumberingSchemes(entityClassId, Nothing) 'kanske inte nothing
Dim requiredScheme As ACW.NumSchm = (From sch As ACW.NumSchm In numSchemes
Where sch.Name = RequiredSchemeName
Select sch).FirstOrDefault()
Dim numGenArgs() As String = {RequiredSchemeString }
oPFilename = Connection.WebServiceManager.DocumentService.GenerateFileNumber(requiredScheme.SchmID, numGenArgs)
'MessageBox.Show("Numbers generated: " & oPFilename & " to ", "Success!", _
'MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Vault didn't work", "Fail!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
getFilenamesFromVaultNamingScheme = oPFilename
End Function
I got the code for the vault numbering scheme from here:
so thanks to JhoelForshav
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi I have this working on my pc but when I get one of the other people in the office to run it, it troughs up and error and I can't work out why. Can you please point me in the right direction?
at System.StubHelpers.StubHelpers.GetCOMIPFromRCW(Object objSrc, IntPtr pCPCMD, IntPtr& ppTarget, Boolean& pfNeedsRelease)
at Microsoft.Office.Interop.Excel.ApplicationClass.set_DisplayAlerts(Boolean RHS)
at ThisRule.Main()
at Autodesk.iLogic.Exec.AppDomExec.ExecRuleInAssembly(Assembly assem)
at iLogic.RuleEvalContainer.ExecRuleEval(String execRule)
'Start a new instance of the Excel application
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
oExcelApp = New Microsoft.Office.Interop.Excel.ApplicationClass
'Fails here
oExcelApp.DisplayAlerts = False
oExcelApp.Visible = False
I also modified it to show a message box that explains how the excel doc needs to be set out, use a specific template, use part number as file name and ground the parts.
'Source "https://forums.autodesk.com/t5/inventor-customization/ilogic-create-empty-parts-and-fill-their-iproperties-from-excel/td-p/9850027"
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel
Sub Main
MessageBox.Show("Excel sheet needs to have the following columns in row 1 in order to populate the properites" & vbCrLf & _
"'Qty' (by ea units only not length), 'Description','Part Number', 'Vendor', 'Stock Number'" & vbCrLf & vbCrLf & _
"Assembly needs to be saved", "Rule Requirements")
'Specify the Excel file's (Workbook) full path & file name (with extension)
'It is calling a Windows OpenFileDialog to allow the user to do this
Dim oFileName As String = GetExcelFile()
'Specify the Sheet name within the workbook
'perhaps this name could be selected from a list of all available sheets
'in the Excel Workbook once opened, of you don't want to manually specify it here?
Dim oSheetName As String = "Sheet1"
'Start a new instance of the Excel application
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
oExcelApp = New Microsoft.Office.Interop.Excel.ApplicationClass
oExcelApp.DisplayAlerts = False
oExcelApp.Visible = False
'[ Attempt to open the specified Workbook (file) using the supplied file name
Dim oWB As Workbook
Try
oWB = oExcelApp.Workbooks.Open(oFileName)
Catch oEx As Exception
MsgBox("The attempt to open the Excel file named '" & oFileName & "' failed." & vbCrLf & _
"The error message for this failure is as follows:" & vbCrLf & _
oEx.Message & vbCrLf & vbCrLf & _
"Its 'StackTrace is as follows:" & vbCrLf & _
oEx.StackTrace & vbCrLf & vbCrLf & _
"Its source is as follows:" & vbCrLf & _
oEx.Source, vbOKOnly + vbCritical, "Couldn't Open File")
Exit Sub
End Try
'Attempt to get the Worksheet
Dim oWS As Worksheet
Try
oWS = oWB.Sheets.Item(oSheetName)
Catch
oWS = oWB.ActiveSheet
Catch
oWS = oWB.Worksheets.Item(1)
Catch
MsgBox("'Sheet 1' Worksheet was not found in the specified Excel file. Exiting.", vbOKOnly + vbCritical, " ")
Exit Sub
End Try
Dim oCells As Range = oWS.Cells
'Define the active Assembly and all related variables
If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
MsgBox("This rule '" & iLogicVb.RuleName & "' only works for Assembly Documents.",vbOKOnly, "WRONG DOCUMENT TYPE")
Exit Sub
End If
Dim oADoc As AssemblyDocument = ThisAssembly.Document
Dim oDir As String = System.IO.Path.GetDirectoryName(oADoc.FullFileName)
Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
Dim oOccs As ComponentOccurrences = oADef.Occurrences
Dim oOcc As ComponentOccurrence
Dim oPDoc As PartDocument
Dim oDProps As Inventor.PropertySet 'Design Tracking Properties (Project)
Dim oSProps As Inventor.PropertySet 'Inventor Summary Information (Summary)
Dim oMatrix As Inventor.Matrix = ThisApplication.TransientGeometry.CreateMatrix
'Find the last row and last column being used to limit our loop ranges
Dim oLastRow As Integer = oWS.UsedRange.Rows.Count
'Dim oLastCol As Integer = oWS.UsedRange.Columns.Count
'Dim oColumnHeadersRow As Integer = 1
Dim o1stDataRow As Integer = 2
Dim oRow, oQty As Integer
Dim oPName, oPNum, oVendor, oStock As String
For oRow = o1stDataRow To oLastRow
'First number is Row index, second number is Column Index
oQty = oCells.Item(oRow, 1).Value
oPName = oCells.Item(oRow, 2).Value
oPNum = oCells.Item(oRow, 3).Value
oVendor = oCells.Item(oRow, 4).Value
oStock = oCells.Item(oRow, 5).Value
'Define part template
oTemplate=ThisApplication.DesignProjectManager.ActiveDesignProject.TemplatesPath & "\Emtpy part for BOM.ipt"
'Create the new Part
oPDoc = ThisApplication.Documents.Add(DocumentTypeEnum.kPartDocumentObject,oTemplate , False)
'Set the iProperties within the new Part
oDProps = oPDoc.PropertySets.Item("Design Tracking Properties")
oSProps = oPDoc.PropertySets.Item("Inventor Summary Information")
oDProps.Item("Description").Value = oPName
oDProps.Item("Part Number").Value = oPNum
oDProps.Item("Vendor").Value = oVendor
oDProps.Item("Stock Number").Value = oStock
'<<<< !!! CHECK IF THIS IS OK BEFORE RUNNING !!! >>>>
'Saving each new Part to the same directory as the Assembly
'if this is not OK, you could specify a different directory
'or you could use a SaveFileDialog for this
'or just skip saving the parts at this stage altogether
oPDoc.SaveAs(oDir & "\" & oPNum & ".ipt", False)
'Add that many of this part to the assembly
For i = 1 To oQty
' oOccs.AddByComponentDefinition(oPDoc.ComponentDefinition, oMatrix)
oOcc = oOccs.AddByComponentDefinition(oPDoc.ComponentDefinition, oMatrix)
oOcc.Grounded = True
Next
Next
'Close the Workbook (file)
oWB.Close
'Close this instance of the Excel application
oExcelApp.Quit
End Sub
Function GetExcelFile() As String
Dim oFileName As String
Dim oOpenDlg As New System.Windows.Forms.OpenFileDialog
oOpenDlg.Title = "Browse To And Select Your Excel File."
oOpenDlg.InitialDirectory = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath
oOpenDlg.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx"
oOpenDlg.Multiselect = False
oOpenDlg.RestoreDirectory = False
Dim oResult = oOpenDlg.ShowDialog
If oResult = vbOK Then
If oOpenDlg.FileName <> vbNullString Then
oFileName = oOpenDlg.FileName
Else
MsgBox("No file was selected. Exiting.", vbOKOnly + vbExclamation, "FILE NOT SELECTED")
End If
ElseIf oResult = vbCancel Then
MsgBox("The dialog was Canceled. Exiting.", vbOKOnly + vbInformation, "CANCELED")
End If
GetExcelFile = oFileName
End Function
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi Terry,
I am sorry, but I cannot help you.
I assume you are trying to read from the same excel file on your own computer and on your colleagues, so there will be no issue in that.
Are your computers same?
If you google for your error message it says it is an MS office/excel Problem and you might try to reinstall/repair MS office/excel.
sounds strange, but maybe you can give it a try.
to see that sometimes error will be solved by reinstall/repair MS office/excel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
just keep in mind to not have any empty cells in your excel file.
that illogic code doesn’t like empty cell
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi @terry.nicholls .
There are several ways to start up the Excel application, open Excel documents, and work with Excel in general. In the case of that rule, it seems I chose the following lines to start the Excel application and assign it to a variable:
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
oExcelApp = New Microsoft.Office.Interop.Excel.ApplicationClass
The simplest change I might suggest is to change 'ApplicationClass' to 'Application' at the end of that second line like this:
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
oExcelApp = New Microsoft.Office.Interop.Excel.Application
Those ways will always attempt to create a new instance of Excel, whether it is currently running or not.
Or, there are other options too. One option would be to simply use the GetObject() &/or CreateObject() methods like this:
Dim oExcel As Microsoft.Office.Interop.Excel.Application
Try
'try to find an already running instance of the Excel Application
oExcel = GetObject(, "Excel.Application")
MsgBox("Found an instance of the Excel Application already open.",,"")
Catch
'it wasn't found open, so create an instance of it (start the application)
oExcel = CreateObject("Excel.Application") 'Option 1
MsgBox("Created a new instance of the Excel Application.", , "")
Catch
MsgBox("Failed to Get/Create an instance of the Excel Application. Exiting.", , "")
Exit Sub
End Try
And if you are just doing something simple from an iLogic rule, you could just use the built-in 'GoExcel' tools, shown within the iLogic Rule Editor's Snippets > System tab > Excel Data Links group.
As far as the empty cell issue, that can be annoying, but can be dealt with. You can either attempt to check the cell's value, without specifying expected data type, or you can use Cast (direct data type conversion) methods to ensure that the returned value is being understood as the data type you are expecting. Cell values can be many types of data, therefore the 'Value' is defined as Variant (or Object) instead of a specific data type. Therefore it can be a little tricky when attempting to set a cell's value directly to a variable of a certain type. If you are expecting the value to be a String type data, you can have a String type variable, then when attempting to set its value from the oCells.Item().Value you can use the oPName = CStr(oCells.Item().Value), which will attempt to directly convert the cell's value to a String type before setting it as the value of the variable. Another convenient tool to check for an empty cell is something like this:
If String.IsNullOrEmpty(oCells.Item().Value) Then
'or
If Not String.IsNullOrEmpty(oCells.Item().Value) Then
If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS)
.
If you want and have time, I would appreciate your Vote(s) for My IDEAS
or you can Explore My CONTRIBUTIONS
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi,
I need urgently the code for Inventor 2021 - I get here constantly the error message " Error Invalid Path" - can someone help me?
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel
Sub Main
'Specify the Excel file's (Workbook) full path & file name (with extension)
'It is calling a Windows OpenFileDialog to allow the user to do this
Dim oFileName As String = GetExcelFile()
'Specify the Sheet name within the workbook
'perhaps this name could be selected from a list of all available sheets
'in the Excel Workbook once opened, of you don't want to manually specify it here?
Dim oSheetName As String = "Sheet1"
'Start a new instance of the Excel application
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
oExcelApp = New Microsoft.Office.Interop.Excel.ApplicationClass
oExcelApp.DisplayAlerts = False
oExcelApp.Visible = False
'[ Attempt to open the specified Workbook (file) using the supplied file name
Dim oWB As Workbook
Try
oWB = oExcelApp.Workbooks.Open(oFileName)
Catch oEx As Exception
MsgBox("The attempt to open the Excel file named '" & oFileName & "' failed." & vbCrLf & _
"The error message for this failure is as follows:" & vbCrLf & _
oEx.Message & vbCrLf & vbCrLf & _
"Its 'StackTrace is as follows:" & vbCrLf & _
oEx.StackTrace & vbCrLf & vbCrLf & _
"Its source is as follows:" & vbCrLf & _
oEx.Source, vbOKOnly + vbCritical, "Couldn't Open File")
Exit Sub
End Try
'Attempt to get the Worksheet
Dim oWS As Worksheet
Try
oWS = oWB.Sheets.Item(oSheetName)
Catch
oWS = oWB.ActiveSheet
Catch
oWS = oWB.Worksheets.Item(1)
Catch
MsgBox("No Worksheet was found in the specified Excel file. Exiting.", vbOKOnly + vbCritical, " ")
Exit Sub
End Try
Dim oCells As Range = oWS.Cells
'Define the active Assembly and all related variables
If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
MsgBox("This rule '" & iLogicVb.RuleName & "' only works for Assembly Documents.",vbOKOnly, "WRONG DOCUMENT TYPE")
Exit Sub
End If
Dim oADoc As AssemblyDocument = ThisAssembly.Document
Dim oDir As String = System.IO.Path.GetDirectoryName(oADoc.FullFileName)
Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
Dim oOccs As ComponentOccurrences = oADef.Occurrences
Dim oOcc As ComponentOccurrence
Dim oPDoc As PartDocument
Dim oDProps As Inventor.PropertySet 'Design Tracking Properties (Project)
Dim oSProps As Inventor.PropertySet 'Inventor Summary Information (Summary)
Dim oMatrix As Inventor.Matrix = ThisApplication.TransientGeometry.CreateMatrix
'Find the last row and last column being used to limit our loop ranges
Dim oLastRow As Integer = oWS.UsedRange.Rows.Count
'Dim oLastCol As Integer = oWS.UsedRange.Columns.Count
'Dim oColumnHeadersRow As Integer = 1
Dim o1stDataRow As Integer = 2
Dim oRow, oQty As Integer
Dim oPName, oPNum, oVendor, oNotes As String
For oRow = o1stDataRow To oLastRow
'First number is Row index, second number is Column Index
oQty = oCells.Item(oRow, 1).Value
oPName = oCells.Item(oRow, 2).Value
oPNum = oCells.Item(oRow, 3).Value
oVendor = oCells.Item(oRow, 4).Value
oNotes = oCells.Item(oRow, 5).Value
'Create the new Part
oPDoc = ThisApplication.Documents.Add(DocumentTypeEnum.kPartDocumentObject, , False)
'Set the iProperties within the new Part
oDProps = oPDoc.PropertySets.Item("Design Tracking Properties")
oSProps = oPDoc.PropertySets.Item("Inventor Summary Information")
oDProps.Item("Description").Value = oPName
oDProps.Item("Part Number").Value = oPNum
oDProps.Item("Vendor").Value = oVendor
oSProps.Item("Comments").Value = oNotes
'<<<< !!! CHECK IF THIS IS OK BEFORE RUNNING !!! >>>>
'Saving each new Part to the same directory as the Assembly
'if this is not OK, you could specify a different directory
'or you could use a SaveFileDialog for this
'or just skip saving the parts at this stage altogether
oPDoc.SaveAs(oDir & "\" & oPName & ".ipt", False)
'Add that many of this part to the assembly
For i = 1 To oQty
oOccs.AddByComponentDefinition(oPDoc.ComponentDefinition, oMatrix)
Next
Next
'Close the Workbook (file)
oWB.Close
'Close this instance of the Excel application
oExcelApp.Quit
End Sub
Function GetExcelFile() As String
Dim oFileName As String
Dim oOpenDlg As New System.Windows.Forms.OpenFileDialog
oOpenDlg.Title = "Browse To And Select Your Excel File."
oOpenDlg.InitialDirectory = ThisApplication.DesignProjectManager.ActiveDesignProject.WorkspacePath
oOpenDlg.Filter = "Excel Files (*.xls;*.xlsx)|*.xls;*.xlsx"
oOpenDlg.Multiselect = False
oOpenDlg.RestoreDirectory = False
Dim oResult = oOpenDlg.ShowDialog
If oResult = vbOK Then
If oOpenDlg.FileName <> vbNullString Then
oFileName = oOpenDlg.FileName
Else
MsgBox("No file was selected. Exiting.", vbOKOnly + vbExclamation, "FILE NOT SELECTED")
End If
ElseIf oResult = vbCancel Then
MsgBox("The dialog was Canceled. Exiting.", vbOKOnly + vbInformation, "CANCELED")
End If
GetExcelFile = oFileName
End Function
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi @max.baumann07. Does the error message indicate which line within the code that is encountering the error. I only see two places where a 'path' is being specified. At the beginning, where you browse for the excel file, then try to open that Excel file, then later where you are specifying the SaveAs path, using oDir & oPName variables. If the assembly had not been saved yet, then you will not get the needed path data when setting the value of the oDir variable. If that cell you are specifying within the Excel sheet is empty, or maybe if the cell is formatted as something other than text, you may be getting bad data from that cell to set as the value of oPName variable. Maybe try using a Logger.Info() or MsgBox, or MessageBox.Show() to show/record what full file name you end up with before trying the SaveAs line.
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
First, thank you for your quick reply.
I had indeed not formatted the cells as text, nor was the excel in the same working directory. However, I have now further a problem and come unfortunately not at all further.
Test Excel:
Error:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Besides what I mentioned above, another thing that might be causing problems is if a file with that same name already exists in that same folder. I don't know if your system will just let you overwrite it, without any prompt or not, especially if that file may be open or ReadOnly for some reason. Do you have full Read/Write permission in that directory? Try adding some lines of code like the following before your SaveAs line of code.
Dim sFFN As String = oDir & "\" & oPName & ".ipt"
Logger.Info("SaveAs FullFileName = " & sFFN )
If System.IO.File.Exists(sFFN) Then 'show a message to the user, or skip this one, or do something different.
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
GREAT. Thank you!
This works.
However, I still have a small question. When I want als to create Custom iPropertys. What am I doing wrong?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi @max.baumann07. That 'iProperties.Value()' iLogic shortcut snippet does not know which specific document to target. There are ways to add a third input into it, as its first input, in an attempt to tell it which document to target, but they are not always that great (Link1, Link2). If you want it to target a specific 'component' in an assembly, you can supply the ComponentOccurrence.Name value (as String) as the first (of 3) inputs. If targeting a referenced document, instead of a component, you can usually supply the Document.DisplayName value there, but that doesn't always work good either. The best way may be to do it the API way
Document.PropertySets.Item(4).Item("CustomPropertyName").Value =
but that will not create the property, if it does not already exist. You would have to use a Try...Catch block, where you try to access that property and/or set its value in the Try side, and if that fails, try to create it in the Catch side, and set its value there, as you create it. I'm using Index 4, because the 'custom' property set is always the fourth one. The PropertySet object has a method for creating a new property.
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Thanks for your answer.
I have tried, but unfortunately I can't get it to work.
Row 1 are supposed to be custom iPropertys and always use the changing values.
Can you maybe create me again a code for this "that I can just paste"?
That would be great, because I absolutely can not figure out what I'm doing wrong.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi @max.baumann07. Busy day today, but I made an attempt at editing the last full code you posted above, and added the stuff from the screen captured image too. Not sure if I fixed anything yet though, because I don't have your file set, but I attached the code as a text file for you to review.
Wesley Crihfield
(Not an Autodesk Employee)