Functions to Write Data to Excel Worksheets
What's new in Analytica 4.2? >
The functions described on this page allow you to write directly to an existing Excel spreadsheet. They complement the WorksheetCell and WorksheetRange functions for reading data from Excel worksheets. These can be used as an alternative to OLE linking or ODBC, which are two other methods for sending data to spreadsheets. They are introduced in Analytica 4.2, and are only available from the Enterprise edition (or higher). They may be also used from ADE.
When you use these functions to write data to a spreadsheet, the values are sent when the function is evaluated. If upstream Analytica variables change, the result is not automatically transfered to Excel unless the variable (or button) that calls these functions is re-evaluated. This aspect differentiates it somewhat from outgoing OLE links when the Auto recompute outgoing OLE links preference is on. If the spreadsheet is currently loaded in Excel when these are evaluated, the running copy of the spreadsheet is altered. The changes only persist when SaveExcelWorkbook is called.
Only the functions relevant to writing data are described on this page. For the functions that read data, see Functions To Read Excel Worksheets.
SaveExcelWorkbook( workbook, filename )
Saves «workbook», which was originally obtained via OpenExcelFile, but which may have been modified since it was first opened, to the indicated filename, or to the original file if «filename» is omitted. The «filename» is interpreted relative to the CurrentDataDirectory.
WriteWorksheetCell( workbook, sheet, col, row, value )
Writes «value» (either a number or text) to the cell identified by «sheet», «col» and «row». Can also be used to write multiple values via array abstraction when col, row and value share one or more indexes. The first parameter, «workbook» must be an object obtained from the OpenExcelFile() function.
For writing very large arrays, WriteWorksheetRange is faster.
Examples
Both these write the value 3.5 to cell Sheet1!C5. Note that «col» can use either the character label or the numeric column position:
- WriteWorksheetCell( wb, "Sheet1", "C", 5, 3.5 )
- WriteWorksheetCell( wb, "Sheet1", 3, 5, 3.5 )
Here A is a 1-D array indexed by I. Writes the array as a column-vector starting at cell "D5":
- WriteWorksheetCell( wb, "Sheet1", 4, 4+@I, A )
Writes the same 1-D array as a row-vector starting at cell "E1":
- WriteWorksheetCell( wb, "Sheet1", "E", @I, A )
Writes a 2-D array B, indexed by I and J, to the sheet with the upper-left corner at cell B7, with the I dimension on the horizontal, the J on the vertical:
- WriteWorksheetCell( wb, "Sheet1", 1+@I, 6+@J, B )
WriteWorksheetRange( workbook, sheet, range, value, colIndex, rowIndex )
Writes «value» to a worksheet range, which may be identified either by a named range label or by a cell range. «value» may be atomic (i.e., a number or text), 1-D or 2-D. When 1-D, one of the optional index parameters, «colIndex» or «rowIndex», must be specified. When 2-D, both must be specified.
Ideally, the target range matches the data in terms of being rectangular with the same number of rows and columns. If the number of columns in «colIndex» exceeds the number of columns in «range», then only the first Size(colIndex) points are written. If «value» has only one column, then the same data is repeated for all rows in «range». If «value» has more than 1 column but less than the number of columns in «range», then the remaining cells in «range» are populated with #N/A.
Examples
Writes the value 6.0 to the cell Sheet1!B5:
- WriteWorksheetRange( wb, "Sheet1", "B5", 6.0)
Writes the 1-D array Cash_flow as a column-vector to a named range, already labelled as "Cash_flow":
- WriteWorksheetRange( wb, "Sheet1", "Cash_flow", Cash_flow, Time )
Writes a 1-D array B, indexed by I, as a row-vector:
- WriteWorksheetRange( wb, "Sheet2", "D5:D15", B, , I)
Writes a 2-D array ShippingCosts, indexed by Destination and Origin, to the range labelled "Shipping_costs":
- WriteWorksheetRange( wb, "Sheet3", "Shipping_costs", ShippingCosts, Destination, Origin )
Enable comment auto-refresher