Writing to Text Files

You can write to a text file with the Write statement or the Print statement. Write produces a line of values separated by commas, and puts hash marks (#) around dates and quotes (") around strings, as shown in the example that follows. Print produces a line that is suitable to be printed, with the data arranged in columns with spaces between. You will first see how Write performs and look at Print a little later.

Say you have a spreadsheet like the one shown in Figure 11-1. You want to create a file containing some of the data. You can use code like the following:

Sub

WriteFile()

Dim dDate As Date

Dim sCustomer As String

Dim sProduct As String

Dim dPrice As Double

Dim sFName As String 'Path and name of text file

Dim iFNumber As Integer 'File number

Dim lRow As Long 'Row number in worksheet

sFName = "C:\VBA_Prog_Ref\Chapter12\JanSales.txt"

'Get an unused file number

iFNumber = FreeFile

'Create new file or overwrite existing file

Open sFName For Output As #iFNumber

lRow = 2

Do

'Read data from worksheet

With Sheet1

dDate = .Cells(lRow, 1)

sCustomer = .Cells(lRow, 2)

sProduct = .Cells(lRow, 4)

dPrice = .Cells(lRow, 6)

End With

'Write data to file

Write #iFNumber, dDate, sCustomer, sProduct, dPrice

'Address next row of worksheet

lRow = lRow + 1

'Loop until an empty cell is found

Loop Until IsEmpty(Sheet1.Cells(lRow, 1))

'Close the file

Close #iFNumber

End

Sub

i5:ii>ÜEI

A J B

Ç

D

■E

F

6

1

Date Customer

Stale

Product

Number Sold Pr

SB

îevenue !

2

Jan 01. 2006 Roberts

NSW

Oranges

903

15

13545

3~

Jan 01 2006 Roberts

TAS

Oranges

331

15

4965

4

Jan 02. 2006 Smith

QLD

Mangoes

299

20

5980

5

Jan 06 2006 Roberts

QLD

Oranges

612

15

9100

6

Jan 08. 2006 Roberts

VIC

Apples

907

125

11337.5

7

Jan 08 2006 Pradesh

TAS

Pears

107

18

1926

8

Jan 10 2006 Roberts

VIC

Apples

770

12.5

9625

Jan 14 2006 Smith

NT

Apples

223

12.5

2787.5

10

Jan 14. 2006 Smith

VIC

Oranges

132

15

1980

11

Jan 15. 2006 Pradesh

QLD

Oranges

669

15

10035

12

Jan 17. 2006 Roberts

NSW

Mangoes

381

20

17620

13

Jan 21 2006 Kee

SA

Pears

624

18

11232

14

Jan 22. 2006 Roberts

QLD

Mangoes

193

20

3860

15

Jan 23 2006 Smith

SA

Mangoes

256

20

5100

16

Jan 27. 2006 Kee

QLD

Mangoes

6

20

120

17

Jan 27 2006 Kee

VIC

Mangoes

311

20

6220

18

Jan 28. 2006 Roberts

NT

Oranges

9

15

135

19

Jan 20. 2006 Kee

TAS

Apples

706

12 5

8825

20

Jan 29. 2006 Kee

NT

Mangoes

441

20

8820

21

Jan 30 2006 Kee

WA

Oranges

936

15

Figure 11-1

FreeFile is used to get the next available file number, and the file is opened in Output mode so it can be written to. Use a Do...Loop to process each row of the worksheet until you find an empty cell in column A. The required data on each row is read into four variables, which are then written to the file. You must Close the file when you have finished using it, or it might not be completed properly and will be left open. You can use Notepad to view the file that's created, as shown in Figure 11-2.

JanSales.txi Notepad

File Edit Format View Help

!|*3<W6-01-01*, "Roberts", "Oranges",15 IaJ #2006-01-01#,"Roberts","oranges",15

#2006-01-02^, "smith", "MangoeS", 20

#2006-01-06fF, "Roberts", "oranges", 15 #20G6-01-08#,"Roberts","Apples",12.5 £2006-01-03#,"Pradesh","Pears",IB #2006-01-10#,"Roberts","Apples ,12.5 £2006-01-14 "smith", "Apples".12. 5 #2006-01-14t,"smi th","Oranges", 15 #2006-01-15^, "Pradesh", "oranges",15 #2006-01-17#,"Roberts","Mangoes",20 £2006-01-21it, "Kee", "Pears",15 #2006-01-22#, "Roberts". "Manooes ",20 £2006-01-23fi, "Smi th", "Mangoes", 20 #2006-01-27?, "Kee"."Mangoes",20 eV2006-01-Z7#, "Kee", "Mangoes", 20 #20Q6-01-2S#,"Roberts", oranges",Is #2006-01-2B#, "Kee", "Apples", 12. 5 #2006-01-29#,"Kee","Mangoes", 20 #2006-01-30#,"Kee","oranges",15

Figure 11-2

0 0

Post a comment