iLogic loop to read rows from Excel
Hi @frahaman7SKBG. Your task does sound possible to automate by iLogic code. Obviously there are a lot of variables involved that may complicate things though. In a task this big, we will need every little detail you can think of, so we have the knowledge about it that you have.
- Excel file's full file name (path, name, & file extension)
- Excel sheet's name
- Where on the sheet is the data we are after?
- Is there a title row? Where?
- Are there column titles or row titles? Where?
- What row & column does the actual data start, and how is the data laid out?
- Are there blank cells in this data set.
- Are we only going to be exporting Parts within the assembly, or parts & sub-assemblies?
- First level components only, all level components, or other?
- Are there content center components (or other read only stuff) involved that will need to be avoided?
- Are there some components set to Reference or Phantom or Purchased that will need to be avoided?
- (There are probably other questions too, so if you can thing of anything else that we might need to know...)
Since your real assembly may have 50+ components, I would suggest creating a simplified test assembly & Excel file to test any code posted here on, before trying it on the real thing, just to be safe, because we might not have a file set to pre-test on.
Wesley Crihfield
(Not an Autodesk Employee)
OK. I'm trying to plan out the whole project in my mind ahead of writing the code out to do it. I'm honestly not too familiar with the process of deriving an assembly from a multi-body part, especially when ModelStates are involved, and how those ModelStates carry over into the new documents. If I'm understanding the situation correctly, the main assembly here that was derived from the master multi-body part does not contain this list of ModelStates itself, but each of the components within this main assembly do have this list of ModelStates defined within them, correct. I assume that is why you are using a custom iProperty in both the main assembly and in those components as a 'middle man' to relay which ModelState you want to set each component within the assembly to, instead of setting an active ModelState within the main assembly.
Also, as I understand the 'steps' so far, Step 1 previously was to simply to ask the user which ModelState they want to switch all the assembly components to. But in this case, instead of using an InputBox like in your earlier rule, you want to pull a series of ModelState names from the Excel sheet and loop through them in this process. Does that sound right?
Then Step 2 is just to push the ModelState (for the current loop in this case) to the assembly's custom iProperty, then to the same custom iProperty of each of the components within the assembly.
Then Step 3 is to run the local rule within each of the assembly's components that will update the component to the chosen ModelStatte.
Then Step 4 takes place after Step 3 has finished, but before moving to the next ModelState name in the main loop of ModelState names (from Step 1), and this step is to export the main assembly as a STEP file, and as an RTA file, right? I know that RTA files are for Revit, but I don't currently use Revit, but I am aware that Inventor 2022 works more closely with it now. Would this be the Translator add-in named "Translator: RVT", that produces those ".rta" files? If so, I can most likely figure it out, or at least get you a good starting point for that export process code.
It seems like most of these steps could be combined into one larger iLogic rule with sub routines, if that would suit your needs. Especially since steps 2, & 3 are both so similarly looping through all components/refdocs of the assembly.
Wesley Crihfield
(Not an Autodesk Employee)
I have constructed most of this main external rule code now. The main part of this code I am unsure of is the sub routine for exporting the assembly as an ".rta" file. I inspected that translator add-in named "Translator: RVT" in an attempt to extract what 'Options' are exposed/available for us to specify within the code for it. It did not return anything, which seems a bit odd to me, since when you attempt to export an assembly using the manual process (File tab > Export > RVT) , it has tons of options in its dialog box. But maybe that's not the right translator for creating the ".rta" files you are referring to, I'm not sure. Also, this code accesses the Excel application and its objects directly, instead of using the iLogic GoExcel tools, so if you don't have the real Excel installed, it might fail.
Anyways, here is the what I have so far. Please revue it carefully before attempting to run it on your main assembly. You might even create a simpler, smaller set of files to test it on, just to test functionality first, to be safe.
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel
Sub Main
'get the 'active' main assembly
If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
MsgBox("An Assembly Document must be active for this rule to work. Exiting.", vbCritical, "iLogic")
Exit Sub
End If
Dim oADoc As AssemblyDocument = ThisApplication.ActiveDocument
oADef = oADoc.ComponentDefinition
oAuto = iLogicVb.Automation
oLocalRuleName = "Update_Model_State"
oExcelFile = "C:\Users\frahaman\Documents\Model State List.xlsx"
oExcelSheet = "Sheet1"
Dim oExcelRow As Integer = 2 'starting with 2, because first data row in Excel is row 2
'<<<< MAKE SURE ALL CELLS IN COLUMN SERIES HAVE VALUE >>>>
'<<<< WHEN EMPTY ONE IS ENCOUTERED, IT WILL EXIT RULE >>>>
For Each oOcc As ComponentOccurrence In oADef.Occurrences
'retrieve the ModelState name from Excel
Dim oModelStateName As String = String.Empty 'ensuring no carryover value
oModelStateName = GetModelStateName(oExcelFile, oExcelSheet, oExcelRow)
If String.IsNullOrEmpty(oModelStateName) Then
MsgBox("Failed to retrieve ModelState name from Excel. Exiting rule.", vbCritical, "iLogic")
Exit Sub
End If
'push that ModelState name to the assembly's custom iProperty
iProperties.Value("Custom", "Model_State:") = oModelStateName
'push that ModelState name to custom iPropertis of Components
iProperties.Value(oOcc.Name, "Custom", "Model_State:") = oModelStateName
'get the Document that the component represents
Dim oOccDoc As Document = Nothing 'ensuring no carryover value
oOccDoc = oOcc.Definition.Document
'get the local rule in that document
Dim oRule As iLogicRule = Nothing 'ensuring no carryover value
oAuto.GetRule(oOccDoc, oLocalRuleName)
If Not IsNothing(oRule) Then
oAuto.RunRuleDirect(oRule)
End If
'update the main assembly now to ensure changes are in place before export processes
oADoc.Update2(True) 'True = Accept Errors & Continue
'run export process sub routines here
ExportAssemblyToSTEP(oADoc, oModelStateName)
ExportAssemblyToRFA(oADoc, oModelStateName)
'advance to next row in Excel for next loop
oExcelRow = oExcelRow + 1
Next
'just a final message to indicate that the process has finished
MsgBox("Finished All Export Processes.", vbInformation, "iLogic")
End Sub
Function GetModelStateName(oXLFile As String, oXLSheet As String, oXLRow As Integer) As String
Dim oExcel As Microsoft.Office.Interop.Excel.Application = GetExcel
If oExcel Is Nothing Then Return String.Empty
Dim oWB As Workbook
Try
oWB = oExcel.Workbooks.Open(oXLFile)
Catch oEx As Exception
MsgBox("Failed to open specified Excel file." & vbCrLf & _
oEx.Message & vbCrLf & oEx.StackTrace, vbCritical, "iLogic")
Return String.Empty
End Try
Dim oWS As Worksheet
Try
oWS = oWB.Worksheets.Item(oXLSheet)
Catch oEx As Exception
MsgBox("Couldn't find specified Excel Sheet." & vbCrLf & _
oEx.Message & vbCrLf & oEx.StackTrace, vbCritical, "iLogic")
Return String.Empty
End Try
Dim oMSName As String
If IsNothing(oWS.Range("A" & oXLRow.ToString)) Then
Return String.Empty
Else
oMSName = CStr(oWS.Range("A" & oXLRow.ToString).Value2)
End If
Return oMSName
End Function
Sub ExportAssemblyToSTEP(oAssembly As AssemblyDocument, oMSName As String)
'your code for exporting the assembly to a STEP file here
'get the STEP translator add-in
Dim oSTEP As TranslatorAddIn
For Each oAddIn As ApplicationAddIn In ThisApplication.ApplicationAddIns
If oAddIn.DisplayName = "Translator: STEP" Then
oSTEP = oAddIn
End If
Next
If IsNothing(oSTEP) Then
MsgBox("STEP Translator Add-in not found. Exiting.", vbCritical, "iLogic")
Exit Sub
End If
'create needed variables for translator
oTO = ThisApplication.TransientObjects
oContext = oTO.CreateTranslationContext
oContext.Type = IOMechanismEnum.kFileBrowseIOMechanism
oOptions = oTO.CreateNameValueMap
oDataMedium = oTO.CreateDataMedium
'specify full file name of new STEP file it is to create
'get path of assembly, without file name or file extension
oPath = System.IO.Path.GetDirectoryName(oAssembly.FullFileName)
oDirChar = System.IO.Path.DirectorySeparatorChar
'integrate ModelState name in place of file name
oNewFile = oPath & oDirChar & oMSName & ".stp"
'Check to see if the STEP file already exists, if it does, ask if you want to overwrite it or not.
If System.IO.File.Exists(oNewFile) Then
oAns = MsgBox("A STEP file with this name already exists." & vbCrLf &
"Do you want to overwrite it with this new one?",vbYesNo + vbQuestion + vbDefaultButton2, "STEP FILE EXISTS")
If oAnswer = vbNo Then Exit Sub
End If
oDataMedium.FileName = oNewFile
If oSTEP.HasSaveCopyAsOptions(oAssembly, oContext, oOptions) Then
' Set application protocol.
' 2 = AP 203 - Configuration Controlled Design
' 3 = AP 214 - Automotive Design
oOptions.Value("ApplicationProtocolType") = 3
'oOptions.Value("IncludeSketches") = True
'oOptions.Value("export_fit_tolerance") = .000393701 'minimum
'oOptions.Value("Author") = ThisApplication.GeneralOptions.UserName
'oOptions.Value("Authorization") = ""
'oOptions.Value("Description") = iProperties.Value("Summary", "Title")
'oOptions.Value("Organization") = iProperties.Value("Summary", "Company")
Try
oSTEP.SaveCopyAs(oAssembly, oContext, oOptions, oDataMedium)
Catch
MsgBox("Your attempt to export this document as a STEP file FAILED!", vbOKOnly + vbExclamation, "Export to STEP Error")
End Try
End If
End Sub
Sub ExportAssemblyToRFA(oAssembly As AssemblyDocument, oMSName As String)
'your code for exporting the assembly to a RFA file here
End Sub
Function GetExcel() As Microsoft.Office.Interop.Excel.Application
Dim oXL As Microsoft.Office.Interop.Excel.Application
Try
'try to find an already running instance of the Excel Application
oXL = GetObject(, "Excel.Application")
Catch
'it wasn't found open, so create an instance of it (start the application)
oXL = CreateObject("Excel.Application")
'oXL = New Microsoft.Office.Interop.Excel.Application
Catch
MsgBox("Failed to Get/Create an instance of the Excel Application. Exiting.", , "")
Exit Function
End Try
Return oXL
End Function
Wesley Crihfield
(Not an Autodesk Employee)
Oh. I totally forgot about that other post, and did not remember what that file type was for, because I never use it. Maybe I can tackle this more tomorrow.
Wesley Crihfield
(Not an Autodesk Employee)
OK. I think I messed up the process I originally had in mind, and that's why you are still needing to run those two other rules to make stuff happen. I should have first gotten the list of model state names from the Excel sheet, then the main loop in the rule will be looping through each of those names. Within each loop, update the main assembly's custom iProperty to this model state name, then loop through every component, updating its custom iProperty, then running its local rule to change its model state. Then right after the loop of components, but still inside the loop of names, update the main assembly, then export the main assembly to the two file formats. Then loop to the next model state name.
I also modified the 'ExportAssemblyToRFA' sub routine to what I believe is the way you are wanting it. It should now be naming the files similarly to the STEP files, but with the ".rfa" file extension. (assembly's original path, model state name, then ".rfa" file extension)
Sub ExportAssemblyToRFA(oAssembly As AssemblyDocument, oMSName As String)
oADef = oAssembly.ComponentDefinition
oPath = System.IO.Path.GetDirectoryName(oAssembly.FullFileName)
oDirChr = System.IO.Path.DirectorySeparatorChar
oFFN = oPath & oDirChr & oMSName & ".rfa"
'export
oADef.BIMComponent.ExportBuildingComponent(oFFN)
End Sub
So here is the finished final code. It doesn't run those two other external rules, but just runs the one local rule in each component.
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel
Sub Main
'get the 'active' main assembly
If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
MsgBox("An Assembly Document must be active for this rule to work. Exiting.", vbCritical, "iLogic")
Exit Sub
End If
Dim oADoc As AssemblyDocument = ThisApplication.ActiveDocument
oADef = oADoc.ComponentDefinition
oAuto = iLogicVb.Automation
oLocalRuleName = "Update_Model_State"
oExcelFile = "C:\Users\frahaman\Documents\Model State List.xlsx"
oExcelSheet = "Sheet1"
Dim oExcelDataStartRow As Integer = 2 'starting with 2, because first data row in Excel is row 2
Dim oMSNames As List(Of String) = GetModelStateNames(oExcelFile, oExcelSheet, oExcelDataStartRow)
'<<<< MAKE SURE ALL CELLS IN COLUMN SERIES HAVE VALUE >>>>
'now loop through these ModelState names as the main loop in this rule
For Each oMSName In oMSNames
If String.IsNullOrEmpty(oMSName) Then Continue For
'push this ModelState name to the main assembly's custom iProperty
iProperties.Value("Custom", "Model_State:") = oMSName
'loop through each component, update its iProperty, and run its local rule
For Each oOcc As ComponentOccurrence In oADef.Occurrences
'push that ModelState name to custom iProperty of this component
iProperties.Value(oOcc.Name, "Custom", "Model_State:") = oMSName
'oOcc.ActiveModelState = oMSName
'get the Document that the component represents, so that we can run the local rule in it
Dim oOccDoc As Document = Nothing 'ensuring no carryover value
oOccDoc = oOcc.Definition.Document
'get the local rule in that document
Dim oRule As iLogicRule = Nothing 'ensuring no carryover value
oAuto.GetRule(oOccDoc, oLocalRuleName)
If Not IsNothing(oRule) Then
oAuto.RunRuleDirect(oRule)
End If
Next
'now main assembly and all components have updated iProperty
'and local rule has been ran in each component to change its ModelState
'now update the main assembly now to ensure changes are in place before export processes
oADoc.Update2(True) 'True = Accept Errors & Continue
'run export process sub routines here
ExportAssemblyToSTEP(oADoc, oModelStateName)
ExportAssemblyToRFA(oADoc, oModelStateName)
Next 'go to next ModelState name in the list we got from Excel
'just a final message to indicate that the process has finished
MsgBox("Finished All Export Processes.", vbInformation, "iLogic")
End Sub
Function GetModelStateNames(oXLFile As String, oXLSheet As String, oXLRow As Integer) As List(Of String)
Dim oExcel As Microsoft.Office.Interop.Excel.Application = GetExcel
If oExcel Is Nothing Then Return Nothing
Dim oWB As Workbook
Try
oWB = oExcel.Workbooks.Open(oXLFile)
Catch oEx As Exception
MsgBox("Failed to open specified Excel file." & vbCrLf & _
oEx.Message & vbCrLf & oEx.StackTrace, vbCritical, "iLogic")
Return Nothing
End Try
Dim oWS As Worksheet
Try
oWS = oWB.Worksheets.Item(oXLSheet)
Catch oEx As Exception
MsgBox("Couldn't find specified Excel Sheet." & vbCrLf & _
oEx.Message & vbCrLf & oEx.StackTrace, vbCritical, "iLogic")
Return Nothing
End Try
'determine last used row, so we know the limits of our loop through the rows
Dim oLastRowUsed As Integer = oWS.UsedRange.Rows.Count
'create variable to hold them all
Dim oNames As New List(Of String)
'loop through rows and add each name to the list
For oRow As Integer = oXLRow To oLastRowUsed
Dim oName As String = String.Empty
If IsNothing(oWS.Range("A" & oRow.ToString)) Then
'if an empty Cell is found in the series, it will stop,
'and return the list without any further names added
Return oNames
Else
oName = CStr(oWS.Range("A" & oRow.ToString).Value2)
End If
oNames.Add(oName)
Next
Return oNames
End Function
Sub ExportAssemblyToSTEP(oAssembly As AssemblyDocument, oMSName As String)
'your code for exporting the assembly to a STEP file here
'get the STEP translator add-in
Dim oSTEP As TranslatorAddIn
For Each oAddIn As ApplicationAddIn In ThisApplication.ApplicationAddIns
If oAddIn.DisplayName = "Translator: STEP" Then
oSTEP = oAddIn
End If
Next
If IsNothing(oSTEP) Then
MsgBox("STEP Translator Add-in not found. Exiting.", vbCritical, "iLogic")
Exit Sub
End If
'create needed variables for translator
oTO = ThisApplication.TransientObjects
oContext = oTO.CreateTranslationContext
oContext.Type = IOMechanismEnum.kFileBrowseIOMechanism
oOptions = oTO.CreateNameValueMap
oDataMedium = oTO.CreateDataMedium
'specify full file name of new STEP file it is to create
'get path of assembly, without file name or file extension
oPath = System.IO.Path.GetDirectoryName(oAssembly.FullFileName)
oDirChar = System.IO.Path.DirectorySeparatorChar
'integrate ModelState name in place of file name
oNewFile = oPath & oDirChar & oMSName & ".stp"
'Check to see if the STEP file already exists, if it does, ask if you want to overwrite it or not.
If System.IO.File.Exists(oNewFile) Then
oAns = MsgBox("A STEP file with this name already exists." & vbCrLf &
"Do you want to overwrite it with this new one?",vbYesNo + vbQuestion + vbDefaultButton2, "STEP FILE EXISTS")
If oAnswer = vbNo Then Exit Sub
End If
oDataMedium.FileName = oNewFile
If oSTEP.HasSaveCopyAsOptions(oAssembly, oContext, oOptions) Then
' Set application protocol.
' 2 = AP 203 - Configuration Controlled Design
' 3 = AP 214 - Automotive Design
oOptions.Value("ApplicationProtocolType") = 3
'oOptions.Value("IncludeSketches") = True
'oOptions.Value("export_fit_tolerance") = .000393701 'minimum
'oOptions.Value("Author") = ThisApplication.GeneralOptions.UserName
'oOptions.Value("Authorization") = ""
'oOptions.Value("Description") = iProperties.Value("Summary", "Title")
'oOptions.Value("Organization") = iProperties.Value("Summary", "Company")
Try
oSTEP.SaveCopyAs(oAssembly, oContext, oOptions, oDataMedium)
Catch
MsgBox("Your attempt to export this document as a STEP file FAILED!", vbOKOnly + vbExclamation, "Export to STEP Error")
End Try
End If
End Sub
Sub ExportAssemblyToRFA(oAssembly As AssemblyDocument, oMSName As String)
oADef = oAssembly.ComponentDefinition
oPath = System.IO.Path.GetDirectoryName(oAssembly.FullFileName)
oDirChr = System.IO.Path.DirectorySeparatorChar
oFFN = oPath & oDirChr & oMSName & ".rfa"
'export
oADef.BIMComponent.ExportBuildingComponent(oFFN)
End Sub
Function GetExcel() As Microsoft.Office.Interop.Excel.Application
Dim oXL As Microsoft.Office.Interop.Excel.Application
Try
'try to find an already running instance of the Excel Application
oXL = GetObject(, "Excel.Application")
Catch
'it wasn't found open, so create an instance of it (start the application)
oXL = CreateObject("Excel.Application")
'oXL = New Microsoft.Office.Interop.Excel.Application
Catch
MsgBox("Failed to Get/Create an instance of the Excel Application. Exiting.", , "")
Exit Function
End Try
Return oXL
End FunctionIt kind of seems to me like this whole job could be done much simpler though, without needing to use any iProperties, or any other rules. What if we simply got the list of names from Excel (like I'm doing in this last code), then while looping through those names, simply directly change the ActiveModelState of each component to the name in this loop.
Like with this line:
oOcc.ActiveModelState = oMSNameIt seems to me like that would eliminate the need for the custom iProperties and running the local rule for each component. Besides, that local rule is running on the Document that the component represents, not just the one specific component occurrence in the assembly. So if there were multiple components representing the same part, and you may want different ones to be at different model states, this would mess that up. I did notice that the local rule seems to be digging down pretty deep before setting the ModelState. Does that main document not have its own set of model states that match the list? I have no idea, because like I said, I'm not familiar with the effects of derived stuff from multi-body parts, so I could be wrong about this last idea.
Wesley Crihfield
(Not an Autodesk Employee)
