Figure

The result of using OpenFixedWidth-File on the sample

D 1 Hill ! V t-l - fine J Vll'l 0 r 1 ! ; ■ .S. ' > 1

EBB

L'^J Fib Edt Vtïïv Insert

Format Tools Data

tt^rdow

Help

-9x1

: -J

.-LI a

■j u

¿1 ■ ¿M ' g i

e - I ü3 y

A1

Order*

A

C ! D 1

E

F

e

_HX

1

|Order#-

[Salesperson—

Date- Product—

Arn c> unt

2

!

Hansen

10/10/2003 XL-DLX

il E5EC

3

2 Johnson

10/10/2003 XL-OLX

$155.50

"1

3 Madison

10/11 /2003 MT-FRS

$275.75

5

4 Smith

10/12H303 MT-FOD

$265.00

6

5-'Thorn as

30/14/2OT3 DD-FRS

$115.65

7

6 Jones

10/14/2003 DD-DUX

$235.50

B

V

Ii i

r m \ftKeii width orders /

Ut

—H

iü2

\ Ready

Text data that needs parsing

D Microsoft EKcel - Chapter 15 EnampleSrKls

ee®

I File Edit View Insert Format Tools Data Window Help

. S x|

. . 1 O ; 1 ¿1 ■■ jM 1 " Rüüly »til 310MB3

SIE-

« J

D1G - f,

A B C , D 1 E

1 F

g 1

H

1

Syni bol ,Dat e ,0 pen, Hagh, Law .Last .Volurn e .Open Inl ere st

1

MWZD3 .B3/2Q02 349.5,354,349,354,7353,23342

3

MWZ03 £/4/2002 352,352,351,351 3952 23549

J

MWZ03 &5/2D02 351.351.349.349335423283

5

MWZD3 M/2002 JD .347.347.347.3514.23147

6

MWZD3.W/2002.0,339,339.339.3011,23299

7

MWZ03 ,6« 0/2002,336,339,333,339,2036 21274

0

MWZ03 ß/t 1 /2D02 0,339,339 339 30G4.21042

3

M WZD3 2/2002 340,340,337,330.5 ,^216,21237

10

MWZD3£^13/3002337 5,338.33B33S.J851,21036

11

MWZ03.&/Î 4/2002,331,331,326,326,4634.22264

12

13

-

M 1

t m\ Sheet 1 / Reecl Prora feit Fde to column | <

¡IL.___Jj

11

9

J"

Text data parsed using Text to Columns

Figure 15.14

Text data parsed using Text to Columns

LJ MrrmnfrFHrel-rhftfïrpr :i;iI:->k'

E®E

lliälj Fib EiSt

Vbiw Insert Format

Tools Dota V

/vrdnv

g H

dp

-

5> x

: -J

_1 _! -J S -i 1 ■ ¿1

¿J I Recdv.iriäl üiaraei.

Br z «

Ê

A1

Symbol

A

B C 1

DIE

r~

Em

G I

H

_a

1

Symbol

Date Open

High Low

Last

Volume

Open Interest

1

MWZ03

&3ÖÜ02 349.S

m\

349

354

7353

23342

ZJ

3

MWZD3

6/4/2QD2 352

352'

351

351

3952

23549

J

MWZ03

6/5/2002 351

351

349

343

3654

23283

_1

S

WVZD3

6/6/2002 0

347'

347

347

3614

23147

6

MWZD3

t/,',jjj 1 01

339

339

339

3911

23299

7

MWZ03

6/10/2002 338

330

333

330

2036

21274

B

MWZ03

6/11 /20D2 0

333

339

339

3064

21042

3

MWZD3

6/12/2032 340

340

337

338.5

4215

21287

10

MWZ03

6/13/2WJ2 337 5

33a'

336

333

4851

21836

11

MWZD3

6/14/2002 331'

331 '

326

32G

4334

222S4

12

.13.

y

M <

h M \ Sheet 1 / Reed From Te>:t FJe ûïexï to Columns | i

■m, , ,

1

> 1

1 read/

The Range object has a TextToColumns method that allows you to do this programmatically. The syntax of TextToColumns is as follows.

rg.TextToColumns [Destination], [DataType], [TextQualifier], _ [ConsecutiveDelimiter], [Tab], [Semicolon], _ [Comma], [Space], [Other], [OtherChar], [FieldInfo], _ [DecimalSeparator], [ThousandsSeparator], _ [TrailingMinusNumbers]

So what do you think, shall we review all of these parameters (again)? We should probably move onward. Just in case you need a review, please refer back to the parameters of the OpenText method in the previous section entitled "Automatic Text Files."

The TextToColumns method does have one parameter that is specific to this function, the Destination parameter. Destination is an optional parameter that specifies the cell representing the upper-left cell of the parsed data. If the range passed to Destination is larger than a single cell, the upper-left cell of the range is used. By default, the destination is the upper-left cell of the Range object that the TextToColumns method operates on. Perhaps an example will make this more clear. Listing 15.3 demonstrates the use of the Destination parameter by converting a range of text to columns not once, but twice. The first time, it converts the text but places the results in a new range. The second time it converts the text but replaces the original text with the converted text.

Listing 15.3: TextToColumns Example

Sub TestTextToColumns() Dim rg As Range

Set rg = ThisWorkbook.Worksheets("Text to Columns"). Range("a20").CurrentRegion

Converts text to columns but leaves the original text untouched

CSVTextToColumns rg, rg.Offset(15, 0)

' Converts text to columns. ' Replaces original text with converted text. CSVTextToColumns rg

Set rg = Nothing End Sub

' Converts text to columns assuming the text ' to be converted is comma delimited.

Sub CSVTextToCo1umns(rg As Range, Optional rgDestination As Range) If IsMissing(rgDestination) Or rgDestination Is Nothing Then rg.TextToColumns , xlDelimited, , , , , True

Else rg.TextToColumns rgDestination, xlDelimited, , , , , True End If End Sub

One interesting thing in this listing is the optional rgDestination parameter used by the CSVTextToColumns procedure. You may wonder why you need to check rgDestination against Nothing in addition to seeing if it is missing in the If.. .Then statement. Shouldn't the use of IsMissing be sufficient? IsMissing is only accurate when you use it on Variant variables. On variables using other data types, IsMissing always returns false. By making rgDestination optional, the only thing I have done is made it easier to call the procedure if you do not want to specify a destination. Another option is to make the rgDestination a Variant (which has the capability to represent range objects) rather than a Range. Figure 15.15 shows the results of the TextTextToColumns procedure.

Figure 15.15

The results of TestTextTo-Columns

E3 Microsoft. Excel - Chapter 15 ExdiiifiltüJiU

Edt £ew insert Farmat laols ßata ^ndow tiefc

E3 Microsoft. Excel - Chapter 15 ExdiiifiltüJiU

Edt £ew insert Farmat laols ßata ^ndow tiefc

D36

-

fi> 354

"A

B

C

D

E

0

I I I.

T

19 i

20 Symbol

Date

Oper:

High

LOW

Last

Volume

Open interest

21 MVV203

6/3/2002

349.5

354

349

354

7353

23342

22 MWZ03

6/4/2002

352

352

35t

351

3952

23549

33 MVVZQ3

6/5/2002

351

351

349

349

3854

23283

24 MWZ03

6/6/2002

0

347

347

347

3614

23147

25 MWZ03

6/7/2002

0

339

339

339

3911

23299

26 MWZ03

6f10/2002

338

339

338

339

2036

21274

27 MWZ03

6/1 1/2002

0

339

339

339

3064

21042

28MWZ03

6/12/2002

340

340

337

338 5

4216

21287

29 MWZÛ3

6/13/2002

337.5

338

336

338

4851

21836

30 MWZÛ3

6/14/2002

331

331

326

326

4334

22284

■jg. 1

35 ' Symbol

Date

Open

Hiqh

LOW

Last

Volume

Open inlerest

36l MW2D3

6/3/2002

343.5

354

349

354

7353

23342

37 MWZ03

6/4/2002

352

332

351

351

3952

23549

38 MWZ03

6/5/2002

351

351

349

349

3854

23283

39 MWZÛ3

6/6/2002

0

347

347

347

3614

23147

40 MWZ03

6/7/2002

0

339

339

339

3011

23299

MWZ03

6/10/2002

338

339

338

339

2036

21274

42 MW203

en 1/2002

0

339

339

339

3064

21042

43 MWZÖ3

6/12/2 00 2

340

340

337

338.5

42Ï6

21287

44 UWZ03

6/13/2002

337,5

338

336

338

4851

21836

45MWZ03

6/14/2002

331

331

326

326

4334

22284

46

47

v

H i * H \ £heeil / Read Frcm Text

File \ïeKt to Columns/j<

■mh)

> 1

Ready

0 0

Post a comment