File access with VBA

 1999-12-17    File access    0    170

Visual Basic for Applications provides functions for performing file input/output (I/O).
This lets your custom solutions create, edit and store large amounts of data.
You can also access several datasets at the same time and share data with other applications.

File access functions in VBA

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


There are three types of file access in VBA:
  • Sequential access, read/write textfiles, such as error logs and reports, e.g. *.txt files, *.ini files and *.csv files.
  • Random access, read/write records of fixed length.
  • Binary access, read/write records of variable length or for files with no general format, this requires that you have exact knowledge on how the data is stored in the file in order to retrieve any information.