Using sequential access

 1999-12-17    File access    0    59

Sequential access (Input, Output, and Append) is used for writing and reading text files, such as error logs and reports, e.g. *.txt files, *.ini files and *.csv files. Sequential access files are easy to create and manipulate with text editors,  most applications can read and write files created with sequential access, and they are easy to process with VBA. Sequential access files are not well suited for storing large amounts of data, they requires more storage space than the other file access types, and you can only read or write to a file opened for sequential access, you can't do both at the same time.

Opening files for sequential access

You can open a file for sequential access in three ways:

  • Input, used to read characters from the file, requires that the file already exists.
  • Output, used to write characters to the file, will create the file if it doesn't exist.
  • Append; used to write characters to the end of the file, will create the file if it doesn't exist.
To open a file for sequential access from VBA you use the Open statement:

Open filename For [Input, Output, Append] As filenumber
You must Close all files you Open before reopening the files for another type of operation:

Close filenumber ' this will close a specific file
Close 1, 2, 3 ' this will close file numbers 1, 2 and 3
Close ' this will close all all active files opened by the Open statement
When you close files that were opened for Output or Append, the final buffer of output is written to the operating system buffer for that file.

Writing to files opened for sequential access

To store the contents of variables in a sequential text file, open it for sequential access and then use either the Print # or Write # statement to write the data to the file.

The Print # statement writes display-formatted data (or space-formatted data) to a sequential file and uses the following syntax:  Print # filenumber,[outputlist] Here is an example macro:

Sub PrintToTextFile()
Dim FileNum As Integer, i As Long
    If Dir("C:\FolderName\Textfile.txt") <> "" Then
        ' deletes the file if it exists
        Kill "C:\FolderName\Textfile.txt"
    End If
    FileNum = FreeFile ' next free filenumber
    Open "C:\FolderName\Textfile.txt" For Output As #FileNum 
    ' creates the new file
    'Open "C:\FolderName\Textfile.txt" For Append As #FileNum 
    ' appends the input to an existing file
    ' write to the textfile
    For i = 1 To 100
        Print #FileNum, "This is line number " & 
            i & " " & Date & " " & i
        If i Mod 10 = 0 Then Print #FileNum, 
        ' adds a blank line for every 10 lines
    Next i
    Close #FileNum ' close the file
End Sub
Both Output and Append will create a new file if the file doesn't exist. Append will add the new data to the end of an existing file.

The Write # statement writes comma-delimited formatted data to the text file and uses the following syntax: Write#filenumber[,outputlist] When you write to the file using Write #, string fields are surrounded with double quotation marks and date fields are surrounded with #'s. In this respect, Write # is a companion to Input #. Here is an example macro:

Sub WriteToTextFile()
Dim FileNum As Integer, i As Long
    If Dir("C:\FolderName\Textfile.txt") <> "" Then
        ' deletes the file if it exists
        Kill "C:\FolderName\Textfile.txt"
    End If
    FileNum = FreeFile ' next free filenumber
    Open "C:\FolderName\Textfile.txt" For Output As #FileNum 
    ' creates the new file
    'Open "C:\FolderName\Textfile.txt" For Append As #FileNum 
    ' appends the input to an existing file
    ' write to the textfile
    For i = 1 To 100
        Write #FileNum, "This is line number " & i, Date, i
    Next i
    Close #FileNum ' close the file
End Sub
See online help for more detailed information about the Print- and Write-statements.

Reading from files opened for sequential access

To read the contents of a text file, you open the file for input, then you use Line Input # or Input # to read the contents of the text file. Use Line Input # when you need to read a file one line at a time. With delimited files such as tab or comma separated values, you use Input # to read each line of the file into a list of variables.

Reading a text file line by line

This example macro shows how you can read a textfile line by line:

Sub ReadLineFromPrintedTextFile()
Dim InputString As String, FileNum As Integer
    FileNum = FreeFile ' next free filenumber
    Open "C:\FolderName\Textfile.txt" For Input As #FileNum
    While Not EOF(FileNum)
        Line Input #FileNum, InputString ' read a line from the textfile
        Debug.Print InputString ' do something with the string
    Wend
    Close #FileNum
End Sub

Reading a delimited text file

Standard string or numeric data is assigned to variables as they appear in the text file. Delimiting commas or blank lines within the file are returned as Empty. Double quotation marks that surround each field in the input data are ignored and fields surround with "#"'s are interpreted as dates. The next line shows a example of a delimited text file content:

"Doe", "John", 30, "123 Main Street", "London"
This example macro shows how you can read information from a delimited textfile:

Sub ReadLineFromWrittenTextFile()
Dim InputString As String, FileNum As Integer
Dim TextLine As String, MyDate As Date, IntegerValue As Integer
    FileNum = FreeFile ' next free filenumber
    Open "C:\FolderName\Textfile.txt" For Input As #FileNum
    While Not EOF(FileNum)
        Input #FileNum, InputString, MyDate, IntegerValue
        Debug.Print InputString, MyDate, IntegerValue
        ' do something with the variables
    Wend
    Close FileNum
End Sub

Modifying information stored in sequential access files

If you open an existing file for Output you are replacing the contents of the file when you write to it. In order to append data at the end of an existing file you must open the file for Append. It's not possible to modify the information stored in a file opened for sequential access, but here is a couple of solutions on how you can solve this problem:

  1. Open the file for Input and read the information into one ore more variables.
  2. Close the file.
  3. Modify the information stored in the variables.
  4. Open the file for Output and write the modified information back to the file.
  5. Close the file.
Another way of modifying a sequential access file that contains a large amount of information:

  1. Open the file for Input.
  2. Create a new file for Output.
  3. Loop through the original file reading a line at the time into a variable.
  4. Modify the information stored in the variable.
  5. Write the modified information to the new file.
  6. Close both files after you have finished looping through the old file.
  7. Delete the original file (Kill oldfilename)
  8. Rename the new file with the same name as the original file
    (Name newfilename As oldfilename)


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.