Transferring data

A very common requirement is to copy data from one Xdata object to another. All Xdata objects have a CopyFrom method which copies data from any other Xdata object and appends it to what is already there (if anything). It is possible to copy a limited number of records by setting a start position and number of records to copy - alternatively setting these parameters to 0 copies all the records. Copying records from or into a TableData object is much faster than setting cells individually in VBA, as all transfers are buffered.

It is also possible to determine which fields to copy. If this information is omitted, then field names are matched (irrespective of field order) and all matching fields are copied. If the field list is specified as "*", the result is similar except that a check will be made that all fields on the destination object are present on the source object. This is a useful protection against errors. Or it is possible to specify an explicit list of fields to transfer.

ArrData also supports a CopyWhere method which combines a data copy with a filter operation, and a CopyCsv2 method which copies data from a csv file.

Also see Getting Started for an explanation of the use of CreateFrom.

Reports

It is straightforward to produce reports from data in a RsetData or ArrData object. Typically a report will be a table or data array on a worksheet and may or may not have a totals row. Typical code to create a report would be to initialise a Tabledata object Td on the table and then to create the report from the Xdata object Xd:

Td.Clear True, , , True 'Clears values and filters.
Td.CopyFrom Xd, 0, 0, "*" 'Copies in all the data from Xd.
Td.FormulaFill 'Fills in any fields which are formulae.
Td.FormatFill Range("Template") 'Copies in formats.

FormulaFill is used when there are formulae in the output. The existing formulae are saved when the TableData object is initialised and used to fill in formulae on all rows. This is normally only needed when the output is not a table, as table formulae are filled in automatically. To avoid losing formulae on empty output, it has the facility to always fill one row of formulae.

FormatFill is used to fill in formats in the output. This is recommended with both tables and non-table output, to ensure consistent formatting. Normally a template row is set up in the row immediately above the output header (which can be hidden) to include normal and conditional formats. Optionally this also fills validation or just conditional formats. It is also possible to use NumFormatFill to just fill number formats which are saved when the object is initialised, or BorderFill to fill vertical borders from the header row.

Where the fields in the report vary - normally where the data has been pivoted by SQL or using Pivot in ArrData - then a report is best created using the CreateFrom method of TableData rather than CopyFrom. This will replace any existing output with a new output with the new columns.

It is also possible to create a pivot table directly from a RsetData or ArrData object using the RefreshPivot method in XShared. This creates a pivot table the first time it is called for any given pivot table name on a worksheet. The pivot table can then be filled out in the normal way. Subsequent calls refresh the pivot table.