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 SpreadsheetCell and SpreadsheetRange 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 SpreadsheetSave 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.
<a name="SpreadsheetSave" />
SpreadsheetSave( workbook, filename )
Saves «workbook», which was originally obtained via SpreadsheetOpen, 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.
<a name="SpreadsheetSetCell" />
SpreadsheetSetCell( 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 SpreadsheetOpen() function.
For writing very large arrays, SpreadsheetSetRange 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:
- SpreadsheetSetCell( wb, "Sheet1", "C", 5, 3.5 )
- SpreadsheetSetCell( 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":
- SpreadsheetSetCell( wb, "Sheet1", 4, 4+@I, A )
Writes the same 1-D array as a row-vector starting at cell "E1":
- SpreadsheetSetCell( 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:
- SpreadsheetSetCell( wb, "Sheet1", 1+@I, 6+@J, B )
<a name="SpreadsheetSetRange" />
SpreadsheetSetRange( workbook, range, value, colIndex, rowIndex, sheet )
Writes «value» to a worksheet range, which may be identified either by a named range label or by a cell range. When «range» is a named range label, it is not necessary to specify the worksheet. When «range» is a cell coordinate range, such as 'C6:F9', then the «sheet» must also be specified. The sheet can be specified using the optional «sheet» parameter with either a textual sheet name or a numeric sheet number, or it may be specified in the range using the notation 'Sheet1!C6:F9'. You can also specify a single cell, e.g., 'C9'.
«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:
- SpreadsheetSetRange( 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":
- SpreadsheetSetRange( wb, "Cash_flow", Cash_flow, Time )
Writes a 1-D array B, indexed by I, as a row-vector, here in the third worksheet:
- SpreadsheetSetRange( wb, "D5:D15", B, , I, sheet:3)
Writes a 2-D array ShippingCosts, indexed by Destination and Origin, to the range labelled "Shipping_costs":
- SpreadsheetSetRange( wb, "Shipping_costs", ShippingCosts, Destination, Origin )
Other Notes
In early 4.2 beta builds, before 4.2.0.21, these functions were present as SaveExcelWorkbook, WriteWorksheetCell and WriteWorksheetRange. Those names have now been deprecated. They will still work for a while, but may be removed in a future Analytica build. Also, the parameters of SpreadsheetSetRange differ slightly from WriteWorksheetRange -- the sheet parameter has been made optional and moved from being the 2nd parameter to being the last parameter. It is now not necessary for named ranges.
Enable comment auto-refresher