Entering a location for the data
Figure 2-9. Data imported from maillist.csv
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
'
CHAPTER 2 n DATA IN, DATA OUT 51
'
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.
Pages:
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78