Description
| Inserts the result of a SQL query (or an existing ADODB.Recordset) as a formatted table into the active worksheet, starting at the specified row and column position.
The method creates a complete table with optional column headers, automatic column formatting based on SQL data types (dates, numbers, currency, text), SQLIGENT formatting support (FONTBOLD, FONTERRORLEVEL, FORECOLOR, BACKCOLOR, BALLOONTIPS, Hidden_, Locked_ columns), optional totals row (SUM for currency/decimal fields, AVERAGE for floating-point fields), and automatic column width adjustment.
Rows and columns are one-based arrays.
Parameter
| Description
| Row
| Long integer, required. The row in the active worksheet.
| Col
| Integer, required. The (first) column in the active worksheet.
| NewValue
| Variant, required. Can hold any numeric, string, date or Boolean value. NEW: Can also be an ADODB.Field object for automatic SQL-type-based formatting. When using multiple values to set adjacent cells, use a string with vertical bars ("|") to separate each value.
| UseBold
| Boolean, optional. If True, the new value(s) are formatted in bold text. Default value is False.
| ForceText
| Boolean, optional. When True, all values are treated as plain text (NumberFormat "@"). When False (default), automatic type detection is applied. This parameter is ignored when NewValue is an ADODB.Field.
| Alignment
| Enumerated, optional. Applies alignment using these values: xlAlignCenter, xlAlignDistributed, xlAlignJustify, xlAlignLeft, xlAlignNotApplicable, xlAlignRight (default when omitted).
| Highlight
| Boolean, optional. When True, the selection will be highlighted using standard highlight color settings.
|
|
Example
| Sub Main
Dim sQuery As String
If Not XLS.AppInit() Then Exit Sub
XLS.AppVisible = True
XLS.AddWorkBook
XLS.AddWorkSheet "Orders"
'--- Simple query, table starts at A2 with headers and totals
sQuery = "SELECT Nummer, Omschrijving, Datum, Bedrag " _
& "FROM dbo.WerkOrderHeaders WITH (READUNCOMMITTED) " _
& "WHERE Admin = 1 AND Jaar = 2026 " _
& "ORDER BY Nummer"
XLS.InsertSQL sQuery, 2, 1, , , , , "Order Overview 2026"
'--- Second sheet: use an existing recordset, no totals
XLS.AddWorkSheet "Details"
Dim rs As New ADODB.Recordset
SQL.RunRecordset "SELECT * FROM dbo.ArtikelsTam " _
& "WITH (READUNCOMMITTED) " _
& "WHERE Deleted = 0", rs
XLS.InsertSQL "", 1, 1, True, False, rs
SQL.CloseRecordset rs
XLS.SaveWorkBook , , "o:\documenten\export.xlsx"
XLS.CloseWorkBook
XLS.AppClose
End Sub
|