Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

2017-07-07 02:10:07

VBA contains a number of statements that allow low-level manipulation of files. These Input/Output (I/O) statements give you much more control over files than Excel's normal text file import and export options.

A file can be accessed in any of three ways:

Sequential access: By far the most common method. This allows reading and writing individual characters or entire lines of data.

Random access: Used only if you're programming a database application - something that's not really appropriate for VBA.

Binary access: Used to read or write to any byte position in a file, such as storing or displaying a bitmap image. This access method is rarely used in VBA.

Because random and binary access files are rarely used with VBA, this chapter focuses on sequential access files, which are accessed sequentially. In other words, your code starts reading from the beginning of the file and reads each line sequentially. For output, your code writes data to the end of the file.

Note

The method of reading and writing text files discussed in this book is the traditional data-channel approach. Another option is to use the object approach. The FileSystemObject object contains a TextStream object that can be used to read and write text files. The FileSystemObject object is part of the Windows Scripting Host. As I mention earlier, this scripting service is disabled on some systems because of the possibility of transferring a virus.

Opening a text file

VBA's Open statement (not to be confused with the Open method of the Workbooks object) opens a file for reading or writing. Before you can read from or write to a file, you must open it.

The Open statement is quite versatile and has a rather complex syntax:

Open pathname For mode [Access access] [lock] _ As [#]filenumber [Len=reclength]

pathname : (Required) The pathname part of the Open statement is quite straightforward. It simply contains the name and path (optional) of the file to be opened.

mode : (Required) The file mode must be one of the following:
- Append : A sequential access mode that either allows the file to be read or allows data to be appended to the end of the file.
- Input : A sequential access mode that allows the file to be read but not written to.
- Output : A sequential access mode that allows the file to be read or written to. In this mode, a new file is always created. (An existing file with the same name is deleted.)
- Binary : A random access mode that allows data to be read or written to on a byte-by-byte basis.
- Random : A random access mode that allows data to be read or written in units determined by the reclength argument of the Open statement.

access : (Optional) The access argument determines what can be done with the file. It can be Read , Write , or Read Write .

lock : (Optional) The lock argument is useful for multiuser situations. The options are Shared , Lock Read , Lock Write , and Lock Read Write .

filenumber : (Required) A file number ranging from 1 to 511 . You can use the FreeFile function to get the next available file number. (Read about FreeFile in the upcoming section, "Getting a file number.")

reclength : (Optional) The record length (for random access files) or the buffer size (for sequential access files).

Reading a text file

The basic procedure for reading a text file with VBA consists of the following steps:

Open the file by using the Open statement.

Specify the position in the file by using the Seek function (optional).

Read data from the file (by using the Input , Input # , or Line Input # statements).

Close the file by using the Close statement.

Writing a text file

The basic procedure for writing a text file is:

Open or create the file by using the Open statement.

Specify the position in the file by using the Seek function (optional).

Write data to the file by using the Write # or Print # statements.

Close the file by using the Close statement.

Getting a file number

Most VBA programmers simply designate a file number in their Open statement. For example:

Open "myfile.txt" For Input As #1

Then you can refer to the file in subsequent statements as #1 .

If a second file is opened while the first is still open, you would designate the second file as #2 :

Open "another.txt" For Input As #2

Another approach is to use VBA's FreeFile function to get a file handle. Then you can refer to the file by using a variable. Here's an example:

FileHandle = FreeFile Open "myfile.txt" For Input As FileHandle

Determining or setting the file position

For sequential file access, it's rarely necessary to know the current location in the file. If for some reason you need to know this, you can use the Seek function.

Statements for reading and writing

VBA provides several statements to read and write data to a file.

Excel's Text File Import and Export Features

Excel supports three types of text files:

CSV (Comma-Separated Value) files : Columns of data are separated by a comma, and each row of data ends in a carriage return. For some non-English versions of Excel, a semicolon rather than a comma is used.

PRN : Columns of data are aligned by character position, and each row of data ends in a carriage return.

TXT (Tab-delimited) files : Columns of data are separated by Tab characters, and each row of data ends in a carriage return.

When you attempt to open a text file with the Office Open command, the Text Import Wizard might appear in order to help you delineate the columns. If the text file is tab-delimited or comma-delimited, Excel usually opens the file without displaying the Text Import Wizard. If the data is not interpreted correctly, close the file and try renaming it to use a .TXT extension.

The Text to Columns Wizard (accessed by choosing Data Data Tools Text to Table) is identical to the Text Import Wizard but works with data stored in a single worksheet column.

Three statements are used for reading data from a sequential access file:

Input : Reads a specified number of characters from a file.

Input # : Reads data as a series of variables, with variables separated by a comma.

Line Input # : Reads a complete line of data (delineated by a carriage return and/or linefeed character).

Two statements are used for writing data to a sequential access file:

Write # : Writes a series of values, with each value separated by a comma and enclosed in quotes. If you end the statement with a semicolon, a carriage return/linefeed sequence is not inserted after each value. Data written with Write # is usually read from a file with an Input # statement.

Print # : Writes a series of values, with each value separated by a Tab character. If you end the statement with a semicolon, a carriage return/linefeed sequence is not inserted after each value. Data written with Print # is usually read from a file with a Line Input # or an Input statement.