XL: Working with Binary Access Files

• 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 allow 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. Binary access allows you to use files to store data however you want; there are no assumptions made about data type or requirements for standard record length. However, you must know precisely how the data is written to the file in order to retrieve it correctly.

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 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 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/supportnet/refguide/

   Type Person
      LName as String
      FName as String
      Age as Integer
   End Type

For more information about the bytes required by data types, see the online Help topic, "Data Type Summary."

Advantages of Binary Access Files

• You can conserve disk space by building variable-length records.
• You can read and write to a file opened for binary access like you can a file opened with random access.

Disadvantage of Binary Access Files

• You must know precisely how the data is written to the file to manipulate it successfully.

Writing to Files Opened for Binary Access

Because records with binary access can be of variable length, it is necessary to actually store information about the size of each field and record so that it can be read successfully. A good way to accomplish this is to store an integer with each string to indicate the length of the string. The following is an example of creating such a file:

   Type Person
      LName as String
      FName as String
      Age as Integer
   End Type

   Sub WriteOneRecord(PRecord as Person)
   Dim StrSize as Integer

      ' Write the LName field and indicate the length of LName
      ' because it is a variable-length string.
      StrSize = Len(PRecord.LName)
      Put #1,,StrSize
      Put #1,,PRecord.LName

      ' Write the FName field and indicate the length of FName
      ' because it is a variable-length string.
      StrSize = Len(PRecord.FName)
      Put #1,,StrSize
      Put #1,,PRecord.FName

      ' Write the Age field - this is type integer so it is not
      ' necessary to indicate a length.
      Put #1,,PRecord.Age
   End Sub

   Sub WriteBinary()
   Dim P as Person

      ' Create a new file and open it for Binary access.
      Open "BINARY.TXT" For Binary As #1

      ' Create and write the first record.
      P.LName = "Doe"
      P.FName = "Jane"
      P.Age = 9
      WriteOneRecord P

      ' Create and write the second record.
      P.LName = "Thompson"
      P.FName = "Richard"
      P.Age = 4
      WriteOneRecord P

      ' Close the file.
      Close #1
   End Sub

A trade-off in using variable-length field and binary access instead of fixed-length fields and random access is that the entire record could be written with a single function call using random-access. While binary access provides greater flexibility, it also requires more code to handle I/O operations.

Reading Files Opened for Binary Access

The Get statement reads a number of bytes equal to the bytes required for the variable that is used. When you use Get with a variable-length string, the number of bytes read from the file equals the current length of the string. To temporarily set the length of a variable-length string, you can use the STRING$ function to set the variable equal to a specific number of blanks, or spaces.

The following example reads a file like the one created with the WriteBinary macro:

   Type Person
      LName as String
      FName as String
      Age as Integer
   End Type

   Sub ReadOneRecord(PRecord as Person)
   Dim StrSize As Integer

      ' Determine the size of the LName field and read it.
      Get #1, , StrSize
      PRecord.LName = String(StrSize," ")
      Get #1, , PRecord.LName

      ' Determine the size of the FName field and read it.
      Get #1, , StrSize
      PRecord.FName = String(StrSize," ")
      Get #1, , PRecord.FName

      ' Read the Age field.
      Get #1, , PRecord.Age
   End Sub


   Sub ReadBinary()
   Dim P as Person

      ' Open the file for Binary access.
      Open "BINARY.TXT" For Binary As #1

      ' Read each record in the file and display it in the Debug
      ' window.
      Do Until EOF(1)
         ReadOneRecord P
         Debug.Print P.LName, P.FName, P.Age
      Loop

      ' Close the file.
      Close #1
   End Sub