Microsoft Office Tutorials and References
In Depth Information
The key to using a text file to represent spreadsheet data is in creating a clear division between
cells. Many programs use the comma as a delimiter , or character that represents the boundary
between two cells. For example, Figure 7-1 displays a worksheet as a table with three rows of
data and three cells per row.
You can open a file that contains comma-delimited data directly into Excel.
It would be incorrect, however, to write the data to a text file with comma delimiters while
using a comma as a thousands separator (in the United States) or decimal separator (in
Europe). Figure 7-2 shows what would happen if the same data list were written using com
mas both as thousands separators and as delimiters:
Instead of the expected three rows of three cells, the above data file would produce a
worksheet with three rows and four cells per row.
When delimiters appear in unexpected places, data chaos ensues.
You can use characters other than commas as delimiters if you need to bring in data that
includes commas, such as text or numbers with thousands separators. In the Text Import
Wizard, you can select an option button indicating which delimiter your file uses (comma,
space, tab, semicolon, or another character you type in yourself ). You can do the same thing
in Visual Basic for Applications (VBA) by setting the parameters of the Workbooks.OpenText
method. The OpenText method has the following full syntax:
expression.OpenText( FileName , Origin , StartRow , DataType , TextQualifier ,
ConsecutiveDelimiter , Tab , Semicolon , Comma , Space , Other , OtherChar ,
FieldInfo , TextVisualLayout , DecimalSeparator , ThousandsSeparator ,
TrailingMinusNumbers , Local )
Table 7-2 lists and describes the available parameters.