About EDC
     Contact
     Feedback

VBA programming
     Add-Ins
     ADO/DAO
     Charts
     Dialogs/UserForms
     Display/Statusbar
     Events
     Files/Folders
     Import/Export
     Menus/Toolbars
     Misc. tips
     OLE Automation
     Operating System
     Printing
     Private Profiles
     Sound
     VBA File Access
     Visual Basic Editor
     Workbooks
     Worksheets

Worksheet Functions
     Adding
     Counting
     Date
     Lookup
     Misc. Functions
     Time

Download

Help / Support

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

Using sequential access

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.

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 a 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

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 a 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"

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.
1. Modify the information stored in the variable.
2. Write the modified information to the new file.
4. Close both files after you have finished looping through the old file.
5. Delete the original file (Kill oldfilename)
6. Rename the new file with the same name as the original file
   (Name newfilename As oldfilename)

Document last updated 1999-12-17 12:48:50      Printerfriendly version