XL: Working with Binary Access Files

ID: Q151335

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

Unlike random file access, binary file access has variable length records. There is no wasted space in a binary accessed file. If you retrieve the data at file location 112 as an integer, bytes 112 and 113 are retrieved to make up an integer value, because an integer data type requires two bytes.

It does not matter that these two bytes may be part of 4 bytes previously stored as Long. It is up to your application to keep track of the contents of the file and make sure that such actions are correct. The following is an example data type for binary file access:


   Type Person

      LName as String
      FName as String
      Age as Integer

   End Type

Note that LName and FName are strings, which is a variable-length data type. Age is an integer, which is a 2-byte data 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

When the WriteBinary macro is run, it will create a file called BINARY.TXT.

The two records in this example take up 34 bytes (as opposed to the 44 bytes required by the same data with random access). Keep in mind that when opening this file in a text editor, such as Notepad, the file will not be readable. It is a binary file, not a text file.

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

Additional query words:

Keywords : kbcode kbprg xlloadsave xlvbahowto xlvbainfo
Version : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a
Platform : MACINTOSH WINDOWS
Issue type : kbhowto