Reading Text Files

You can read text files with the Input statement or the Line Input statement. Input expects data like that produced by Write and reads the data into a list of variables. Line Input reads the whole line of data as a single string into a single variable. The following code reads the JanSales.txt file and inserts the data into a worksheet:

Sub

ReadFile()

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

'Prepare file for reading

Open sFName For Input As #iFNumber

Sheet2.Cells.Clear

lRow = 2

Do

'Read data from file

Input #iFNumber, dDate, sCustomer, sProduct, dPrice

'Write data to worksheet

With Sheet2

.Cells(lRow, 1) = dDate

.Cells(lRow, 2) = sCustomer

.Cells(lRow, 3) = sProduct

.Cells(lRow, 4) = dPrice

End With

'Address next row of worksheet

lRow = lRow + 1

'Loop until end of file

Loop Until EOF(iFNumber)

'Close the file

Close #iFNumber

End

Sub

The Do...Loop processes each line in the file until the EOF function detects that the end of file has been reached. The Input statement reads each line of the file into four variables. It is important to have variables of the correct type to match the data, or unexpected results can occur.

If you just want to record data for your own purposes or exchange it with users of the same systems, Write and Input would probably be all you need. Unfortunately, other systems can use formats that are not quite the same. They can use different separator characters and different delimiter characters, or none. Some work in fixed-sized fields for different variables. You need to find more flexible ways to produce these files and read them.

Was this article helpful?

0 0

Post a comment