Microsoft KB Archive/151335

= Working with Binary Access Files =

Article ID: 151335

Article Last Modified on 8/17/2005

-

APPLIES TO


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

-



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.



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

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

Keywords: kbhowto kbprogramming kbcode KB151335

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.