By itself, a file consists of nothing more than a series of related byteslocated on disks. When your application accesses a file, it must makeassumptions about what the bytes are supposed to represent (integers,strings, or other data types). Microsoft Excel Visual Basic forApplications provides functions and statements that enable you to processthe file based on these assumptions. By processing files, your applicationcan create, manipulate, and store large amounts of data, access severalsets of data at once, and share data with other applications. RandomAccess 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 ofrecords of equal length. Each record must correspond to a single datatype, that is, an integer, a string, a user-defined type, and so on. Byusing a user-defined type for each record, you can create records made ofnumerous fields with different types. The length of each record depends onits data type. Integers require 2 bytes, Boolean requires 2 bytes, andcharacters in a string require 1 byte for each character. The followinguser-defined type requires 44 bytes, so each record takes up 44 bytes inthe file:
Type MyRecord LastName as String*20 FirstName as String*20 Age as Integer Married as Boolean End Type
LastName requires 20 bytes, FirstName requires 20 bytes, Age requires 2bytes 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 randomaccess, you must specify a length for the string because each recordshould be of equal length. In other words, use only fixed-length strings.
For more information about the bytes required by data types, see theonline Help topic "Data Type Summary."
Advantage of Random Access Files
In comparison to sequential access files, you may significantly save onthe 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 sametime. This is a great advantage over using sequential access.
Disadvantage of Random Access Files
You can have wasted space if many fields in the records are left blankor if most of the strings stored in the record are significantly shorterthan the length dimensioned for the string.
Reading Files Opened for Random Access
The bytes in random-access files form identical records, each recordcontaining one or more fields. A record with one field can have only onestandard data type, such as integer or fixed-length string. A recordwith more than one field can have different data types as defined in auser- defined type.
Before opening a file for random access, define a user-defined type thatcorresponds to the records the file contains. Because records in a fileopened for random access must all have the same length, user-definedtype string elements must have a fixed length. If the actual stringcopied into the file's record contains fewer characters than the fixedlength of the string variable, the field is filled with trailing spacesin the record with blanks. This can create wasted space in the file.Additionally, if the actual string copied into the file's recordcontains 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 Openstatement:
Open <file name> For Random As <#filenumber> Len=RecordLength
where <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, andLen=RecordLength represents the size of each record. If this value is notcorrect, 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 theuser-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 Getstatement. The Get statement has the following syntax:
where <[recnumber]> is the record number at which reading begins and<varname> is the variable into which data is read. The first record insuch a file is at position 1, the second record is at position 2, and so on. Ifyou omit [recnumber], the next record is read.
You could use the following code to read all of the data from a file whoserecords have the following structure: first field of up to 12 characters(LName); second field of up to 8 characters (FName), and third field as aninteger (Age).
Type Person LName as String*12 FName as String*8 Age as Integer End Type Sub ReadRandom() Dim P As Person ' Create a variable of user-defined type Person. ' Open the file for random access and specify that the length ' of each record is equal to the record length of P. Open "RANDOM.XXX" For Random As #1 Len = Len(P) ' Read each record in the file and display it in the Debug ' window. The number of records in the file is determined by ' the integer value of Total Bytes in File/Bytes in Each ' Record. For i = 1 To Int(LOF(1) / Len(P)) Get #1, i, P Debug.Print P.LName, P.FName, P.Age Next 'Close the file. Close #1 End Sub
Writing to Files Opened for Random Access
The Put statement is the companion to the Get statement. When writingrecords to files opened for random-access, you must use the Put statement.The Put statement has the following syntax:
where <[recnumber]> is the record number at which writing begins and<varname> is the variable from which data is written. The first record insuch a file is at position 1, the second record is at position 2, and soon. If you omit [recnumber], the next record is written.
To create a new file you could use the following code:
Type Person LName as String*12 FName as String*8 Age as Integer End Type Sub WriteRandom() Dim P As Person ' Create a variable of user-defined type Person. ' Open the file for random access and specify that the length ' of each record is equal to the record length of P. Open "RANDOM.XXX" For Random As #1 Len = Len(P) ' Create and write the first record. P.LName = "Doe" P.FName = "Jane" P.Age = 9 Put #1, , P ' Create and write the second record. P.LName = "Thompson" P.FName = "Richard" P.Age = 4 Put #1, , P ' Continue to add additional records if needed. ' Close the file. Close #1 End Sub
Editing Records in a File Opened for Random Access
Using both the Get and the Put statements, you can edit a single recordwithin the file by specifying the record number. You use Get to read therecord into a variable, make the necessary changes to the variable, andthen 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 thefile RANDOM.XXX (note that the record for Richard Thompson is the secondrecord). To do this, use the following code:
Type Person LName as String*12 Name as String*8 Age as Integer End Type Sub ChangeRecord() Dim P As Person ' Create a variable of user-defined type Person. ' Open the file for random access and specify that the length ' of each record is equal to the record length of P. Open "RANDOM.XXX" For Random As #1 Len = Len(P) ' Get the contents of the second record and place them in the ' variable P. Get #1, 2, P ' Change the Age field in P to 5. P.Age = 5 ' Write P back to the file at the second record. Put #1, 2, P ' Close the file. Close #1 End Sub
Appending Records to the End of a File Opened for Random Access
To append records to the end of a file, determine the record number of thenew 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:
Sub AppendRecord() Dim P As Person ' Create a variable of user-defined type Person. ' Open the file for random access and specify that the length ' of each record is equal to the record length of P. Open "RANDOM.XXX" For Random As #1 Len = Len(P) ' Write the new record to the end of the file. ' The new record number is determined by taking the length of ' the file divided by the size of the record plus one. P.LName = "Smith" P.FName = "Bill" P.Age = 30 Put #1, Int(LOF(1) / Len(P)) + 1, P ' Close the file. Close #1 End Sub