ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

 

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

User comments:
Ole P. from Norway wrote (2006-07-21 16:44:03 CET):
Re: Without a carriage return
You will have to work around this limitation by using a string variable to build up your line content until it is ready to be written to the text file as a complete line, with a carriage return at the end.

E.g. like this:
For r = 1 To 10
strline = strline & Range("A" & r).Value
Next r
Print #f, strline
KF wrote (2006-07-21 16:09:59 CET):
Without a carriage return
I need to write a string to the text file WITHOUT the carriage return at the end of it - any ideas?
Ole P. from Norway wrote (2006-06-28 12:38:02 CET):
Re: Writing Tab Spaces
You can create tab delimited content like this:
Print #FileNum, strLastName & vbTab & strFirstName & vbTab & strAddress & vbTab & strZip & vbTab & strCountry
alan from UK wrote (2006-06-28 09:11:59 CET):
Writing Tab Spaces
Hi,
Is there any way to write a text file so it is tab space delimited?
Thanks
Alan
Ole P. from Norway wrote (2006-06-23 23:06:10 CET):
Re: What if you have quotes in your field?
You can read the input using the syntax from the "plain" text file example and add your own parsing instructions, e.g. like this:

Line Input #FileNum, InputString
' split input into individual items using comma as separator
varItems = Split(InputString, ",", -1, vbBinaryCompare)
' remove first and last character from each item ("-chars)
For i = 0 to UBound(varItems)
varItems(0) = Mid$(varItems(i), 2, Len(varItems(i)) - 2)
Next i

Then you can do want you want with each item (arrItem(0) - arrItem(2)) before reading the next line from the text file.
Jesse from Hamilton, Bermuda wrote (2006-06-23 15:59:49 CET):
What if you have quotes in your field?
Hi, Great examples. Very clear and solved my problem (almost).

I have a line that looks like this:
749291,"MARC HQ & BUSH BUS DIVISION KNOWN AS ""WA","1501"
There are 3 fields I want to input to an array.
749291 in the first element
MARC HQ & BUSH BUS DIVISION KNOWN AS ""WA in the second
and 1501 in the third.

When I import, I think the quotes in front of WA act as a closing and new opening quote.
How can get this line to import the way I want.
The textfiel is generated by exporting from Access.
I use this textfile to make a new with some modifications.

Thank you.
gapostolopoulos@gmail.com from Athens, Greece wrote (2006-05-25 09:13:01 CET):
helped a lot!
Thanks for your advice,
I was playing around trying to process text files in Excel,
you helped me save a lot of time and have peace of mind :-D

George
Rachel Ravitsky from Israel, Rehovot wrote (2006-04-08 11:33:23 CET):
Reading Writing Sequential Files via VBA in Excel
Dear Mr Erlandsen,
Your explantions as well as your good will and generoucity
putting your wisdome to the use of everybody are superb
I copied the above macro and will make lots of use of it
CM wrote (2006-03-29 03:08:35 CET):
Lost...
At one point I thought I had grasped the concept of sequential access files but more and more I'm realising how I'm incredibly lost I am!
tina from philippines wrote (2005-08-03 04:05:19 CET):
I have a question... how can i save the variables in an array? please see my comments.. ineed help
I wanted to save the variables value (from the sequential reading of file) into an array then output those array's value in the excel worksheet, specifying what rows and columns... thanks.

 

 
Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2024    Ole P. Erlandsen   All rights reserved
E-mail Contact Address