Page 158 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

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.
Chapter 7
10345,5738,6029
24082,7459,3108
5119,8003,14972

Figure 7-1. 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:

10,345,5,738,6,029
24,082,7,459,3,108
5,119,8,003,14,972
Instead of the expected three rows of three cells, the above data file would produce a work-
sheet with three rows and four cells per row.

Figure 7-2. 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.

132