Using sequential access

 1999-12-17    File access    0    224

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 filepath For mode [Access accesstype] [Lock locktype] As [#] filenumber [Len = recordlength]

' filepath:
' must be a full filepath to the file you want to open
' If the file specified doesn't exist, it will be created when opening a file for Output, Append, Random or Binary access.

' Open modes:
' Input: used for reading content from text files (sequential access)
' Output: used for writing content to text files (sequential access)
' Append: used for adding content at the end of text files, reading is also allowed (sequential access)
' Random: used for reading/writing records of equal length
' Binary: used for reading/writing records of variable length

' Access types, specifies the operations permitted on the open file
' Access Read
' Access Write
' Access Read Write

' Lock types, specifies the operations restricted on the open file by other processes:
' Lock Shared: other processes can read from and write to the file (default)
' Lock Read: other processes can not read from the file
' Lock Write: other processes can not write to the file
' Lock Read Write: other processes can not read from or write to the file

' filenumber:
' use the FreeFile function to return an integer number for the next available file number (1 - 511)

' recordlenght:
' an integer number <= 32,767 (bytes)
' for files opened for sequential access, this number is the count of characters to read in each individual read operation.
' for files opened for random access, this number is the record length.
' for files opened for sequential access, this number is the number of characters buffered.
' for files opened for binary access, this number is ignored.

' examples: open file for reading, the file must exist
    Dim f As Integer
    f = FreeFile ' get the next available file number (1 - 511)
    Open "C:\Folder\Filename.txt" For Input As #f ' open textfile for reading
    Open "C:\Folder\Filename.txt" For Input Access Read As #f ' open textfile for reading in read only mode
    Open "C:\Folder\Filename.txt" For Input Access Read Lock Write As #f ' open textfile for reading in read only mode, prevent other processes from writing to the file

' examples: open file for writing, the file will be created if it doesn't exist
    Open "C:\Folder\Filename.txt" For Output As #f ' open textfile for writing
    Open "C:\Folder\Filename.txt" For Output Access Write Lock Read Write As #f ' open textfile for writing, prevent other processes from accessing the file
    Open "C:\Folder\Logfile.txt" For Append As #f ' open textfile for adding content at the end of the file
    Open "C:\Folder\Logfile.txt" For Append Access Write Lock Read Write As #f ' open textfile for adding content at the end of the file, prevent other processes from accessing the file


You must Close all files you Open when you are finished reading/writing and before reopening the files for another type of operation:
Close f ' close the file associated with the file number
Close 1, 2, 3 ' close multiple files associated with the file numbers
Close ' close all opened files
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

Use the Output or Append statements to write data to a text file.
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.

To write the contents of variables in a sequential text file you can use either the Print # or the 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]

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 Example_SequentialAccess_Write()
    Dim f As Integer
    
    ' open file for writing, the file will be created if it doesn't exist
    f = FreeFile ' get the next available file number (1 - 511)
    Open "C:\Folder\Filename.txt" For Output As #f ' open textfile for writing
    'Open "C:\Folder\Filename.txt" For Output Access Write Lock Read Write As #f ' open textfile for writing, prevent other processes from accessing the file
    'Open "C:\Folder\Logfile.txt" For Append As #f ' open textfile for adding content at the end of the file
    'Open "C:\Folder\Logfile.txt" For Append Access Write Lock Read Write As #f ' open textfile for adding content at the end of the file, prevent other processes from accessing the file
    
    ' write content to text file using Print (unformatted output)
    Dim strContent As String
    strContent = "This is some content"
    Print #f, strContent ' writes a line with unformatted output to the textfile
    Print #f, 1234.56 ' writes a line with number using the decimal separator from the regional settings
    Print #f, Date ' writes a line with date using the format from the regional settings
    Print #f, True ' writes a line with boolean value as True or False
    Print #f, ' writes a blank line
    Print #f, strContent, 1234.56, Date, False ' writes a line with unformatted fixed width values:     This is some content         1234,56      12.08.2021    False
    Print #f, strContent, 451234.56, Date, False ' writes a line with unformatted XXX-separated values: This is some content         451234,56    12.08.2021    False
    Print #f, strContent, 34.56, Date, False ' writes a line with unformatted XXX-separated values:     This is some content         34,56        12.08.2021    False
    Print #f, ' writes a blank line
    
    ' write content to text file using Write (formatted output)
    Write #f, strContent ' writes a line with formatted output to the textfile (double quotes around each item, separated by comma)
    Write #f, 1234.56 ' writes a line with number using a dot as the decimal separator
    Write #f, Date ' writes a line with date as #yyyy-mm-dd#
    Write #f, True ' writes a line with boolean value as #TRUE" or #FALSE#
    Write #f, ' writes a blank line
    Write #f, strContent, 1234.56, Date, False ' writes a line with formatted comma-separated values:   "This is some content",1234.56,#yyyy-mm-dd#,#FALSE#
    Write #f, strContent, 451234.56, Date, False ' writes a line with formatted comma-separated values: "This is some content",451234.56,#yyyy-mm-dd#,#FALSE#
    Write #f, strContent, 34.56, Date, False ' writes a line with formatted comma-separated values:     "This is some content",34.56,#yyyy-mm-dd#,#FALSE#
    Write #f, ' writes a blank line
    
    Close f ' close the file associated with the file number
    'Close 1, 2, 3 ' close multiple files associated with the file numbers
    'Close ' close all opened files
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 can use Input # to read each line of the file into a list of variables.

Sub Example_SequentialAccess_ReadContent()
' read all or some content
    Dim f As Integer
    
    ' open file for reading
    f = FreeFile ' get the next available file number (1 - 511)
    Open "C:\Folder\Filename.txt" For Input As #f ' open textfile for reading
    'Open "C:\Folder\Filename.txt" For Input Access Read As #f ' open textfile for reading in read only mode
    'Open "C:\Folder\Filename.txt" For Input Access Read Lock Write As #f ' open textfile for reading in read only mode, prevent other processes from writing to the file
    
    ' read content from a text file
    Dim strContent As String
    If Not EOF(f) Then
        strContent = Input(LOF(f), #f) ' reads all content from a textfile
        'strContent = Input(100, #f) ' reads the first 100 characters from a textfile
        Debug.Print strContent
    End If
    Close f ' close the file associated with the file number
End Sub

Sub Example_SequentialAccess_ReadLines1()
' read each line
    Dim f As Integer
    
    ' open file for reading
    f = FreeFile ' get the next available file number (1 - 511)
    Open "C:\Folder\Filename.txt" For Input As #f ' open textfile for reading
    'Open "C:\Folder\Filename.txt" For Input Access Read As #f ' open textfile for reading in read only mode
    'Open "C:\Folder\Filename.txt" For Input Access Read Lock Write As #f ' open textfile for reading in read only mode, prevent other processes from writing to the file
    
    ' read lines from a text file
    Dim strLine As String
    Do While Not EOF(f)
        Line Input #f, strLine ' read a line from a textfile
        Debug.Print strLine ' do something with the string
    Loop
    Close f ' close the file associated with the file number
End Sub

Sub Example_SequentialAccess_ReadLines2()
' read each line with comma-separated values
    Dim f As Integer
    
    ' open file for reading
    f = FreeFile ' get the next available file number (1 - 511)
    Open "C:\Folder\Filename.txt" For Input As #f ' open textfile for reading
    'Open "C:\Folder\Filename.txt" For Input Access Read As #f ' open textfile for reading in read only mode
    'Open "C:\Folder\Filename.txt" For Input Access Read Lock Write As #f ' open textfile for reading in read only mode, prevent other processes from writing to the file
    
    Dim varID As Variant, strName As String, varValue As Variant
    f = FreeFile ' get the next available file number (1 - 511)
    Open "C:\Folder\Filename.txt" For Input As #f ' open textfile for reading
    Do While Not EOF(f)
        Input #f, varID, strName, varValue ' read items from a line in the textfile
        Debug.Print varID, strName, varValue ' do something with the variables
    Loop
    Close f ' close the file associated with the file number
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 are 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)



  • Random access, read/write records of fixed length.
  • Binary access, read/write records of variable length or for files with no general format, this requires that you have exact knowledge on how the data is stored in the file in order to retrieve any information.