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
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 | ||||||||||||
Post a comment