How to: MultiValue.List(oUserParam) = GoExcel.NamedRangeValue(oNamedRange)

[WCrihfield]

I am looking for any iLogic examples where I can set the values of a 'MultiValue.List(oUserParam)  with the values from a GoExcel.NamedRangeValue(oNamedRange).  I can can set a regular non-MultiValue Parameter to the value of a NamedRangeValue, when the NamedRangeValue reffers to a single cell in the Excel file.  I'm just not really clear on setting the MuliValue.List values from a NamedRangeValue that has multiple cells within a single column.  I also know how to set the MultiValue.List from a series of values in a Excel Column using C:3 to C:12, but that won't work for my situation, because the excel file changes and the Column letter may change, and the Row numbers may change.

If anyone has any examples they are willing to share, that would be great.

I seem to recall this process possibly involving breaking the NamedRangeValue down into ArrayLists, if it involved multiple columns, then using the ArrayLists to populate the MultiValue.List, but I don't recall if this may be neccessary if the NamedRangeValue is just several consecutive cells in a single Column.

[J-Camper]

I think this should work:

Dim excelFileName As String = "C:\Test_book.xlsx"
Dim sheetName As String = "Sheet1"
GoExcel.Open(excelFileName, sheetName)

rangeArray = GoExcel.NamedRangeValue("MyRange")'This is the named range in excel

Dim oRowsCount As Integer = rangeArray.GetLength(0) '0 is rows
Dim oColCount As Integer = rangeArray.GetLength(1) '1 is columns

Dim oRowIndex As Integer
Dim oColIndex As Integer

Dim oList As New List(Of String)
For oRowIndex = 1 To oRowsCount  
	'add value of each row one by one
	For oColIndex = 1 To oColCount 
    	oList.Add(rangeArray(oRowIndex,oColIndex))
	Next 
Next

Dim oUserParam As String = "test"
MultiValue.List(oUserParam) = oList

GoExcel.Close

I found documentation for this code from This Blog 

[WCrihfield · Accepted solution]

Thank you @J-Camper . I knew I had seen that exact blog before somewhere, maybe a year or so ago, I just couldn't recall where I had seen it.

My code already had a similar section of code in it, I was getting a message saying it couldn't find a certain .dll file, which I knew wasn't the problem with the code.

The NamedRangeValue was just being recognized as an Object, and therefore its only options were ToString.

So, after some trial & error tweaking, I tried defining the NamedRangeValue as an Array, then the iLogic Rule Editor recognised it and showed all the options, including the GetLength option.

That made the .dll message go away.

But, even after figuring that portion out, something was still not working.

I was still getting a message saying "Object reference not set to an instance of an object."

But after more trial & error tweaking, I finally got it to work.

It turns out, that I just needed to add a 'If-Then-Else-End If' statement right before the oList.Add statement to weed out any empty cell within the NamedRangeValue.

That eliminated the object reference message, and fixed it.

My code is basically to search for a specific UserParameter, if found, offer to update its values, if not found create it and set its values from the excel reference.

This code needed to work for both Parts & Assemblies, so I included the DocumentType filter at the top.

Below is my final working code.

Dim oDocType As DocumentTypeEnum = ThisApplication.ActiveDocumentType
Dim oPDoc As PartDocument
Dim oPDef As PartComponentDefinition
Dim oADoc As AssemblyDocument
Dim oADef As AssemblyComponentDefinition
Dim oParams As Parameters

If oDocType = DocumentTypeEnum.kPartDocumentObject Then
	oPDoc = ThisApplication.ActiveDocument
	oPDef = oPDoc.ComponentDefinition
	oParams = oPDef.Parameters
ElseIf oDocType = DocumentTypeEnum.kAssemblyDocumentObject Then
	oADoc = ThisApplication.ActiveDocument
	oADef = oADoc.ComponentDefinition
	oParams = oADef.Parameters
Else
	MessageBox.Show("This rule '" & iLogicVb.RuleName & "' only works on Part or Assembly Documents.", "WRONG DOCUMENT TYPE")
	Return
End If

Dim oUParams As UserParameters = oParams.UserParameters
Dim oParamName As String = "CHASSIS"
Dim oExists As Boolean = False

Dim oFile As String = "S:\Engineering\Shared Standard Parameters.xlsx"
Dim oSheet As String = "CHASSIS NAMES & CAB HEIGHTS"
Dim oRangeName As String = "CHASSIS_NAMES"
GoExcel.Open(oFile, oSheet)
GoExcel.DisplayAlerts = False

Dim oNamedRange As Array = GoExcel.NamedRangeValue(oRangeName)
Dim oRowsCount As Integer = oNamedRange.GetLength(0) 'counts rows in named range
Dim oColCount As Integer = oNamedRange.GetLength(1) 'counts colmuns in named range
Dim oRowIndex As Integer
Dim oColIndex As Integer

Dim oList As New List(Of String)

For oRowIndex = 1 To oRowsCount
	For oColIndex = 1 To oColCount
		If oNamedRange(oRowIndex, oColIndex) = "" Then
		Else
			oList.Add(oNamedRange(oRowIndex, oColIndex))
		End If
	Next
Next

For Each oUParam As UserParameter In oUParams
	If oUParam.Name = oParamName Then
		oExists = True
		oAnswer = MessageBox.Show("The user parameter named " & oParamName & " already exists." & vbNewLine &
		"Do you want to overwrite its values?", "PARAMETER EXISTS", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
		If oAnswer = vbNo Then
			Return
		ElseIf oAnswer = vbYes Then
			MultiValue.List(oParamName) = oList
		End If
	End If
Next

If oExists = False Then
	Dim oNewParam As UserParameter = oUParams.AddByValue(oParamName, "FORD E-SERIES", "text")
	MultiValue.List(oParamName) = oList
	oNewParam.Comment = "CHOOSE ONE"
	oNewParam.IsKey = True
End If

GoExcel.Close
'Output Parameters values from this rule to the Model. (Use this before Document Update)
RuleParametersOutput()
'Immediately update the current document.
InventorVb.DocumentUpdate()

I hope this helps others the the future, if they attempt to do this.

I have been using NamedRangeValue for quite a while, but always as a single cell, which always worked just fine without having to redefine it.