Opening Files Under the Covers

So far this chapter has been primarily geared towards working with structured text files. Structured text files are usually used to move data between incongruent applications that do not have a built-in facility for sharing data with each other. As a developer, structured text files are likely to be the most frequent type of text file you work with. You saw earlier in this chapter that Excel has a native ability to open structured text files. The examples so far used Excel to open the text files. This is fine if you want or do not mind the end user viewing the contents of the text file. For those times when you want to examine or modify a text file without displaying it to the user ,you can open the text file under the covers using VBA.

Additionally, you may need to work with unstructured text files include text files that have a formalized structure—just not a structure that Excel knows how to work with. In order to work with unstructured text files, you need to open them and inspect or modify them using VBA.

In order to perform these feats, you need to learn how to use the text file functionality offered by VBA. VBA provides read/write support for text files. In particular, you need to be familiar with the following VBA language elements:

Open Enables input/output to a file.

Close Closes a file opened using Open.

FreeFile Returns an Integer representing the next file number available for use by Open. Input and Input # Returns a String containing text read from a file. LineInput Returns a String representing an entire line of text read from a file. Print Writes display-formatted data to a file. Write # Writes data to a file.

Open for Business

The first thing you need to do to work with any file is open it. Opening text files in VBA is a little different than opening a workbook in Excel or opening a text file in Excel. For starters, the fundamental concept of open is different. Previously in this chapter when a file was opened, it was opened and displayed visibly on the screen. In this section, when a file is opened, it is opened in memory only. Unless you take programmatic action on your own, the file will not be displayed on the screen, and the user (or the developer for that matter) gets no visual indication that the file is open.

The mechanics of opening a file are also different. This is primarily due to the convention used for referring to open files. Once opened, files are referred to by a number which you assign in the process of opening them.

To open a file, use the VBA Open statement. This is not the same thing as the Open method of the Workbooks object. The syntax of the Open statement is as follows.

Open Pathname For Mode [/Access] [Lock] As [#]Filenumber [RecLength] The following list describes the various parameters of Open.

Pathname This is a required parameter that specifies the name of the file to open. It may include a directory and drive. If you do not include a directory and drive, it uses the value given by the VBA function CurDir (CurDir returns the current path).

Mode Mode is a keyword specifying the mode that the file should be opened in. You can use Append, Binary, Input, Output, or Random. If Mode is unspecified, the file is opened in Random mode. For text files, you should use one of the sequential modes: Input, Output, or Append. Input mode restricts file operations to read only. Output mode creates a new file, overwriting any file in the directory that has the same filename. Append allows you to add text to the end of an existing file. Binary mode is for, well, binary files or any situation in which you need to read/write individual bytes. Random mode is usually used for files that contain fixed-length data structures.

Access Access is an optional parameter that specifies the kind of operations that you can perform on the file. You should specify one of these: Read, Write, or Read Write.

Lock Lock is an optional parameter that specifies the restrictions placed on the file if it is accessed by other processes. Lock can be Shared, Lock Read, Lock Write, or Lock Read Write.

Filenumber Filenumber is required and should be a value between 1 and 511 inclusively. It is best to use the FreeFile function to obtain the next available file number.

RecLength RecLength is an optional parameter that represents the record length when used with Random files, and the length of the buffer when used with sequential files.

I find VBA's Open statement to be rather awkward to use. Perhaps this is because I do not use it very often, or maybe it is just because the syntax of it is downright discomfited (much like the word discomfited itself). Most of the time, you can get the job done by modifying one of the simple forms shown in Listing 15.4.

0 0


  • Ippolito
    How to open structured text?
    6 years ago

Post a comment