export to XL - problem with currency format from cells

[Michel Peeters]

I am exporting a recordset to an XL sheet.
I have to set the format from the XL sheet-columns in VBA.
The currency is changing from column to column (from field to field in the recordset)

strFormat = "# ##0.00 \" & strCur & "; - # ##0.00[Red] \" & strCur
xlsheet.Range(.Cells(2, intKol), .Cells(intRC, intKol)).NumberFormat = strFormat

intKol= the column number from the loop
intRC= total number of records

As long as strCur = "€" or = "$" or = "EUR" it works.
But when strCur = "USD" or "CHF" I get error 1004:
Unable to set the Number Format property of the Range class.

In regional setting I have:
country = belgium
currency = €
thousand seperator = space

Please help

 

[Michel Peeters]

For those who would have the same problem: I found it: the name of the currency has to be between double quotes:

strFormat = "# ##0.000 " & GcQUOTE & strCur & GcQUOTE & ";[red] - # ##0.000 " & GcQUOTE & strCur & GcQUOTE

where GcQUOTE is a constant for a double quote - declared in a general module:
Public Const GcQUOTE = """"
I am exporting a recordset to an XL sheet.
I have to set the format from the XL sheet-columns in VBA.
The currency is changing from column to column (from field to field in the recordset)

strFormat = "# ##0.00 \" & strCur & "; - # ##0.00[Red] \" & strCur
xlsheet.Range(.Cells(2, intKol), .Cells(intRC, intKol)).NumberFormat = strFormat

intKol= the column number from the loop
intRC= total number of records

As long as strCur = "€" or = "$" or = "EUR" it works.
But when strCur = "USD" or "CHF" I get error 1004:
Unable to set the Number Format property of the Range class.

In regional setting I have:
country = belgium
currency = €
thousand seperator = space

Please help