Article ID: 151262 - View products that this article applies to.
This article was previously published under Q151262
Sequential access files, plain text files, are the types of files that you will encounter the most in Visual Basic for Applications. In a sequential access file, each character in the file is assumed to represent either a text character or a text formatting sequence, such as a tab or a newline character. 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:
Opening Files for Sequential AccessWhen you open a file for sequential access, you open it to perform one of the following operations:
Open filename For [Input | Output | Append] As filenumber Len=buffersizeWhen you use sequential access to open a file for Input, the file must already exist; otherwise, Visual Basic for Applications generates a trappable error. When you try to open a nonexistent file for Output or for Append, the Open statement actually creates the file first, and then opens it. Each time you open the file, you must use the Close statement to close the file before reopening the file for another type of operation.
Reading Files Opened for Sequential AccessTo retrieve the contents of a text file, first open the file for sequential 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. With delimited files (such as CSV), use Input # to read each line of the file into a list of variables.
Reading a File Line-by-LineUse Line Input # with a file opened for sequential access if the data is stored in the file one line at a time. The Line Input # statement reads from a file one character at a time until it encounters a carriage return (Chr(13)) or a carriage return-linefeed sequence. Carriage return-linefeed sequences are skipped rather than appended to the character string.
The following sample code uses the Line Input # statement to read data in from 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 one does not already exist.
Reading a Delimited Text FileAs mentioned before, use Input # to read delimited files. When read, 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 surrounds each field in the input data is ignored and fields surround with #s (pound signs) can be interpreted as dates. When using Input #, data items in a file must appear in the same order as the variables in the variable list and be matched with variables of the same data type. If the actual data doesn't match the variable 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", 32432The following sample code uses the Input # statement to read data from the sample text file into variables:
"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
Writing to Files Opened for Sequential AccessTo 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 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 surrounded with double quotation marks and date fields are surrounded with #s (pound signs). In this respect, the Write # is a companion to Input #. The following macro demonstrates how you can write to a delimited text file:
After the WriteFile macro is finished, you will have a comma-delimited text file named Test.txt that looks as follows:
"Doe",#1995-01-01#, 1Unlike Write #, the Print # statement writes display-formatted data (or space-formatted data) to a sequential file. The Print # statement has the following syntax:
In the output variable list for Print #, you can specify a number of spaces 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, the Test.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 AccessNote that when you Open a file for Output, if the file already exists, you are 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 to append data to the end of a file, you must Open the file for Append. All data that is written to a file opened for Append is added to the end of the file. You cannot modify the "records" in a file that is opened for sequential access. What you have to do is as follows:
Article ID: 151262 - Last Review: October 11, 2006 - Revision: 2.3