Listing Opening Delimited Files Simplified

Sub TestOpenDe1imitedFi1e() Dim wb As Workbook Dim vFields As Variant

' The third column of the orders file ' is a date column (MM/DD/YYYY). ' The rest are general (default) vFields = Array(Array(3, xlMDYFormat))

Set wb = OpenDe1imitedFi1e("C:\tab delimited orders.txt", 2, _ xlTextQualifierNone, False, vbTab, vFields)

Set wb = Nothing End Sub

Function OpenDe1imitedFi1e(sFi1e As String, _

IStartRow As Long, _ TxtQualifier As XlTextQualifier, _ bConsecutiveDelimiter As Boolean, _ sDelimiter As String, _

Optional vFieldlnfo As Variant) As Workbook

On Error GoTo ErrHandler

If IsMissing(vFieldlnfo) Then

Application.Workbooks.OpenText _ Fi1ename:=sFi1e, _ StartRow:=1StartRow, _ DataType:=x1De1imited, _ TextQua1ifier:=TxtQua1ifier, _ ConsecutiveDe1imiter:=bConsecutiveDe1imiter, _ Other:=True, _ OtherChar:=sDe1imiter

Else

Application.Workbooks.OpenText _ Filename:=sFile, _ StartRow:=lStartRow, _ DataType:=xlDelimited, _ TextQualifier:=TxtQualifier, _ ConsecutiveDelimiter:=bConsecutiveDelimiter, Other:=True, _ OtherChar:=sDelimiter, _ FieldInfo:=vFieldInfo

End If

Set OpenDelimitedFile = ActiveWorkbook ExitPoint:

Exit Function ErrHandler:

Set OpenDelimitedFile = Nothing Resume ExitPoint End Function

NOTE Listings 15.1 and 15.2 refer to sample data files located in the root directory of your C:\ drive. You should update the file location of these files accordingly if you try these listings.

The OpenDelimitedFile in Listing 15.1 reduces the number of parameters from 18 to 6, and one of the parameters is optional— the vFieldlnfo parameter. This is helpful because out of all the parameters, vField-Info is the most tedious to prepare. Because I don't use the field info parameter very much, it made sense to make this parameter optional in order to make this function easier to use. After turning error handling on, the next thing to do is check to see whether vFieldlnfo was specified using the VBA IsMissing function. The call to OpenText is the same in both cases other than the inclusion of the FieldInfo parameter. Assuming all goes well, OpenDelimitedFile sets a reference to the ActiveWorkbook. Opening a file always results in the ActiveWorkbook being set to the opened file. If an error occurs, however, the error-handling code merely sets the return value to Nothing and then exits the function at the exit point.

One other comment regarding this listing is the use of the Other and OtherChar parameters in the OpenText method call. Rather than fiddling with setting the Tab, Semicolon, Comma, or Space parameters of the OpenText method, it is easier to just use the Other and OtherChar parameters. The only trick is specifying a tab delimiter when calling OpenDelimitedFile. In order for this to work you can use the VB-defined constant vbTab.

The test data I used is a tab-delimited file containing a header row and a date field in the third column. An example of this file is shown in Figure 15.10. Because I would like the date field treated as a date, the TestOpenDelimitedFile procedure prepares the vFieldInfo parameter by using the Array function. Remember that the field info parameter requires an array of two-element arrays. That is why you see two calls to the Array function.

Figure 15.10

A tab-delimited text file

r tab delimited otders.TXT - notepad

sb®

File Edit

Format View Help

order#

5alespersdn Date

Product

.Amount

1

Hans En 10/10/2003

xl-dlx

J15S.

.50

2

Johnson 10A0/2003

xl-dux

J155.

.50

3

Madison 10A1/2003

mt-fus

J275.

.73

4

Smith 10/12/2003

MT-FQD

Î2S5,

,00

5

Thomas 10/14/2003

DD-FRS

sil 5.

.65

6

Jones 10/14/2003

dd-dlx

J235.

.50

The result of using OpenDelimitedFile on the sample data

LJ r J>0| EHtel - lab delimited orders,TXT

BBB

§1

Fils Edt

View Insert Format

Tools Data JMndow

Help

- 9 X

: _J

2J ti

j <& _ii

j ■ &

■•■ ÜM !

si ■ £ "

1 -iE

al

f. 1

a

B

c

D

E

F 1

e

H "Xs

1

1 '

Hs risen

10/1CÎ2003

xl-dlî;

$155.50

2

2 Johnson

10/1CIÎ2G03

XL-DUX

$155.50

3

3 Madison

i:;'"/.'rr

MTFRS

$275.75

4

4 Smith

10/12/2003

I^IT-FOD

$265.00

5

5 Thomas

10/14/2003

DD-FRS

$11565

s

6 J Jones

10/14(3003

DD-DLX

$235.60

7

B

V

H {

► M \tab delimited orders /

R

41

1 Ready

As you can see in Figure 15.11, the test is successful and the OpenDelimitedFile procedure successfully opens the orders file shown in Figure 15.10. Because I specified that it should start with the second row, the header row has been skipped.

For fixed-width files, the FieldInfo parameter of OpenText assumes paramount importance. If you don't provide the FieldInfo parameter, Excel opens the file with everything squeezed into the first column. Consequently, the OpenFixedWidthFile procedure shown in Listing 15.2 requires a parameter, vFieldInfo, which it merely passes to OpenText as the FieldInfo parameter. Notice that the OpenFixedWidthFile procedure only has three parameters versus six for OpenDelimitedFile. This is because you do not need to worry about specifying any delimiter-related information.

0 0

Post a comment