Returns or sets the thousands separator character thatMicrosoft Excel uses when you import a text file into a query table. The default is the system thousands separator character. Read/write String.
Remarks
Use this property only when your query table is based on data from a text file (with the QueryType property set to xlTextImport), especially when the file contains decimal and thousands separators that are different from those used on the computer, due to a different language setting being used.
The following table shows the results when you import text into Microsoft Excel using various separators. Numeric results are displayed in the rightmost column.
| System decimal separator | System thousands separator | TextFileDecimalSeparator value | TextFileThousandsSeparator value | Text imported | Cell value (data type) |
|---|---|---|---|---|---|
| Period | Comma | Comma | Period | 123.123,45 | 123,123.45 (numeric) |
| Period | Comma | Comma | Comma | 123.123,45 | 123.123,45 (text) |
| Comma | Period | Comma | Period | 123,123.45 | 123,123.45 (numeric) |
| Period | Comma | Period | Comma | 123 123.45 | 123 123.45 (text) |
| Period | Comma | Period | Space | 123 123.45 | 123,123.45 (numeric) |
Example
This example saves the original thousands separator and sets it to a period for the first query table on Sheet1, in preparation for importing a French text file (for example) into the U.S. English version of Microsoft Excel.
strDecSep = Worksheets("Sheet1").QueryTables(1) _
.TextFileThousandsSeparator
Worksheets("Sheet1").QueryTables(1) _
.TextFileThousandsSeparator = "."