Page 321 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Files

Writing to an External File

VBA contains a number of statements that allow file manipulation. These Input/Output Chapter 13
statements give you more control over files than Excel’s normal text file import and export
options.
The standard procedure for writing to a text file is listed here:
1 Open or create the file using the Open statement.
2 Specify the position in the file using the Seek function, which is optional.
3 Write the data to the file using the Write # or the Print # statement.
4 Close the file using the Close statement.

Note Do not confuse the VBA Open statement with the Open method for the Application
object. VBA’s Open statement is used to open a file for reading or writing whereas the Open
method for the Application object actually opens the file.

Inside Out

Opening a Text File
Before you are able to read or write to a file, you must open it. The Open statement is quite
versatile, and the syntax can be a challenge.
Open pathname For mode [Access access][lock]As[#]filenumber _
[Len=reclength]
● pathname A required element that contains the name and path of the file to be
opened.
● mode A required element that specifies which mode the file will be using, such as
Append, Input, Output, Binary, or Random.

Note The VBA Help file for the mode parameter says that it’s required but that if you
leave it out, Excel will assume the mode is Random. We weren’t able to resolve the con­
tradiction, so the authors’ advice is to always set the parameter.

● access Specifies the file operation as Read, Write, or Read Write.
● lock Specifies the file status as Shared, Lock Read, Lock Write, or Lock Read Write.
● filenumber A required element that sets the file number ranging from 1 to 511. The
FreeFile function can be used to assign the next available number.
● reclength Sets the record length for random access files or the buffer size for
sequential access files.

295
Part 4: Advanced VBA