Sequential Access Files

Writing information to a sequential access file is sort of like recording music to a cassette tape. The songs vary in length and are recorded one after the other. Because it is hard to know the location of each song on the tape, it is difficult to quickly access a particular song. When information is written to a sequential file, the individual pieces of data (usually stored in variables) vary in length and are written to the file one after the other. For example, a sequential file containing names and phone numbers may look something like what's shown here:

"John Smith", "111-2222" "Joe James", "123-4567" "Jane Johnson", "456-7890"

The names and phone numbers were all written to the file as strings so they are enclosed in quotes. Numerical values written to a sequential access file will not contain the quotes. The strings containing the names vary in length and will require different amounts of memory for storage. If access to a part of the sequential file is desired at a later time (say we want Jane Johnson's phone number), the entire file must be read into memory because it is not possible to know the location of the desired component within the file. After loading the file, the content must be searched for the desired value. This makes sequential access inefficient with very large files, because it will take too long to access the desired information. With smaller files, however, that do not take long to read, sequential access will work well. The CreateSeqFile() sub procedure writes textual information from the first three rows in columns A and B of a worksheet to a sequential access file.

Public Sub CreateSeqFile() Dim filePath As String Dim I As Integer filePath = ActiveWorkbook.Path & "\SeqPhone.txt" Open filePath For Output As #1

Write #1, Cells(I, "A").Value, Cells(I, "B").Value Next I Close #1 End Sub

The procedure above uses a For/Next loop to write the contents of the first three cells of columns A and B to a file called SeqPhone.txt. The I/O operation is terminated with the Close statement. The resulting file as viewed from Notepad is shown in Figure 7.15.

Using Notepad to view a sequential file created using VBA code.

Using Notepad to view a sequential file created using VBA code.

Using Write # places quotes around each value written to the file. The file contains three lines of data because Write # adds a new line character to the end of the last value written to the file; because the For/Next loop iterates three times, the Write #statement was executed three times, resulting in three lines of data.

Because the structure of the file is known, it is a simple task to alter the CreateSeqFile() procedure to create a new procedure that reads the data.

Public Sub ReadSeqFile() Dim filePath As String Dim I As Integer Dim theName As String Dim theNumber As String I = 1

filePath = ActiveWorkbook.Path & "\SeqPhone.txt" Open filePath For Input As #1 Do While Not EOF(1)

Input #1, theName, theNumber Cells(I, "A").Value = theName Cells(I, "B").Value = theNumber I = I + 1

Loop

Close #1 End Sub

I changed the Open statement in the ReadSeqFile() procedure to allow for data input, and I replaced Write #with Input #. I also replaced the For/Next loop with a Do-loop and used the EOF() function in the conditional to test for the end of the file. The EOF() function accepts the file number as an argument and returns true when the end of the file is reached. The loop, therefore, continues as long as the EOF() function returns false (Do While NOT False equates to Do While True). Variables must be used to hold the strings returned from the file. Two variables (theName and theNumber) are used in order to match the structure of the procedure that wrote the data to the file.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment