Update all component Model States via spreadsheet

[a81383]

Hello,

I'm working on creating a rule that will update model states within each component of an assembly (via excel). I believe I'm close. I have it cycling through all of the components. When I use MsgBox()'s for the values within the excel spreadsheets they return the correct values. 

I believe my issue is the changes to the model state spreadsheets are not being saved. I am using the "wb.Save" to save the workbook. So it may be that the component itself needs to save the changes?

Thank you for your time!

	Dim oADoc As AssemblyDocument = ThisDoc.Document
	Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
	Dim oOccs As ComponentOccurrences = oADef.Occurrences
	
	Call RecurseComponents(oOccs)

	Dim oOcdModelStates As ModelStates
	oOcdModelStates = oADef.ModelStates
	oFileName = oADef.Document.DisplayName

	Call ModelStateUpdate(oADef, oOcdModelStates)

Sub ModelStateUpdate(oDef, oMS)
'	Dim ModelStates As ModelState
'	oMS = oDef.ModelStates
	Dim userparams As UserParameters = oDef.Parameters.UserParameters

	Dim oWorkSheet
	oWorkSheet = oMS.ExcelWorkSheet
	
	Dim oWB
	oWB = oWorkSheet.Parent
	
	Dim oCells
	oCells = oWorkSheet.Cells

    Dim oTotalDictX As New Dictionary(Of Integer, String)
    For y As Integer = 1 To oMS.Count + 1
		oDimY = oCells.Item(y, 1)
	
		MSname = oDimY.Value

....

oDimY.Value = 10

....

oWB.Save 
oWB.Close

[dalton98]

Heres a good example/explination of using excel with ilogic: Link 

I think your code might be throwing an error in excel that you cannot see.

Column 1 is typically the model state name, correct? So you will not be able to give them each the same name. Also you will always need to start on row 2 b/c row 1 is the title.

Here's another post that shows how to change all model states' value simultaneously

https://forums.autodesk.com/t5/inventor-ilogic-and-vb-net-forum/change-part-number-of-all-model-stat...

[A.Acheson]

I had a little look at this earlier. It was failing at the workbook save. The cell was being read but not saved. It was erroring out. The sheet.parent was showing the component definition instead of the workbook so this is likely because I didn't reference the excel application and the worksheet and workbook objects. With this done it should hopefully work. Here is a article witha  sample of modifying an ipart through excel. Same principle as modelstates. 

Untested Code:

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel

	Dim oADoc As AssemblyDocument = ThisDoc.Document
	Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
	Dim oOccs As ComponentOccurrences = oADef.Occurrences
	
	Call RecurseComponents(oOccs)

	Dim oOcdModelStates As ModelStates
	oOcdModelStates = oADef.ModelStates
	oFileName = oADef.Document.DisplayName

	Call ModelStateUpdate(oADef, oOcdModelStates)
 

Sub ModelStateUpdate(oDef, oMS)
'	Dim ModelStates As ModelState
'	oMS = oDef.ModelStates
	Dim userparams As UserParameters = oDef.Parameters.UserParameters

	Dim oWorkSheet As Worksheet
	oWorkSheet = oMS.ExcelWorkSheet
	
	Dim oWB As WorkBook
	oWB = oWorkSheet.Parent
	
	Dim oCells
	oCells = oWorkSheet.Cells

    Dim oTotalDictX As New Dictionary(Of Integer, String)
    For y As Integer = 1 To oMS.Count + 1
		oDimY = oCells.Item(y, 1)
	
		MSname = oDimY.Value



                oDimY.Value = 10

      Next

oWB.Save 
oWB.Close

[a81383 · Accepted solution]

Hi @A.Acheson,

I really appreciate you looking into this. I was able to get this working utilizing code I received from @WCrihfield. The issue had to do with how I was referencing sub component occurrences that are model states. @WCrihfield code shown below resolves this issue.

Dim oADoc As AssemblyDocument = ThisDoc.Document
Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
Dim oOccs As ComponentOccurrences = oADef.Occurrences
Dim oOcc As ComponentOccurrence = oOccs.Item(1)
Dim oOcc As ComponentOccurrence = oOccs.ItemByName("Part1:1") 'works in top level only
Dim oOccMSName As String = oOcc.ActiveModelState
Dim oOccDoc As Document = oOcc.Definition.Document
If oOcc.Definition.IsModelStateMember Then
	oOccDoc = oOcc.Definition.FactoryDocument
End If
Dim oMSs As ModelStates = oOccDoc.ComponentDefinition.ModelStates

[WCrihfield · Accepted solution]

Hi @a81383.  I had not really looked into using the ModelStates.ExcelWorkSheet property that much, due to using either more traditional means, or the ModelStatesTable object directly, but you (and another recent poster) got me interested in it.  So, I decided to create an iLogic rule to explore it.  With the right references, as mentioned above, and some familiarity with how to navigate 2-dimensional data grid, as well as how to copy, edit, write back, whole blocks of data at a time, I wrote the following code, sort of as a guide, to help others out in this area.  It is working OK for me so far.  My testing was done on a PartDocument with multiple ModelStates, so if you are working with an assembly, or do not know which document type it may be, then you will obviously have to alter the first couple of lines to suit your needs.

Here is the code I created:

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Dim oPDoc As PartDocument = ThisDoc.FactoryDocument
Dim oPDef As PartComponentDefinition = oPDoc.ComponentDefinition
Dim oMSs As ModelStates = oPDef.ModelStates
Dim oWS As Excel.Worksheet = oMSs.ExcelWorkSheet
Dim oWB As Excel.Workbook = oWS.Parent
'Dim oWBType As String = TypeName(oWB)
'MsgBox("oWBType = " & oWBType,,"")
Dim oCells As Excel.Range = oWS.Cells
'a couple good hints here, in case you want to stay within bounds of Excel
'Dim oRowsCount As Integer = oWS.UsedRange.Rows.Count
'Dim oColsCount As Integer = oWS.UsedRange.Columns.Count
'copy the whole block of data to 'memory' (a 2-dimensional Array variable)
'As Object, because some data may be String, some Double, some Integer, etc.
Dim oAllRetrievedData(, ) As Object = oWS.UsedRange.Value
'now we can work with the data without working directly with Excel for a while (faster)
Dim oRows As Integer = oAllRetrievedData.GetUpperBound(0)
Logger.Info("oRows = " & oRows)
Dim oCols As Integer = oAllRetrievedData.GetUpperBound(1)
Logger.Info("oCols = " & oCols)
For oRow As Integer = 1 To oRows
	For oCol As Integer = 1 To oCols
		'again, As Object, because data may be String, or Double, or Integer, etc.
		Dim oCellData As Object = oAllRetrievedData(oRow, oCol)
		'Logger.Info("Cell at Row" & oRow & " & Col" & oCol & " = " & oCellData.ToString)
		'<<<< edit data here, if needed, then when done, write this block of data back to the source >>>>
		'notice how I am 'setting' the value...not to oCellData, but to the Array directly
		'If oCellData.ToString = "2 in" Then oAllRetrievedData(oRow, oCol) = "3 in"
	Next 'oCol
Next 'oRow
'here is where you write all the edited data back to the Excel Worksheet
oWS.UsedRange.Value = oAllRetrievedData
oWB.Save
oWB.Close
If oPDoc.RequiresUpdate Then oPDoc.Update2(True)
'If oPDoc.Dirty Then oPDoc.Save

If this solved your problem, or answered your question, please click ACCEPT SOLUTION .
Or, if this helped you, please click (LIKE or KUDOS) 👍.

[a81383]

Hi @WCrihfield, 

I've chosen to work with the excel spread sheets due to the sheer quantity of parameters and component/feature suppression I'm doing across multiple model states. I believed it would be faster. Plus I was running into complications with updating multiple model states when model states first came out and using the spread sheets were my workaround. Although, I am self taught when it comes to iLogic. So please let me know if there is a more efficient method I'm overlooking.

I've never used the Microsoft excel Imports before. Do these imports improve efficiency or do they simply allow access to various commands such as "UsedRange" ?