File Processing
Do you know that you, too, can build your own database to store a collection of data? You can-and you can do it with file I/O (input/output) and a little help from this tutorial.
Within VBA there are many techniques for building and managing file I/O routines. File I/O is the approach taken by programmers to manage data stored in files. Data files that you create can be viewed and edited through Microsoft text editors such as Notepad.
Most data files that you work with are built upon a common foundation, much like a database. The data files you learn about in this tutorial share the following relationships and building blocks:
- Data File. A collection of data that stores records and fields
- Record. A row of related data that contains one or more fields, separated by a space, tab, or comma
- Field. An attribute in a record, which is the smallest component in a data file
In the sections to come, how to build and manage your own data files using sequential file access.
About Sequential File Access
Data files created with sequential file access have records stored in a file, one after another, in sequential order. When you access data files with sequential file access, records must be read in the same order in which they were written to the file. In other words, if you want to access the 20th record in a data file, you must first read records 1 through 19.
Sequential file access is useful and appropriate for small data files. If you find that your sequential file access program is starting to run slowly, you might want to change file access to an RDBMS such as Microsoft Access.
Opening a Sequential Data File
The first step in creating or accessing a data file is to open it. Microsoft provides an easy-touse facility for opening a data file through the Open function.
Open "Filename" For {Input | Output | Append} As #Filenumber [Len = Record Length]
The Open function takes three parameters. Filename describes the name of the file you wish to open or create. Input|Output|Append is a list from which you pick one to use. #Filenumber is a number from 1 to 511 that is used for referencing the file. Len is an optional parameter that can control the number of characters buffered. The sequential access modes are shown in Table-2.
Use the Open method to create a new file for output called quiz.dat.
Open "quiz.dat" For Output As #1
Tip:The Filename attribute can contain paths in addition to filenames. For example,
if you want to create employee records in a file named employee.dat on removable
storage, you could use the following syntax.
Open "a:\employee.dat" For Output As #1
Mode Description Input Reads records from a data file Output Writes records to a data file Append Writes or appends records to the end of a data file
The result of the Open function varies depending on the initial action chosen. If the Input parameter is chosen, the Open function searches for the file and creates a buffer in memory. If the file is not found, VBA generates an error.
Tip: A buffer is an area where data is temporarily stored.
If the file specified is not found, a new file is created using the Filename parameter as the filename. Note that the Output mode always overwrites an existing file. After a data file has been successfully opened, you can then read from it, write to it, and close it.