Article ID: 151335 - Last Review: August 17, 2005 - Revision: 2.1

Working with Binary Access Files

Retired KB Content Disclaimer

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

This article was previously published under Q151335

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.

Back to the top

MORE INFORMATION

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

Back to the top

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.

Back to the top

Disadvantage of Binary Access Files

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

Back to the top

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.

Back to the top

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

Back to the top

APPLIES TO

Microsoft Excel 97 Standard Edition
Microsoft Excel 95 Standard Edition
Microsoft Excel 5.0 Standard Edition
Microsoft Excel 98 for Macintosh

Back to the top

kbcode kbhowto kbprogramming KB151335

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top