|< Day Day Up >|
The I/O term read really means to retrieve. When opening the file in Input, Output or Append mode, use the following read statements:
The easiest way to retrieve data is with the Input function using the form
where number specifies the number of characters to return and filenumber is any valid file handle. Input returns all the characters it reads, including carriage returns, linefeeds, and spaces.
Let's look at a simple procedure that uses Input to retrieve data from an exported table. To do so, you need to export the table first. Choose File, Export and export the Employees table as a text file (don't change any of the other default settings during the export). Then, enter the following procedure in a standard module (or use Chapter 20's example module):
Sub ReadInput(fil As String) 'Print data from passed file 'in Immediate window. Dim hFile As Long hFile = FreeFile Open fil For Input Access Read Shared As hFile Debug.Print Input(LOF(hFile), hFile) Close hFile End Sub
Run the following statement in the Immediate window to retrieve the file's entire contents, as shown in Figure 20.2:
Figure 20.2. Use Input to quickly grab an entire file's content.
Remember to update the actual path to the file accordingly. The LOF(hFile) component in the Input function specifies the file's length, and as a result, the function grabs the file's entire content.
The exported file, employees.txt, contains line breaks. The Export wizard inserted them when you exported the data and that's why the printed values in the Immediate window are so neatly arranged one employee, one line. If the file contains no line breaks, Input retrieves the file's contents as one long line, breaking only where forced to by the conventions of the container.
About EOF and LOF
The previous procedure introduced the LOF function. Anytime you actually need to do anything with a file's content, you'll probably use LOF or EOF.
The EOF function determines whether you've reached the end of the file. For the technically minded, the EOF function returns a Boolean value that indicates whether the current byte position is at the end of the file. What all this means is that EOF returns True if you've reached the end of the file and False if you haven't.
The current byte position refers to the current pointer in the file, for lack of a better definition. VBA sets this value to 0 when you first open a file positioning to the very first byte in the file. You might think of this value as representing the spot just before the first value in the first line. For the most part, a byte refers to one character, including spaces.
LOF returns the size of the open file, in bytes. In the previous example, LOF determines the amount of content retrieved by the Input function.
Using Line Input #
Like Input, the Line Input # statement reads data from an opened file, but Line Input # grabs the data one line at a time. This statement uses the form
Line Input #filenumber, varname
where filenumber is the file handle and varname is the variable to which the statement saves the retrieved data.
The following procedure retrieves the file's entire contents just as the previous Input procedure did:
Sub ReadLineInput(fil As String) 'Print data from passed file 'in Immediate window. Dim hFile As Long Dim strLine As String hFile = FreeFile Open fil For Input Access Read Shared As hFile Do Until EOF(hFile) Line Input #hFile, strLine Debug.Print strLine Loop Close hFile End Sub
In the Immediate window, run the following statement:
(Be sure to update path accordingly.) Instead of retrieving the file's contents as a whole as the Input example did, the Do Until loop retrieves the data line by line. If you remove the Do Until loop, the Debug.Print statement prints only the file's first line.
Using Input #
Input # is similar to Line Input #, but it's a little more flexible. This statement retrieves data and assigns the data to variables using the form
Input #filenumber, varlist
where varlist is a list of variables separated by commas. Input # saves each data element in the file to a separate variable. In addition, it deletes the quotation marks that surround text values and converts date strings to VBA dates.
The following procedure returns the same data, but in three separate variables strID, strFN, and strLN which the Debug.Print statement then concatenates:
Sub ReadInputPoundSign(fil As String) 'print data from passed file 'in Immediate window Dim hFile As Long Dim strID As String Dim strFN As String Dim strLN As String hFile = FreeFile Open fil For Input Access Read Shared As hFile Do Until EOF(hFile) Input #hFile, strID, strFN, strLN Debug.Print strID & vbTab & strFN & " " & strLN Loop Close hFile End Sub
Run the following statement in the Immediate window:
(Don't forget to update path.) The data is the same as the previous procedure's data, but is formatted slightly different as shown in Figure 20.3.
Figure 20.3. You can control the returned data by data elements using Input #.
|< Day Day Up >|