File Access with Visual Basic® for Applications

Table of Contents

• Using Sequential Access
  Opening Files for Sequential Access
  Reading Files Opened for Sequential Access
  Writing to Files Opened for Sequential Access
  
  
• Using Random Access
  Reading Files Opened for Random Access
  Writing to Files Opened for Random Access
  
  
• Using Binary Access
  Opening Files For Binary Access
  Writing to Files Opened for Binary Access
  Reading Files Opened For Binary Access

Overview

In Visual Basic for Applications, there are three types of file access. These types are listed in the following table.

 

Type Access	Note
Sequential	Used with simple text files.
Random Access	Used for files that are composed of a series of records of identical length.
Binary	Used for files with no general format. You must know exactly how the data is written to the file in order to retrieve it.

By itself, a file consists of nothing more than a series of related bytes located on a disk. When your program accesses a file it must make assumptions about what the bytes are supposed to represent (integers, strings, or other data types). Visual Basic for Applications provides functions and statements that allow you to process the file based on these assumptions. The table below lists some of the file access functions and statements that Visual Basic for Applications provides for different types of file access:

File Access Functions	Function's Action	Sequential Access	Random Access	Binary Access
Close	Closes the file	X	X	X
EOF	Tests for end of file	X	X	X
FreeFile	Returns the next file number available for use by the Open statement.	X	X	X
Get	Reads data from a file at a specific record number		X	X
Input$	Returns all characters (bytes) from an open file	X		X
Input #	Reads a line of text into a list of variables	X
Line Input #	Reads an entire line of text into a single variable	X
Loc	Returns the current read/write position within an open file.	X	X	X
LOF	Returns the size, in bytes, of a file opened using the Open statement	X	X	X
Open	Opens a file with a specified access type	X	X	X
Put	Writes data to a file at a specific record number		X	X
Seek	Returns the current read/write position within a file opened using the Open statement	X	X	X
Write #	Writes data to a file opened for sequential access	X

Table 1: Visual Basic for Applications File Access Functions and Statements

This document contains example macros to demonstrate how to access files with these Visual Basic for Applications functions. The macro examples in this document may be used with Microsoft Excel versions 5.0, 7.0, and Microsoft Excel 97. You can download a workbook that contains these macro examples and the text files used in these examples. To use the sample macros copy the workbook and the text files to a directory named C:\FILEIO.

Using Sequential Access

With sequential access, each character in the file is assumed to represent either a text character or a text formatting sequence such as a tab or a new line character. Files such as CSV (comma separated), TXT (tab delimited) and PRN (space formatted) are all examples of files you would access sequentially.

Advantages of Sequential Access:

• Files can be easily created or manipulated by text editors.
• Most applications can read/write files created with sequential access.
• It is easier to process in Visual Basic for Applications.

Disadvantages of Sequential Access:

• It is not well suited for storing large amounts of data because all values are stored as a character string.
• Usually requires more storage space than the other access types.
• You cannot read and write to a file that is opened for sequential access. You can only do one or the other at any given time.

Opening Files for Sequential Access

 

Operation	Description
Input	Input characters to the file.
Output	Output characters to the file.
Append	Append characters to the end of the file.

To open a file for any one of these operations use the Open statement:

Open filename For [Input | Output | Append] As filenumber Len=buffersize

When you use sequential access to open a file for Input, the file must already exist; otherwise Visual Basic for Applications generates a trappable error. When you try to open a nonexistent file for Output or for Append, the Open statement will actually create the file first and then open the empty file. Each time you open a file you must Close it before reopening the file for another type of operation.

Reading Files Opened for Sequential Access

Reading a File Line-by-Line

This is the first line of the file
		This is the second line of the file
		This is the third line of the file
		This is the fourth line of the file
		This is the last line of the file

Microsoft provides examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft Support Professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

The following sample macro uses the Line Input # statement to read data from the sample text file in Figure 1:

Sub ReadStraightTextFile()
Dim LineofText As String
'Open the file for Input
    Open "C:\FILEIO\TEXTFILE.TXT" For Input As #1
    'Read each line of the text file into a single string   
    'variable
    Do While Not EOF(1)
        Line Input #1, LineofText
        Debug.Print LineofText
    Loop
        'Close the file
    Close #1
End Sub

Reading a Delimited Text File

Standard string or numeric data is assigned to variables as they appear in the text file. Delimiting commas or blank lines within the file are returned as Empty. Double quotation marks that surround each field in the input data are ignored and fields surround with "#"'s are interpreted as dates. When using Input #, data items in a file must appear in the same order as the variables in the variable list and they must be matched with variables of the same data type. If the data doesn't match the variable type you may encounter run-time errors.

"Smith", "John", 22, "123 Main St.", "New York", "NY", 32432
"Doe", "Jane", 33, "324 Elm Ln.", "San Diego", "CA", 23542
"Adams", "Bill", 45, "4523 Oak Cir.", "Miami", "FL", 52343
"Jones", "Tom", 23, "235 Maple Dr.", "Houston", "TX", 23453

Figure 2: Sample Delimited Text File Delimit.txt

This sample code below uses the Input # statement to read data from the sample text file in Figure 2 into variables.

Sub ReadDelimitedTextFile()
Dim LName As String, FName As String, Addr As String, City As String
Dim state As String
Dim age As Integer
'Open the file for Input
    Open "C:\FILEIO\DELIMIT.TXT" For Input As #1
'Read each line of the text file into the list of variables
    'until the end of the file is reached
    Do While Not (EOF(1))
        Input #1, LName, FName, age, Addr, City, state, zip
        Debug.Print LName, FName, age, Addr, City, state, zip
    Loop
'Close the file
    Close 1
End Sub

Writing to Files Opened for Sequential Access

To store the contents of variables in a sequential text file, open it for sequential access and then use either the Print # or Write # statement to write the data to the file.

The Write # statement writes comma-delimited format data to the text file and uses the following syntax:

Write#filenumber[,outputlist]

When you write to the file using Write #, string fields are surrounded with double quotation marks and date fields are surrounded with #'s. In this respect, Write # is a companion to Input #. The following macro demonstrates how you can write to a delimited text file:

Sub WriteFile()
Dim LName As String
Dim BDay As Date
Dim age As Integer
'Create a new text file called TEST.TXT
    Open "C:\FileIO\TEST.TXT" For Output As #1
'Create and then write the first "record"
    LName = "Doe"
    BDay = #1/1/95#
    age = 1
    Write #1, LName, BDay, age
'Create and then write the second "record"
    LName = "Smith"
    BDay = #4/29/56#
    age = 39
    Write #1, LName, BDay, age
'Create and then write the third "record"
    LName = "Jones"
    BDay = #5/1/80#
    age = 15
    Write #1, LName, BDay, age
'Close the file
    Close #1
End Sub

When the macro "WriteFile" is done, you will have a comma-delimited text file named Test.txt that looks like the following:

"Doe",#1995-01-01#, 1

"Smith",#1956-04-29#,39

"Jones",#1980-05-01#,15

Unlike Write #, the Print # statement writes display-formatted data (or space-formatted data) to a sequential file and uses the following syntax:

Print # filenumber,[outputlist]

In the list of output variables for Print #, you can specify the number of spaces to separate fields. For more information, see the online help topic for the Print # statement.

If you were to change "Write #" to "Print #" in the macro WriteFile the file Test.txt would look like the following:

Doe 1/1/95 1

Smith 4/29/56 39

Jones 5/2/80 15

Modifying Data in a File Opened for Sequential Access

Note that when you open a file for Output, and the file already exists, you are essentially replacing the contents of the file when you write to it. The new data that is written is not appended to the file. In order to append data to the end of a file you must open the file for Append. All data that is written to a file opened for Append is added to the very end of the file. There is no way to modify the records in a file opened for sequential access, but there is a process to workaround this:

1. Open the file for Input and read all of the data into variables.

2. Close the file.

3. Make the necessary modifications to the data contained in the variables.

4. Open the file for Output and write all of the modified data to the file.

5. Close the file.

Because of the number of steps required to make modifications to a file opened for sequential access, it may not be the most efficient method for modifying data in a text file particularly if the text file is relatively large.

Using Random Access

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 (an integer, a string, or a user-defined type). The length of each record depends on its data type. The table below demonstrates the size of each record for a few data types.

 

Record Data Type	Bytes Required by Each Record	Comment
Integer	2	An integer requires 2 bytes.
String*1 (A)	1	Each character in a string needs 1 byte.
String*20	20	Each character in a string needs 1 byte.
Type MyRecord LastName as String*20 FirstName as String*20 Age as Integer Married as Boolean End Type	44	LastName requires 20 bytes, FirstName requires 20 bytes, Age requires 2 bytes, and Married requires 2 bytes, thus, one record requires 44 bytes ( 20+20+2+2=44).

Table 2: Bytes Required for Records

(A) When dimensioning string variables to use as a record type for random access you must specify a length for the string since each record should be of equal length. In other words, use only fixed length strings for storing the records of a file opened for random access.

Advantages of Random Access:

• Compared to Sequential access, you may save significant disk space required by the file.
• Files can be opened for both read and write at the same time.

Disadvantage of Random Access:

• You can waste space if fields in the records are left blank or if most of the record strings are significantly shorter than the length dimensioned for the string.

Type Person
   LName as String*12
   FName as String*8
      Age as Integer
End Type

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 "C:\FileIO\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

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 "C:\FileIO\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 Get and Put statements you can edit a single record by specifying the record number. Use Get to read the record into a variable make the necessary changes, and then use Put to write the record back to the file. For example, to change Richard Thompson's age from 4 to 5 in the file Random.xxx (note that Richard Thompson is the second record), use the following macro:

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 "C:\FileIO\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

After you run the ChangeRecorrd macro, the file looks like Figure 4.

Figure 4: Records in Random.xxx After Running the ChangeRecord Macro

Appending Records 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.

The AppendRecord macro 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 "C:\FileIO\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

After you run the ChangeRecord macro, the file looks like Figure 5.

Figure 5: Records in Random.xxx After Running the Macro AppendRecord

Using Binary Access

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 to retrieve it correctly.

For example, if you retrieve the data at file location 112 as an integer bytes, 112 and 113 are retrieved to make up an integer value. It does not matter that these two bytes may be part of 4 bytes previously stored as Long integer type; your program must keep track of the contents of the file and make sure that such actions are correct.

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 as you can with random access.

Disadvantage of Binary Access Files:

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

To understand how and when to use binary access, consider the Person records that were used to demonstrate random access. This example used records and field of fixed length to store information. The Person record for random access was:

Type Person
   LName as String*12
   FName as String*8
   Age as Integer
End Type

With random access, every record required 22 bytes regardless of whether or not the data required that space. This waste may be avoided by using binary access since it does not require equal length records. For binary access you can change the Person data type to:

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

Opening Files For Binary Access

To open a file for binary access use the following syntax for the Open statement:

Open file For Binary As filenumber

Open for binary access differs from Open for random access in that a record length is not specified. In fact, if you do specify one it is ignored. Return to Top

Writing to Files Opened for Binary Access

Since records with binary access can be of variable length, it is necessary to store information about the size of each field and record so that it can read successfully. A good way to do this is to store an integer with each string to indicate the length of the string. Here's an example of 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
   'since 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
   'since 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 "C:\FileIO\BINARY.XXX" 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 creates a file called Binary.xxx that will have the structure shown in Figure 6. The two records in this example take up 34 bytes (as opposed to the 44 bytes required by the same data with random access).

Figure 6: Binary.xxx Created by the Macro WriteBinary

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. Return to 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 spaces.

This 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 Loc(1) >= LOF(1)
      ReadOneRecord P
      Debug.Print P.LName, P.FName, P.Age
   Loop
   'Close the file
   Close 1
End Sub