Article ID: 150700 - View products that this article applies to.
This article was previously published under Q150700
By itself, a file consists of nothing more than a series of related bytes located on disks. When your application accesses a file, it must make assumptions about what the bytes are supposed to represent (integers, strings, or other data types). Microsoft Excel Visual Basic for Applications provides functions and statements that enable you to process the file based on these assumptions. By processing files, your application can create, manipulate, and store large amounts of data, access several sets of data at once, and share data with other applications. Random Access files can be opened as read/write, but must have fixed-field length.
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. A file opened for random access is assumed to be composed of a series of records of equal length. Each record must correspond to a single data type, that is, an integer, a string, a user-defined type, and so on. By using a user-defined type for each record, you can create records made of numerous fields with different types. The length of each record depends on its data type. Integers require 2 bytes, Boolean requires 2 bytes, and characters in a string require 1 byte for each character. The following user-defined type requires 44 bytes, so each record takes up 44 bytes in the file:
LastName requires 20 bytes, FirstName requires 20 bytes, Age requires 2 bytes and Married requires 2 bytes, so the total is 20+20+2+2 = 44.
When dimensioning string variables for use as a record type for random access, you must specify a length for the string because each record should be of equal length. In other words, use only fixed-length strings.
For more information about the bytes required by data types, see the online Help topic "Data Type Summary."
Advantage of Random Access FilesIn comparison to sequential access files, you may significantly save on the amount of disk space required by the file by using random access.
With random access, files can be opened for both read/write at the same time. This is a great advantage over using sequential access.
Disadvantage of Random Access FilesYou can have wasted space if many fields in the records are left blank or if most of the strings stored in the record are significantly shorter than the length dimensioned for the string.
Reading Files Opened for Random AccessThe bytes in random-access files form identical records, each record containing one or more fields. A record with one field can have only one standard data type, such as integer or fixed-length string. A record with more than one field can have different data types as defined in a user- defined type.
Before opening a file for random access, define a user-defined type that corresponds to the records the file contains. Because records in a file opened for random access must all have the same length, user-defined type string elements must have a fixed length. If the actual string copied into the file's record contains fewer characters than the fixed length of the string variable, the field is filled with trailing spaces in the record with blanks. This can create wasted space in the file. Additionally, if the actual string copied into the file's record contains more characters than the fixed length of the string variable, the string will be truncated.
To open a file for random access, use the following syntax for the Open statement:
Open <file name> For Random As <#filenumber> Len=RecordLengthwhere <file name> is the name of the file (surrounded by quotation marks) that you want to open, <#filenumber> is a number between 1 and 511, and Len=RecordLength represents the size of each record. If this value is not correct, access to the file will more than likely result in corrupt data. In your macro, you can use the Len() function to determine the size of the user-defined type or variable that represents a record in the file.
To read the data in a file opened for random access, you must use the Get statement. The Get statement has the following syntax:
Get [#]filenumber,<[recnumber]>,<varname>where <[recnumber]> is the record number at which reading begins and <varname> is the variable into which data is read. The first record in such a file is at position 1, the second record is at position 2, and so on. If you omit [recnumber], the next record is read.
You could use the following code to read all of the data from a file whose records have the following structure: first field of up to 12 characters (LName); second field of up to 8 characters (FName), and third field as an integer (Age).
Writing to Files Opened for Random AccessThe Put statement is the companion to the Get statement. When writing records to files opened for random-access, you must use the Put statement. The Put statement has the following syntax:
Put [#]filenumber,<[recnumber]>,<varname>where <[recnumber]> is the record number at which writing begins and <varname> is the variable from which data is written. The first record in such a file is at position 1, the second record is at position 2, and so on. If you omit [recnumber], the next record is written.
To create a new file you could use the following code:
Editing Records in a File Opened for Random AccessUsing both the Get and the Put statements, you can edit a single record within the file by specifying the record number. You use Get to read the record into a variable, make the necessary changes to the variable, and then you use Put to write the record back to the file. For example, suppose you wanted to change Richard Thompson's age from 4 to 5 in the file RANDOM.XXX (note that the record for Richard Thompson is the second record). To do this, use the following code:
Appending Records to the End of a File Opened for Random AccessTo append records to the end of a file, determine the record number of the new record and write it to the file. To determine the new record number, calculate the number of records in the file, and then add one to it.
The macro below appends a new record to RANDOM.XXX:
Article ID: 150700 - Last Review: October 10, 2006 - Revision: 2.3