Macro Recorder Generated Text Import Code

After we stop the Macro Recorder, we are left with code that looks like Listing 2-2.

Listing 2-2. Macro-Generated Text Data Import Code Sub GetTextData()

' GetTextData Macro

With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;C:\projects\Excel2007Book\Chapters\Chapter 2\files\maillist.csv",

Destination:=Range("$A$1"))

.Name = "maillist"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 437

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = False

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = True

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = Array(2, 2, 2)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

Application.Goto Reference:="maillist"

Range( "A1").Select End Sub

One of the first differences to notice about this code when compared to the Access data import is how simple the connection string is. There is no complex Source string, and there are no Command object properties (CommandType and CommandText) to set. We simply tell Excel we're connecting to a text file, and then provide the path to the file and add it to the QueryTables collection via the Add method.

Then there are some common properties, such as the FillAdjacentFormulas and SavePassword properties. After the RefreshPeriod property, we begin to see a lot of text file-specific commands. We can set properties that define the type of text file we're working with by setting the TextFileParse type to xlFixedWidth if our data is arranged in columns of fixed widths, or xlDelimited if we have a character-delimited file. If we set this to xlDelimited, we can then set one or more of the following properties to True:

• TextFileTabDelimiter

• TextFileSemicolonDelimiter

• TextFileCommaDelimiter

• TextFileSpaceDelimiter

TextFileColumnDataTypes Property

The Macro Recorder generated this line of code:

.TextFileColumnDataTypes = Array(2, 2, 2)

Setting this property to 2 for all columns tells Excel to format the columns as text. These values correspond to the xlTextFormat constant in Table 2-2. If you enter more values into this array than there are columns in your data, the additional values are ignored. To see the numeric equivalent for Excel constants like these, type the name into the Immediate window (go to View > Immediate Window or press Ctrl+G) in the VBE, preceded by the ? output character. You can use the xlColumnDataType constants listed in Table 2-2 to specify the column data types used or the actions taken during a data import.

Table 2-2. TextFileColumnDataTypes Enums

Constant

Description

Value

xlGeneralFormat

General

1

xlTextFormat

Text

2

xlSkipColumn

Skip column

9

xlDMYFormat

Day-month-year date format

4

xlDYMFormat

Day-year-month date format

7

xlEMDFormat

EMD date

10

xlMDYFormat

Month-day-year date format

3

xlMYDFormat

Month-year-day date format

6

xlYDMFormat

Year-day-month date format

8

xlYMDFormat

Year-month-day date format

5

A quick way to find the value of any of Excel 2007's built-in constants or enumerations is to type it into the Immediate window, preceded by a ? character. This will display the value as shown in Figure 2-10.

Immediate xjl

?xlTextFormet

2

?xlDMYFormat

4

?xlGenerelForm£t

1

5 kip Column

3

jlU

Figure 2-10. Viewing constant values in the Immediate window

We've seen that Excel's Macro Recorder is a fast and easy way to explore some of the properties and methods available when bringing data into Excel. Let's write a little of our own code and explore some flexible methods of data transfer. These methods will work in Excel or any other VB- or VBA-enabled application, making them relatively portable and reusable.

Was this article helpful?

0 0

Post a comment