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.
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 filesWhen 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 SubSee 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:
- Open the file for Input and read the information into one ore more variables.
- Close the file.
- Modify the information stored in the variables.
- Open the file for Output and write the modified information back to the file.
- Close the file.
Another way of modifying a sequential access file that contains a large amount of information:
- Open the file for Input.
- Create a new file for Output.
- Loop through the original file reading a line at the time into a variable.
- Modify the information stored in the variable.
- Write the modified information to the new file.
- Close both files after you have finished looping through the old file.
- Delete the original file (Kill oldfilename)
- 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.