SetValue

Syntax

XLS.SetValue Row, Col, NewValue[, UseBold][, ForceText][, Alignment][, Highlight]

Description

Sets a value into the selected cell, using the current font settings from the XLS.Font object. Optionally applies extra formatting to that cell.

The span for NewValue can hold multiple adjacent columns by placing a vertical bar ("|") between each value.

NEW: When NewValue is an ADODB.Field object (e.g. a field from an open recordset), the cell is automatically formatted based on the SQL data type of the field. This includes correct NumberFormat, date formatting (with time detection), currency display, integer/decimal formatting, and Boolean display (Yes/No).

When ForceText is False (default) and NewValue is not an ADODB.Field, the VarType of the value is used to determine the appropriate NumberFormat (Date, Integer, Double, Currency, Boolean, String with date/numeric detection, formulas starting with "=").

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.

See Also

InsertPicture, InsertSQL, MergeCells, Font, SelectRange, SetColor, Value

Example

Sub Main
Dim rs As New ADODB.Recordset
 
   If Not XLS.AppInit() Then Exit Sub
 
   XLS.AppVisible = True
   XLS.AddWorkBook
   XLS.AddWorkSheet "Demo"
 
   '--- Classic usage: string with pipe-separated values
   XLS.SetValue 1, 1, "<span style='color: #004080; font-weight: bold;'>Name</span>|Amount|<span style='color: #0000FF; font-weight: bold;'><span style='color: #800080; font-weight: bold;'>Date</span></span>", True
 
   '--- Classic usage: individual values
   XLS.SetValue 2, 1, "Widget A"
   XLS.SetValue 2, 2, 1250.50
   XLS.SetValue 2, 3, Now()
 
   '--- NEW: ADODB.Field - automatic type formatting
   SQL.RunRecordset "SELECT TOP 1 Nummer, Omschrijving, " _
                  & "Datum, Bedrag " _
                  & "FROM dbo.WerkOrderHeaders " _
                  & "WITH (READUNCOMMITTED)", rs
 
   If Not rs.EOF Then
      XLS.SetValue 4, 1, rs!Nummer       ' Integer format
      XLS.SetValue 4, 2, rs!Omschrijving ' Text format
      XLS.SetValue 4, 3, rs!Datum         ' Date format
      XLS.SetValue 4, 4, rs!Bedrag        ' Currency format
   End If
 
   SQL.CloseRecordset rs
   XLS.AppClose
End Sub