Working with Sequential Files

The hard drive of your computer contains hundreds of sequential files. Configuration files, error logs, HTML files, and all sorts of plain text files are all sequential files. These files are stored on disk as a sequence of characters. The beginning of a new text line is indicated by two special characters. One is called the carriage return and the other line feed. When you work with sequential files, you start at the beginning of the file and move forward character by character, line by line, until you encounter the end of the file. Sequential access files can be easily opened and manipulated by just about any text editor.

Tip 8-4: What is a Sequential File?

A sequential file is one in which the records must be accessed in the order they occur in the file. This means that before you can access the third record, you must first access record number 1 and then record number 2.

Tip 8-5: Opening Files with the Open Statement

When you use sequential access to open a file for input, the file must already exist.

Reading Data Stored in Sequential Files

Let's take one of the sequential files that is already present on your computer and read its contents with VBA straight from the Microsoft Excel Visual Basic Editor window. You can read the Autoexec.bat file or any other text file that you want. To read data from a file, you must first open the file with the Open statement. Here's the general syntax of this statement, followed by an explanation of each component:

Open pathname For mode [Access access][lock] As [#]filenumber [Len=reclength]

The Open statement has three required arguments. They are: pathname, mode, and filenumber. In the syntax shown above, these arguments are preceded by keywords that appear in bold.

■ Pathname is the name of the file you want to open.

■ Pathname may include the name of a drive and folder.

■ Mode is a keyword that determines how the file was opened. Sequential files can be opened in one of the following modes: Input, Output, or Append. Use Input to read the file, Output to write to a file by overwriting any existing file, and Append to write to a file by appending any existing information.

■ Access is a keyword that determines the file's read and write permissions. Access can be: Shared, Lock Read, Lock Write, or Lock Read Write.

■ Lock determines which file operations are allowed for other processes. For example, if a file is open in a network environment, lock determines how other people can access it. The following lock keywords can be used: Read, Write, or Read Write.

■ Filenumber is a number from 1 to 511. This number is used to refer to the file in subsequent operations. You can obtain a unique file number by using the Visual Basic built-in FreeFile function.

■ The last element of the Open statement, reclength, specifies the total number of characters in the sequential files or the size of the record for random access files.

Taking the preceding into consideration, to open C:\Autoexec.bat or any other sequential file in order to read its data, you should use the following instruction:

Open "C:\Autoexec.bat" For Input As #1

If a file is opened for input, it can only be read from. After you open a sequential file, you can read its contents with the following statements: Line Input # or Input # or by using the Input function.

Reading a File Line by Line

To read the contents of the Autoexec.bat or any other sequential file line by line, use the following Line Input # statement:

Line Input #filenumber, variableName

#filenumber is the file number that was used in the process of opening the file with the Open statement. variableName is a String or Variant variable that will store the line being read.

The statement Line Input # reads a single line in an open sequential file and stores it in a variable. Bear in mind that the Line Input # statement reads the sequential file one character at a time, until it encounters a carriage return (Chr(13)) or a carriage return-linefeed sequence (Chr(13) & Chr(10)). These characters are omitted from the text retrieved in the reading process.

The ReadMe procedure that follows demonstrates how you can use the Open and Line Input# statements to read the contents of the Autoexec.bat file line by line. Try to apply the same method for reading other sequential files.

1. Insert a new module into the current project and rename it SeqFiles.

2. Enter the ReadMe procedure shown below:

Sub ReadMe()

Dim rLine As String

Open "C:\Autoexec.bat" For Input As #1 ' stay inside the loop until the end of file is reached Do While Not EOF(1)

Line Input #1, rLine

MsgBox "Line " & i & " in Autoexec.bat reads: " _ & Chr(13) & Chr(13) & rLine i = i + 1


MsgBox i & " lines were read." Close #1 End Sub

3. Execute the procedure step by step by pressing F8.

The ReadMe procedure opens the Autoexec.bat file in the Input mode as file number 1 in order to read its contents. The Do.. .While loop tells Visual Basic to execute the statements inside the loop until the end of the file has been reached. The end of the file is determined by the result of the EOF function.

The EOF function returns a logical value of true if the next character to be read is past the end of the file. Notice that the EOF function requires one argument—the number of the open file you want to check. This is the same number that has been used previously by the Open statement. Use the EOF function to ensure that Visual Basic doesn't read past the end of the file.

The Line Input # statement stores each line's contents in the variable rLine. Next, a message is displayed that shows the line number and its contents. Then, VBA increases the line counter by one and begins reading the next line in the open file if the result of the EOF function is false (the end of the file has not been reached). Visual Basic exits the Do.While loop when the result of the EOF function is true. Before VBA ends the procedure, two more statements are executed. A message is displayed with the total number of lines that have been read. The last statement closes the open file.

Reading Characters from Sequential Files

Suppose that your procedure needs to check how many colons appear in the Autoexec.bat file. Instead of reading entire lines, you can use the Input function to return the specified number of characters. Next, the If statement can be used to compare the obtained character against the one you are looking for. Before you write a procedure that does this, let's review the syntax of the Input function:

Input(number, [#]filenumber)

Both arguments of the Input function are required. number specifies the number of characters you want to read, and filenumber is the same number that the Open statement had used to open the file. The Input function returns all the characters being read, including commas, carriage returns, end of file markers, quotes, and leading spaces.

1. In the SeqFile module, enter the Colons procedure:

Sub Colons()

Dim counter As Integer Dim char As String counter = 0

Open "C:\Autoexec.bat" For Input As #1 Do While Not EOF(1) char = Input(1, #1) If char = ":" Then counter = counter + 1 End If


MsgBox "Characters found: " & counter


MsgBox "The specified character has not been found."

End If

Close #1 End Sub

2. Execute the procedure step by step.

3. Replace the colon character with any other character you'd like to find and execute the procedure again.

The Input function allows you to return any character from the sequential file. And if you use the Visual Basic function called LOF as the first argument of the Input function, you'll be able to quickly read the contents of the sequential file without having to loop through the entire file. The LOF function returns the number of bytes in a file. Each byte corresponds to one character in a text file. The ReadAll procedure shows how to read the contents of the System.ini file to the Immediate window:

Sub ReadAll()

Dim all As String

Open "C:\WINNT\System.ini.bat" For Input As #1 all = Input(LOF(1), #1) Debug.Print all Close #1 End Sub

Instead of printing the file contents to the Immediate window, you can read it into a text box placed in a worksheet (see Figure 8-2). The procedure that does this is shown here:

Sub WriteToTextBox()

Dim mysheet As Worksheet Set mysheet = ActiveWorkbook.Worksheets(l) On Error GoTo CloseFile Open "C:\WINNT\System.ini" For Input As #1 mysheet.Shapes(1).Select Selection.Characters.Text = Input(LOF(1), #1) CloseFile: Close #1 End Sub

Before you run the above procedure, draw a text box on Sheet1 in the Chap08.xls workbook. Notice that the statement On Error GoTo CloseFile activates error trapping. If an error occurs during the execution of a line of the procedure, the program will jump to the error-handling routine that follows the CloseFile label. The statement Close #1 will be executed, whether or not the program encounters an error.

Figure 8-2:

The contents of the System.ini file are displayed in a text box placed in an Excel worksheet.

Reading Delimited Text Files

In some text files (files usually saved in CSVV TXT, or PRN format), data entered on each line of text is separated (or delimited) with a comma, tab, or space character. These types of files can be read faster with the Input # statement rather than the Line Input # statement introduced in the preceding sections. The Input # statement allows you to read data from an open file into several variables. This function looks like the following:

Input #filenumber, variablelist filenumber is the same file number that was opened with the Open statement. variablelist is a comma-separated list of variables that you will want to use to store the data being read. You can't use arrays or object variables. You may, however, use a user-defined variable (this type of variable is explained later in this chapter).

An example of a sequential file with comma-delimited values is shown below:




To read text formatted in this way, you must specify one variable for each item of data: last name, first name, and age.

1. Open a new workbook and enter the data shown below:
















2. Save the file as C:\Winners in the CSV format (comma-delimited). Excel will display a message that the selected file type does not







1 2

; for 16-bit app supports


j 4

[drive rs]n wave=nnmitrv.dllO


limer^timer. djvD

10 11 12



woafonl=dosapp.FOND £ GAflQWOAP ON=EGA30 WOA. F 0 NO





M 4

t ¡7k Sheet I/sheet* / jheetJ ] \ < |


support workbooks that contain multiple sheets. Click OK to save only the current sheet.

3. Enter the Winners procedure shown below: Sub Winners()

Dim lname As String, fname As String, age As Integer

Open "C:\Winners.csv" For Input As #1

Do While Not EOF(1)

Input #1, lname, fname, age

MsgBox lname & ", " & fname & ", " & age


Close #1 End Sub

4. Before you run the Winners procedure, make sure that the file is in the specified path or change the path in the code of this procedure to point to the actual location of the Winners.csv file on your hard drive.

The above procedure opens the Winners.csv file for input and sets up a Do...While loop that runs through the entire file until the end of the file is reached. The Input #1 statement is used to write the contents of each line of text into three variables: lname, fname, and age. Then a message box displays the contents of these variables. The procedure ends by closing the Winners.csv file.

Writing Data to Sequential Files

When you want to write data to a sequential file, you should open the file in the Append or Output mode. The differences between these modes are explained below:

■ Append allows adding data to the end of an existing text file. For example, if you open the Readme.txt file in the Append mode and add to this file the text "Thank you for reading this document," Visual Basic won't delete or alter in any way the text that is currently in the file but will add the new text to the end of the file.

■ Output. When you open a file in the Output mode, Visual Basic will delete the data that is currently in the file, and if the file does not exist, a brand new file will be created. For example, if you open the Readme.txt file in the Output mode and attempt to write some text to it, the previous text that was stored in this file will be removed. If you don't back up the file prior to writing the data, this mistake may be quite costly. You should open an existing file in the Output mode only if you want to replace its entire contents with new data.

Here are some examples of when to open a file in the Append or Output mode:

■ To add new text to the end of C:\Readme.txt, open the file in the Append mode as follows:

Open "C:\Readme.txt" For Append As #1

■ To enter some text in a brand new file called "C:\Result.txt," open the file in the Output mode as follows:

Open "C:\Result.txt" For Output As #1

■ To replace the contents of an existing file C:\Winners.csv with a list of new winners, first prepare a backup copy of the original file, and then open the original file in the Output mode:

FileCopy "C:\Winners.csv","C:\Winners.old" Open "C:\Winners.csv" For Output As #1

Tip 8-6: Can't Read and Write at the Same Time

Sequential files have to be opened separately to perform read and write operations. You cannot perform these operations simultaneously. For instance, after a file has been opened for output and data has been written to the file, the file should be closed before being opened for input.

Tip 8-7: Advantages and Disadvantages of Sequential Files

Although sequential files are easy to create and use, and don't waste any space, they have a number of disadvantages. For example, you can't easily find one specific item in the file without having to read through a large portion of the file. Also, an individual item of the file cannot be changed or deleted easily—you must rewrite the entire file. And as stated in Tip 8-6 sequential files have to be opened separately for read/write operations.

Using Write # and Print # Statements

Now that you know both methods for opening a text file with the intention of writing to it (Append or Output), it's time to learn the Write # and Print # statements that will allow you to send data to the file.

When you read data from a sequential file with the Input # statement, you usually write data to this file with the Write # statement. This statement looks like the following:

Write #filenumber, [outputlist]

filenumber, which specifies the number of the file you're working with, is the only required argument of the Write # statement. outputlist is the text you want to write to the file. outputlist can be a single text string or a list of variables that contain data that you want to write. If you specify only the file number, Visual Basic will write a single empty line to the open file.

To illustrate how the data is written to a file, let's prepare a text file with the first name, last name, birthdate, and number of siblings for three people:

1. Enter the DataEntry procedure in the current module:

Sub DataEntry()

Dim lname As String Dim fname As String Dim birthdate As Date Dim s As Integer

Open "C:\My Documents\Friends.txt" For Output As #1 lname = "Smith" fname = "Gregory" birthdate = #1/2/63# s = 3

Write #1, lname, fname, birthdate, s lname = "Conlin" fname = "Janice" birthdate = #5/12/48# s = 1

Write #1, lname, fname, birthdate, s lname = "Kaufman" fname = "Steven" birthdate = #4/7/57# s = 0

Write #1, lname, fname, birthdate, s

Close #1 End Sub

The above procedure opens the C:\My Documents\Friends.txt file for output. Because this file does not yet exist on your hard disk, Visual Basic creates a brand new file and writes three records to it. The data written to the file is stored in variables. Notice that the strings are delimited with double quotes (" ") and the birthdate is surrounded by pound signs (#).

When you open the Friends.txt file using the Windows Notepad, you will see the following entries:




Notice that the Write # statement automatically inserts commas between the individual data items in each record and places the end-of-line character at the end of each line of text (Chr(13) & Chr(10)) so that each new record starts on a new line. In the above example, each line of text shows one record—each record begins with the last name and ends with the number of siblings.

If instead of separating data with commas you'd rather show the contents of a file in columns, write the data with the Print # statement. For example, if you replace the Write # statement in the DataEntry procedure above with the Print # statement, Visual Basic will write the data in the following way:

Smith Gregory 1/2/63 3 Conlin Janice 5/12/48 1 Kaufman Steven 4/7/57 0

Although the Print # statement has the same syntax as the Write # statement, Print# writes data to the sequential file in a format ready for printing. The variables in the list may be separated with semicolons or spaces. To print out several spaces, you should use the Spc(n) instruction, where n is the number of spaces. Similarly, to enter a word in the fifth column, you should use the instruction Tab(5).

Let's look at some formatting examples:

■ To add an empty line to a file, use the Write# statement with a comma: Write #1,

■ To enter the text "fruits" in the fifth column: Write #1, Tab(5); "fruits"

■ To separate the words "fruits" and "vegetables" with five spaces: Write #1, "fruits"; Spc(5); "vegetables"

0 0


  • wiseman
    How to send data go to sequential file in vba?
    16 days ago

Post a comment