File access with VBA
1999-12-17 File access 0 214
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.