XL: How to Work with Random Access Files

ID: Q150700

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel 98 Macintosh Edition

SUMMARY

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.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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:


   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 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 Files

In 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 Files

You 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 Access

The 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=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, 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).


   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 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:


   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 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:


   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 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:


   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

Additional query words: 5.00a 5.00c 8.00

Keywords : kbprg kbdta kbdtacode PgmHowto KbVBA
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0c,7.0
Platform : MACINTOSH WINDOWS
Issue type : kbhowto