DataSource Property

DataSource(Name As Variant,
            MaxRows As Long,
            DataSource As Variant)

DataSource sets a data source to bind to template data markers. The data source may be an ADO Recordset, a variable, a one dimensional array or a two dimensional array. A data marker is a string beginning with "%%=" (or "%%=$" for variables and arrays), which is placed in a cell of the template spreadsheet. Each data marker specifies a database field, variable, or array column to insert in the spreadsheet column containing the marker. Every data marker in the template spreadsheet must refer to a DataSource which has been assigned in the ExcelWriter script.

You may assign many DataSources to a single spreadsheet.

DataSources should be set after all data manipulation is complete and before calling the Process method.

DataSource takes two optional parameters:

• Name
  The data source name specified in the data marker (e.g. "Orders" in %%=Orders.OrderID).
  The string passed to the Name parameter must begin with a letter.
  Default value: "" (empty string). If you wish to use binding by order,
  you must leave the name parameter empty.
  
  
  
• MaxRows
  The maximum number of rows to return from a database or 2-dimensional array.
  Default value: 65536.

Assign the optional parameters Name and MaxRows, as follows.

'--- Setting Name and MaxRows.
'--- Name must begin with a letter. 
'--- This format cannot be used with binding by order
	obj.DataSource("Name", 50) = MyDataSource
	
'--- Setting Name only. 
'--- Name must begin with a letter 
'--- This format cannot be used with binding by order
	obj.DataSource("Name") = MyDataSource

'--- Setting MaxRows only. Use any of the following formats.
	obj.DataSource (, 50)  
	obj.DataSource (50)   
		
'--- If neither parameter is set, default settings will be used.
	obj.DataSource = MyDataSource

Note: When the name parameter is not set, the DataSource should be referenced by number in your data markers, according to the order of DataSource assignments. See binding by order.

DataSources with different formats and different types of data may be used together the same script.
For example:

obj.DataSource = myVariable
obj.DataSource("Orders", 200) = myRecordset
obj.Datasource("twoD") = myArray
obj.Datasource = anotherRecordset

Note: ExcelWriter expects data in the order row, column. Therefore all 2-dimensional arrays must be created to take rows for the first dimension and columns for the second dimension. For example, if you want to create an array in VBScript with 4 rows and 2 columns, you should declare it as follows:

dim myArray(3,1)'--- VBScript arrays are 0-based
'--- Alternatively you can set the lower and upper bound:
'dim myArray(1 to 4, 1 to 2)

Data marker formats
Recordset data marker	%%=[DataSourceNameOrNumber.]FieldNameOrNumber
Variable data marker	%%=$DataSourceNameOrNumber
One-Dimensional Array data marker	%%=$DataSourceNameOrNumber
Two-dimensional Array data marker	%%=$DataSourceNameOrNumber.#ColumnNumber
Important: In ExcelWriter version 3.1 and higher, when binding a DataSource by position, if the number of fields in a particular recordset is not known in advance, you may include excess datamarkers in the template, and ExcelWriter will remove any that are not used. DataSource order numbers, and field position numbers, are 1-based. If ExcelWriter encounters %%=#0[.field] or %%=[DataSource.]#0 in a data marker, an error will occur. Note: In ExcelWriter versions before 3.1, DataSource and field numbers were 0-based. If you upgraded from an earlier version, you may need to modify your code.

DataSource Formats for Binding to Recordset
	Format	Data Marker	Code
1	Generic	%%=DataSourceName.FieldName	obj.Datasource("DataSourceName") = MyDataSource
2	Implicit bind	%%=FieldName Note: If there are many datasources assigned, implicit bind assumes the first.	obj.Datasource = MyDataSource
3	Bind by order	%%=#1.FieldName %%=#2.FieldName	obj.Datasource = MyDataSource obj.Datasource = MyDataSource1
4	Bind by field position	%%=DataSourceName.#1 %%=DataSourceName.#2	obj.Datasource("DataSourceName") = MyDataSource
2 & 3	Implicit Bind with Bind by order	%%=FieldName %%=#2.FieldName	obj.Datasource = MyDataSource obj.Datasource = MyDataSource1
1,2,3  & 4	Generic, Implicit, Bind by order, and Bind by field position	%%=#1 %%=#2.#2	obj.Datasource = MyDataSource obj.Datasource = MyDataSource1
Note: In ExcelWriter version 3.1 and higher, DataSource sequential numbers are 1-based; when binding by order the first data marker is %%=#1[.field]. If ExcelWriter encounters a %%=#0[.field] data marker, an error will occur. Note: In ExcelWriter version 3.1 and higher, Field position sequential numbers are 1-based. If ExcelWriter encounters a data marker containing field 0 (e.g. %%=DataSource.#0), an error will occur. Note:In ExcelWriter version 3.1 and higher, when binding a DataSource by position, if the number of fields in a particular recordset is not known in advance, you may include excess datamarkers in the template, and ExcelWriter will remove any that are not used.

DataSource formats for binding to Variables
	Format	Data Marker	Code
1	Generic	%%=$DataSourceName	obj.Datasource("DataSourceName") = MyVar
2	Bind by order	%%=$#1 %%=$#2	obj.Datasource = MyVar obj.Datasource = MyVar1
1 & 2	Generic Bind with Bind by order	%%=$DataSourceName %%=$#2	obj.Datasource("DataSourceName") = MyVar1 obj.Datasource = MyVar2
Note: As part of the bind to variable functionality, it is possible to directly assign a string to a datasource. ExcelWriter will automatically convert it to a variant. For example: obj.Datasource("DataSourceName") = "hello world"

DataSource formats for binding to One-Dimensional Arrays
	Format	Data Marker	Code
1	Generic	%%=$DataSourceName	obj.Datasource("DataSourceName") = MyArray
2	Bind by order	%%=$#1 %%=$#2	obj.Datasource = MyArray1 obj.Datasource = MyArray2
1 & 2	Generic Bind with Bind by order	%%=$DataSourceName %%=$#2	obj.Datasource("DataSourceName") = MyArray1 obj.Datasource = MyArray2
Note: The data from a one-dimensional array gets loaded into rows beginning at the cell with the corresponding data marker, i.e. for each element of the array, a new row is inserted. If you wish to accomplish the equivalent of displaying a one-dimensional array horizontally across columns, you should create a two dimentional array with only one column and place datamarkers horizontally.

DataSource formats for binding to Two-Dimensional Arrays
	Format	Data Marker	Code
1	Generic	%%=$DataSourceName.#1	Dim TwoDArray(5,10) obj.Datasource("DataSourceName") = TwoDArray
2	Bind by order	%%=$#1.#1 %%=$#2.#1	Dim TwoDArray1(5,10) obj.Datasource = TwoDArray1 Dim TwoDArray2(12,3) obj.Datasource = TwoDArray2
1 & 2	Generic Bind with Bind by order	%%=$DataSourceName.#1 %%=$#2.#1	Dim TwoDArray1(5,10) obj.Datasource("DataSourceName") = TwoDArray1 Dim TwoDArray2(12,3) obj.Datasource = TwoDArray2
Note: In ExcelWriter version 3.1 and higher, DataSource order and column position numbers are 1-based. If ExcelWriter encounters data markers with %%=#0.#columnNumber or %%=DataSource.#0, an error will occur. Column position numbers in the data markers must begin with #1, even if the lower bound of the array is other than 1, as in: Dim TwoDArray(1 to 5, 40 to 45) Note: ExcelWriter imports data in the order row, column. Therefore, two-dimensional arrays must be created with rows for the first dimension and columns for the second dimension. For example: Dim myArray(5,10) will fill 6 rows, 11 columns (VBScript arrays are 0-based by default). Note: Array columns must always be bound by position, because unlike recordsets, they do not have field names:    %%=$MyArray.#1    %%$#3.#2 Note: In ExcelWriter version 3.1 and higher, if the number of columns in a particular 2-D array is not known in advance, one may put as many datamarkers as desired in the template spreadsheet, and ExcelWriter will remove any that are not used.