Many times you will need to export assortment information to excel, but you will not know in advance how many assortment items the user will have filled. In order to export a variable number of pieces of information do the following. We will use the Assortment UPCs for this example.

There are 3 scripting sections that you need to work with:

The “Globals” area is where you set up any global variables or functions you will need. Here we will declare a stringlist to hold all valid UPCs which will be used in the OnBeforeData and OnAfterData scripts.

' Declare our global variables that will need to hold their values in
// other routines
dim slUPCs  // a list to hold valid UPCs
dim EndingRow // the EndingRow in Excel for each product
 
// A Function to retrieve a valid up or a blank
// index is the index into the extra rows of info
FUNCTION GetListValue(list, index)
  if (list.count > index) then
     GetListValue = list.Strings[index]
  else
     GetListValue = ""
  end if
END FUNCTION
 
// function to output a formula
// ws - an excel Worksheet variable a la excel.ActiveSheet
// col - the column name - e.g. "AG"
// row - the excel row number - e.g. 25
// formula - a template for the formula with python like string format vars.
//      e.g. "=B{0} + E{1} * F{2}"
// data - an array of variables to complete the formula
//      e.g. [row, row - 4, 43] would replace {0} with the value of the row variable
//                                            {1} with the value of row-4
//                                            {2} with 43
SUB exportFormula(ws, col, row, formula, data)
  // enumerate through the data array and substitute the values
  for i = low(data) to high(data)
    formula = ReplaceAll(formula, "{" + i + "}", row, false)
  next
  // output to Excel
  addr = col + row
  Rng = ws.Range(addr)
  Rng.formula = formula
END SUB