Opening Files

The whole process begins with opening a file using the VBA Open function in the form

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

Table 20.1 lists this function's many arguments.

Table 20.1. Open Arguments

Argument	Explanation
pathname	The complete path to the file you want to open.
mode	One of the following: Append, Binary, Input, Output, or Random (the default).
access	One of the following: Read (the default), Write, or Read Write.
lock	One of the following: Shared (the default), Lock Read, Lock Write, or Lock Read Write.
filenumber	Variable that contains the results of a call to the FreeFile function. Use this value to identify the file.
recordlength	When using Random mode, it's the length of the record; when using Append or Output mode, it's the number of bytes buffered.

About mode

The mode argument determines how Windows handles the open file and what you can do to it. For the most part, you'll want to perform one of the following actions:

• To review data without editing it or adding to it, use Input mode. Files open in Input mode have read-only access.

• To add or edit values, use Append mode if you want to work with the existing file by adding values to the end of the file.

• To add or edit values, use Output mode when you want to delete the existing file and create a new one with the same name.

• To add or edit values byte by byte, open the file in Binary mode. (You'll mainly use this mode to work with image files.)

• To add or edit values to a file that depends on a fixed record length, open the file in Random mode, using the recordlength argument to specify the record's size.

About access

This argument has three self-explanatory options: Read, Write, and Read Write. Keep in mind that regardless of the option you try to pass, you must have appropriate permission through your system's security settings. For instance, if you try to access a file with Write-level permissions, but Windows security allows you read-only permissions on that file, your request will return an error. For that reason, you'll want to include plenty of error-handling routines in your I/O code.

About locking

In a single-user application, locking isn't an issue. In contrast, when working in a multiuser application, the default locking setting is Shared. That means other users can read and write to the file while it's open by someone else. The locking settings with a brief explanation follow:

• Shared Users with the proper permissions can retrieve values or add values to the file, regardless of who has the file open.

• Lock Read Users with the proper permissions can write to the file, but they can't retrieve values from a file open by someone else.

• Lock Write Users with the proper permissions can retrieve values, but they can't write values to a file open by someone else.

• Lock Read Write Users can't retrieve or write to a file open by someone else.

A Simple Open Example

The following sub procedure accepts a string argument that identifies the path to the file you want to open:

 Sub OpenFile(fil As String)   'Open a file using I/O.   Dim hFile As Long   hFile = FreeFile   Open fil For Input Access Read Shared As hFile   MsgBox fil & " = " & hFile   Close hFile End Sub 

In truth, this procedure really doesn't do anything other than open the file passed to the procedure via the fil argument. The file is opened with Read access using the Shared locking setting. That means you can retrieve data but not write to the file. In addition, any user can read and write to the file while you have it open (with the appropriate permissions of course).

You can test this procedure in TimeTrack.mdb and use Chapter 20's example module or enter the previous procedure into a standard module. In the Immediate window, run the following statement with the appropriate updates to accommodate your system:

 OpenFile "path\TimeTrack20.mdb" 

Be sure to update path accordingly to reflect the location of TimeTrack20.mdb on your own hard drive. After opening TimeTrack.mdb (or TimeTrack20.mdb, depending on the example setup you're using), the procedure displays a message box similar to that shown in Figure 20.1. The message box displays the file you opened with the allocated file handle number. Click OK to clear the message.

Figure 20.1. Now you know the file handle for the opened file.

CAUTION

When you're done with a file, always close it using the VBA Close statement. Otherwise, you might lose data or lock out other users.

Although this is an easy way to discern a file's file handle, it isn't particularly productive or efficient. In a working situation, refer to the hFile variable you don't need to know the actual number yourself.

TIP

When working with more than one file in an I/O arrangement, open a file using the value returned by FreeFile before calling FreeFile again. FreeFile continues to return the same value until you actually open a file. Without the proper sequence of events, you can inadvertently assign the same value to all the open files, which doesn't work of course.