| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "XL3SqlArea"
(→Examples) |
(→Parameters) |
||
| Line 15: | Line 15: | ||
|- | |- | ||
| {{Code|SendUpdates}} | | {{Code|SendUpdates}} | ||
| − | | A cell that | + | | A cell that determines if the database should be updated. Requires a boolean value (TRUE / FALSE), this cell is normally controlled with an XL3Link() formula, which sets teh value to TRUE, and it will be rest to FALSE when the update has run. |
|- | |- | ||
| {{Code|Connection}} | | {{Code|Connection}} | ||
Revision as of 10:45, 8 August 2017
Creates and updates table range based on the results of a SQL Query.
Contents
[hide]Syntax
XL3SqlArea( RunQuery, SendUpdates, Connection, OutputRange, Sql, Parameters, UpdateTableSql, PostUpdateSql, PostUpdateParameters, FormulaBasedColumns, OutputTotalRows, StartRow)
Parameters
| Parameter | Description |
|---|---|
| RunQuery | A cell reference that contains TRUE when the SQL Statement should be run. After execution the cell reference will be set to FALSE or an error message if an error occurred. |
| SendUpdates | A cell that determines if the database should be updated. Requires a boolean value (TRUE / FALSE), this cell is normally controlled with an XL3Link() formula, which sets teh value to TRUE, and it will be rest to FALSE when the update has run. |
| Connection | The connection to use to connect to the database. This can either be the Connection ID of a workbook relational connection or a connection string. |
| OutputRange | A cell containing the results of the SQL query. |
| Sql | The SQL statement to run. |
| Parameters | If the SQL statement includes "?" for each parameter, you can pass a range of cells that contain the parameters to pass. The order of the cells need to match the order they appear in the SQL statement. |
| UpdateTableSQL | If the SQL statement to run contains several tables or a stored procedure, you can give the name of the table to update here. |
| PostUpdateSQL | A SQL statement that is run following the update of the data and again once the data has be re-retrieved in the workbook. |
| PostUpdateParameters | Any parameters needed for the PostUpdateSQL |
| FormulaBasedColumns | Pass a set of ranges that include the names of any columns that will not get overridden when loading the workbook. This allows a column to contain an Excel calculation and the value will be calculated and stored in the column when the table is saved. |
| OutputTotalRows | An excel range to output the total number of rows retrieved by the query - allows a Pager to be used to page through the results |
| StartRow | The row number to start displaying the data from - allows a pager to be used to page through the results |
Examples
XL3SqlArea(B3,,1,B4,A1)
When B3 is set to TRUE (via an XL3Link) then the SQL statement in A1 is run and the result of the query is outputted to the cell B4. After completion B3 will be reset to FALSE – ready for the next updated by an XL3Link.
