Sequential access files, plain text files, are the types of files that youwill encounter the most in Visual Basic for Applications. In a sequentialaccess file, each character in the file is assumed to represent either atext character or a text formatting sequence, such as a tab or a newlinecharacter. Files such as CSV (Comma-Separated Value), TXT (Tab Delimited)and PRN (Space Formatted) are examples of sequential access files.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The advantages of binary access files are as follows:
- Files of this type can be easily created or manipulated by text editors.
- Just about all applications can read/write sequential access files.
- Files of this type are easy to process in Visual Basic for Applications.
The disadvantages of binary access files are as follows:
- Files of this type are not well suited for storing large amounts of data because all values are stored as a character string.
- Files of this type usually require more storage space than the other access types.
- You cannot read and write to a file that is opened for sequential access simultaneously. You can only do one or the other at one time.
Opening Files for Sequential Access
When you open a file for sequential access, you open it to perform one ofthe following operations:
- Input characters to the file (Input)
- Output characters to the file (Output)
- Append characters to the file (Append)
To open a file for any one of these operations, use the Open statement, asfollows:
Open filename For [Input | Output | Append] As filenumber Len=buffersize
When you use sequential access to open a file for Input, the file mustalready exist; otherwise, Visual Basic for Applications generates atrappable error. When you try to open a nonexistent file for Output or forAppend, the Open statement actually creates the file first, and then opensit. Each time you open the file, you must use the Close statement to closethe file before reopening the file for another type of operation.
Reading Files Opened for Sequential Access
To retrieve the contents of a text file, first open the file forsequential input. Then, use Line Input # or Input # to copy the file into variables.Use Line Input # when you need to read a file, one line at a time. Withdelimited files (such as CSV), use Input # to read each line of the fileinto a list of variables.
Reading a File Line-by-Line
Use Line Input # with a file opened for sequential access if the data isstored in the file one line at a time. The Line Input # statement readsfrom a file one character at a time until it encounters a carriage return(Chr(13)) or a carriage return-linefeed sequence. Carriage return-linefeedsequences are skipped rather than appended to the character string.
The following sample code uses the Line Input # statement to read data infrom a sample text file called Textfile.txt, one line at a time.
NOTE: You will have to create the text file called Textfile.txt, if onedoes not already exist.
Sub ReadStraightTextFile() Dim LineofText As String ' Open the file for Input. Open "TEXTFILE.TXT" For Input As #1 ' Read each line of the text file into a single string ' variable. Do While Not EOF(1) Line Input #1, LineofText MsgBox LineofText Loop ' Close the file. Close #1 End Sub
Reading a Delimited Text File
As mentioned before, use Input # to read delimited files. When read,standard string or numeric data is assigned to variables as they appear inthe text file. Delimiting commas or blank lines within the file arereturned as Empty. Double quotation marks ("") that surrounds each fieldin the input data is ignored and fields surround with #s (pound signs) can beinterpreted as dates. When using Input #, data items in a file must appearin the same order as the variables in the variable list and be matchedwith variables of the same data type. If the actual data doesn't match thevariable type, you may encounter run-time errors.
If you have a text file named Delimit.txt and enter the following data,the macro below will read in the data, one line at a time.
"Smith", "John", 22, "123 Main St.", "New York", "NY", 32432
"Doe", "Jane", 33, "324 Elm Ln.", "San Diego", "CA", 23542
"Adams", "Bill", 45, "4523 Oak Cir.", "Miami", "FL", 52343
"Jones", "Tom", 23, "2335 Maple Dr.", "Houston", "TX", 23453
The following sample code uses the Input # statement to read data from thesample text file into variables:
Sub ReadDelimitedTextFile() Dim LName As String, FName As String, Addr As String, City As String Dim state As String Dim age As Integer ' Open the file for Input. Open "DELIMIT.TXT" For Input As #1 ' Read each line of the text file into the list of variables ' until the end of the file is reached. Do While Not (EOF(1)) Input #1, LName, FName, age, Addr, City, state, zip MsgBox LName & ", " & FName & ", " & age & ", " & Addr & ", " _ & City & ", " & state & ", " & zip Loop ' Close the file. Close 1 End Sub
Writing to Files Opened for Sequential Access
To store the contents of variables in a sequential text file, open it forsequential access, and then use either the Print # or Write # statement towrite the data to the file.
The Write # statement is used to write raw data to the text file as comma-delimited and has the following syntax:
When you write to the file using Write #, string fields are surroundedwith double quotation marks and date fields are surrounded with #s (poundsigns). In this respect, the Write # is a companion to Input #. Thefollowing macro demonstrates how you can write to a delimited text file:
Sub WriteFile() Dim LName As String Dim BDay As Date Dim age As Integer ' Create a new text file called Test.txt. Open "TEST.TXT" For Output As #1 ' Create and then write the first "record." LName = "Doe" BDay = #1/1/95# age = 1 Write #1, LName, BDay, age ' Create and then write the second "record." LName = "Smith" BDay = #4/29/56# age = 39 Write #1, LName, BDay, age ' Create and then write the third "record." LName = "Jones" BDay = #5/1/80# age = 15 Write #1, LName, BDay, age ' Close the file. Close #1 End Sub
After the WriteFile macro is finished, you will have a comma-delimitedtext file named Test.txt that looks as follows:
Unlike Write #, the Print # statement writes display-formatted data (orspace-formatted data) to a sequential file. The Print # statement has thefollowing syntax:
In the output variable list for Print #, you can specify a number ofspaces to separate fields. For more information, search for "Print # Statement,"using the Microsoft Excel Help Index.
If you change Write # to Print # in the previous WriteFile macro, theTest.txt file would look something like this instead:
Doe 1/1/95 1
Smith 4/29/56 39
Jones 5/2/80 15
Modifying Data in a File Opened for Sequential Access
Note that when you Open a file for Output, if the file already exists, youare essentially replacing the contents of the file when you write to it.The new data that is written is not appended to the file. In order toappend data to the end of a file, you must Open the file for Append. Alldata that is written to a file opened for Append is added to the end ofthe file. You cannot modify the "records" in a file that is opened forsequential access. What you have to do is as follows:
- Open the file for Input and read all of the data into variables.
- Close the file.
- Make the necessary modifications to the data contained in the variables.
- Open the file for Output and write all of the modified data to the file.
- Close the file.
Because of all the steps required to make modifications to a file openedfor sequential access, sequential access may not be the most efficientmethod for modifying data in a text file, especially if the text file islarge.