Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.
Owner2229
in reply to: Anonymous

Hi, the "MODEL" snipset is supposed to be placed after your original code, like this:

 

 

' Set the list of "SIZE" parameter
MultiValue.SetList("SIZE", "SIZE-AA", "SIZE-BB", "SIZE-CC")
' Determine the SIZE value If SIZE = "SIZE-AA" Then ' Load values from excel
GoExcel.TitleRow = 2
' Fill the "MODEL" parameter with found values MultiValue.List("MODEL") = GoExcel.CellValues("filename.xls", "Sheet1", "A3", "A5") ElseIf SIZE = "SIZE-BB" Then GoExcel.TitleRow = 7 MultiValue.List("MODEL") = GoExcel.CellValues("filename.xls", "Sheet1", "A8", "A10") ElseIf SIZE = "SIZE-CC" Then GoExcel.TitleRow = 12 MultiValue.List("MODEL") = GoExcel.CellValues("filename.xls", "Sheet1", "A13", "A15") End If Dim oRow As Integer
' Pick the value from "MODEL" parameter Select Case Parameter("MODEL") ' Set searched row, according to "MODEL" parameter value
Case "AA100": oRow = 3 Case "AA110": oRow = 4 Case "AA120": oRow = 5 Case "BB100": oRow = 8 Case "BB110": oRow = 9 Case "BB120": oRow = 10 'And so on for C... End Select
' Fill the "SIZE-VALUES" parameter with vaues from the excel table,
' using the previously set "oRow" variable with combination of columns "B" and "E" MultiValue.List("SIZE-VALUES") = GoExcel.CellValues("filename.xls", "Sheet1", "B" & oRow, "E" & oRow)

 

Here to the second snipset:

 

 

Sub Main()
    ' Check if the parameter "SIZE" does exist and has valid value. End this makro if not.
If CheckParam("SIZE") = "" Then MultiValue.SetList("SIZE", "SIZE-AA", "SIZE-BB", "SIZE-CC")
MsgBox("Please select value for SIZE parameter first.")
Exit Sub
End If
Dim Row As Integer
' Create new object table to store the excel table in Dim oMyValues(9, 5) As Object ' Set the range for values (9 rows, 5 columns)
' Open excel document GoExcel.Open("filename.xls", "Sheet1") Row = 1
' Load all values from the excel table For j = 3 To 5 ' Set the rows for AA... values GetValues(oMyValues, Row, j) Row = Row + 1 Next For j = 8 To 10 ' Set the rows for BB... values GetValues(oMyValues, Row, j) Row = Row + 1 Next For j = 13 To 15 ' Set the rows for CC... values GetValues(oMyValues, Row, j) Row = Row + 1 Next
' Close excel table GoExcel.Close Dim oModel As String oModel = Parameter("SIZE")
' Create new array list, taht will be used to fill the "SIZE_VALUES" parameter Dim MyArrayList As New ArrayList
' Go throught each of rows in previously created object table
' and check if any of the rows match our needs For i = 1 To UBound(oMyValues, 1) If oMyValues(i, 1) = oModel Then MyArrayList.Add(oMyValues(i, 2)) MyArrayList.Add(oMyValues(i, 3)) MyArrayList.Add(oMyValues(i, 4)) MyArrayList.Add(oMyValues(i, 5)) End If Next
' Check if the parameter "SIZE_VALUES" does exist NewParameter = CheckParam("SIZE_VALUES")
' Set the values for "SIZE_VALUES" parameter MultiValue.List("SIZE_VALUES") = MyArrayList End Sub
' Sub-function used to load values from the excel table Private Sub GetValues(oMyValues As Object, Row As Integer, j As Integer) oMyValues(Row, 1) = GoExcel.CellValue("A" & j) oMyValues(Row, 2) = GoExcel.CellValue("B" & j) oMyValues(Row, 3) = GoExcel.CellValue("C" & j) oMyValues(Row, 4) = GoExcel.CellValue("D" & j) oMyValues(Row, 5) = GoExcel.CellValue("E" & j) End Sub
' Function used to check if the parameter exists and create it if not Private Function CheckParam(oParaName As String) As String ' Return value as string oParameters = ThisApplication.ActiveDocument.ComponentDefinition.Parameters.UserParameters Try
Parameter(oParaName) = Parameter(oParaName)
CheckParam = Parameter(oParaName) Catch oParameter = oParameters.AddByValue(oParaName, "", UnitsTypeEnum.kTextUnits)
CheckParam = "" End Try End Sub

 

Here below is the rule to pick one of the "SIZE" parameter values. Use the number to pick (first/second/...) value from the parameter list.

 

Try
    Parameter("SIZE") = Parameter("SIZE")
Parameter("SIZE") = MultiValue.List("SIZE").Item(1)
Catch MsgBox("The parameter 'SIZE' doesn't exist") End Try

 

Consider using "Accept as Solution" / "Kudos" if you find this helpful.
- - - - - - - - - - - - - - -
Regards,
Mike

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live." - John F. Woods