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
