Create one last new method and name it GetAccessData3. Paste the code
from GetAccessData2 into it and make the following changes:
sConnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";" ??
& "User ID=Admin;" ??
& "Data Source=C:\projects\Excel2007Book\Files\Northwind 2007.accdb;"
We could also remove any property call from the QueryTable object??™s instantiation as well,
to further simplify the code??”but we??™ll leave that alone for now.
The GetAccessData, GetAccessData2, and GetAccessData3 subroutines show all three versions
of this code with each version becoming more succinct than the last.
Importing Text Data
Before we begin writing our own code to import data, let??™s record one more macro to see some
of the settings available when we bring in data from a text file.
1. Create a new workbook and name it DataAccessSample02.xlsm.
2. Create a new macro and name it GetTextData.
3. On the Data ribbon, choose From Text.
4. Navigate to the myfilepath\maillist.csv file, and then choose the Import command.
The Text Import wizard will open, as shown in Figure 2-3.
Figure 2-3. The Text Import wizard
CHAPTER 2 n DATA IN, DATA OUT 48
The file is comma-delimited (the default selection in the Original Data Type section), so
just click Next.
On Step 2 of the Text Import wizard, the default delimiter is Tab. The ???Data preview???
section should show us our columns separated by vertical lines. Since our file is not tabdelimited,
the preview shows our raw data file (see Figure 2-4).
Pages:
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76