Writing to Text Files Using Print

Print enables you to write text files in any format; you just have to do a bit more work. To see the effect of using Print instead of Write, change the Write statement in the WriteFile sub as follows:

Print #iFNumber, dDate, sCustomer, sProduct, dPrice

The output looks like that in Figure 11-3.

JanSales.txr

Notepad

313®

File Edit Format View

Help

it oi/zooe

Roberts

oranges

15

1/01/2005

Roberts

oranges

15

2/"01/20 06

Smith

Mangoes

20

6/01/2006

Roberts

oranges

15

8/01/2006

Roberts

Apples

12

5

S/ 01/2006

Pradesh

Pears

IS

10/01/2006

Roberts

Apples

12

5

14/01■2006

snri til

Apples

12

5

14/01/2006

snri th

oranges

15

15/01/2006

Pradesh

oranges

15

17/01/2006

Roberus

Mangoes

20

21/01 -'2006

Kee

Pears

IE

22/01/2006

Roberts

Mangoes

20

23/01/2006

smith

Mangoes

20

27/01/2006

Kee

Mangoes

20

27'01/2006

Kee

Mangoes

20

28/01-2006

Roberts

oranges

15

23/01/2006

Kee

Apples

12

5

29/01/2006

Kee

Mangoes

20

30/01 ■■'2006

Kee

oranges

15

Figure 11-3

If you want to read data in this format, you can read each line of the file using the Line Input statement. You then need to have code to parse out the data. Taking a hint from Write, you might want to use a separator character, but you want to be able to use any character that does not appear in the data. You might also introduce some flexibility with the characters used to delimit items. The following code shows how you can assemble your own strings and write them to a file. Code that is specific to WriteFile is highlighted:

Sub WriteStrings()

Dim sLine As String

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\JanSalesStrings.txt"

'Get an unused file number iFNumber = FreeFile

'Create new file or overwrite existing file Open sFName For Output As #iFNumber lRow = 2

'Read data from worksheet With Sheetl

sLine =

= Format(.

.Cells(lRow, 1), "yyyy-mmm-dd") & ";"

sLine =

= sLine &

.Cells(lRow, 2) & ";"

sLine =

-- sLine &

.Cells(lRow, 4) & ";"

sLine =

-- sLine &

Format(.Cells(lRow, 6), "0.00")

End With

End With

'Write data to file

Print #iFNumber, sLine

'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

The code assembles each line of the file in a variable sLine. For data other than strings, it uses the Format function to convert the data to a string. A semicolon is used as a separator. The Print statement writes the string to the file. The result is shown in Figure 11-4.

JanSalesStrings.ixr Notepad

•allgjitl

File Edit Forma! View Help

20062006200620062006200620062006200620062006200620062006200620062006200620062006-

:an-01 Jan-01 Jan-02 0an-06 üan-03 Jan-OS oan-io ■j an-14 :an-14 Jan-15 Jan-17 oan-21 ■Han-22 Oan-23 Oan-27 Jan-27 Jan-2S ■Jan-2E ■J an-29 0an-30

Roberts;oranges;15. 00 Roberts; oranges; 15. 00 smith; Mangoes; 20. 00 Roberts; oranges; 15. 00 Roberts;Apples;12.50 Pradesh; Pears; 13. 00 Roberts;Apples;12.50 smith; Apples;12. 50 smitli; oranges; 15. 00 Pradesh; oranges; 15. 00 Roberts;Mangoes;20. 00 Kee:Pears;18. 00 Roberts;Mangoes ; 20. 00 smith; Mangoes; 20. 00 Kee;Mangoes;20.00 Kee;Mangoes;20. 00 Roberts; oranges; 15. 00 Kee; Apples;12.50 Kee;Mangoes;20.00 K.ee: oranges; 15. 00

Figure 11-4

0 0

Post a comment