File processing is the ability to save values from an
application and be able to get those values back when needed. The VBA language
supports file processing.
Before performing file processing, the first action you must
perform consists of creating a file. To support file creation, the VBA provides
a procedure named Open. Its syntax is:
Open pathname For Output [Access access] [lock] As [#]filenumber [Len=reclength]
The Open statement takes many factors, some are required and
others are not. The Open (the name of the procedure) word, the For
Output expression,
and the As # expression are required.
The first argument, pathname, is required. This is a
string that can be the name of the file. The file can have an extension or not.
Here is an example:
Open "example.dat"
If you specify only the name of the file, it would be
considered in the same folder where the current workbook is (the workbook that was
opened when you called this statement). If you want, you can provide a complete
path for the file.
This would include the drive, the (optional) folder(s), up to
the name of the file, with or without extension.
Besides the name of the file or its path, the mode
factor is required. This factor specifies the actual action you want to perform,
such as creating a new file or only opening an existing one. This factor can be one
of the following keywords:
Here is an example of creating a file:
Private Sub cmdSave_Click()
Open "example.dat" For Output As #1
End Sub
The access factor is optional. It specifies what
types of actions will be performed in the file, such as writing values to it or
only reading existing values. This factor can have one of the following values:
If you decide to specify the access factor, precede
its value with the Access keyword.
The lock factor is optional. It indicates how the
processor should behave while the file is being used. Its possible values are:
On the right side of #, type a number, for the filenumber
factor, between 1 and 511. If you are working on one file, use the number 1. If
you are working on many files, you should use an incremental number. If you have
not been keeping track of the number or you get confused at one time, to know
the next number you can use, call the FreeFile() function, which returns
the next available number in the sequence.
The reclength factor is optional. If the file was
opened, this factor specifies the length of the record that was read.
When you create a file and start using it, or after opening
a file and while you are using it, it uses memory and consumes (or can be
consuming) memory (which could be significant). When you have finished using the
file, you should free the memory it was using and release the resources it was
consuming. To assist you with this, the VBA provides a procedure named Close.
Its syntax is:
Close [filenumberlist]
The filenumberlist factor is the filenumber
you would have previously used to create or open the file.
Here is an example of closing a file:
Private Sub cmdSave_Click()
Open "example.dat" For Output As #1
Close #1
End Sub
After creating a file, you may want to write values to it.
To support this, the VBA provides two procedures. One of them is called Print
and its syntax is:
Print #filenumber, [outputlist]
The Print statement takes two factors but only the
first is required.
The filenumber factor is the filenumber you
would have used to create the file. The filenumber is followed by a
comma.
The outputlist factor can be made of 0, 1 or more
parts. Because it is optional, if you do not want to write a value to the file,
leave this part empty. If you want to write a value, type a comma after the filenumber
factor and follow these rules:
Here is an example of writing some values:
Private Sub cmdSave_Click()
Open "Employee.txt" For Output As #1
Print #1, "James"
Print #1, "Larenz"
Print #1, True
Print #1, #12/08/2008#
Close #1
End Sub
Instead of writing one value per line, you can write more
than one value with one statement. To do this, separate them with either a
semi-colon or an empty space. Here is an example:
Private Sub cmdSave_Click()
Open "Employee.txt" For Output As #1
REM The values are separated by a semi-colon
Print #1, "James"; "Larenz"
REM The values are separated by an empty space
Print #1, True #12/08/2008#
Close #1
End Sub
Besides the Print procedure, the VBA also provides a
procedure named Write that can be used to write one or more values to a file. The syntax of
the Write statement is the same as that of Print:
Write #filenumber, [outputlist]
The filenumber factor is required. It must be the filenumber
specified when creating the file.
The outputlist factor is optional. If you want to
skip it, type a comma after the filenumber and end the Write
statement. In this case, an empty line would be written to the file. To write
the values to the file, follow these rules:
Here is an example of writing some values:
Private Sub cmdSave_Click()
Open "Employee.txt" For Output As #1
Write #1, "James"
Write #1, "M"
Write #1, "Larenz"
Write #1, #12/08/2008#
Write #1, 24.50
Write #1, True
Close #1
End Sub
You can also write values on the same line. To do this, separate them with an empty
space, a comma, or a semi-colon. Here is an example:
Private Sub cmdSave_Click()
Open "Employee.txt" For Output As #1
REM The values are separated by a semi-colon
Write #1, "James"; "M"; "Larenz"
REM The values are separated by a comma
Write #1, #12/08/2008#, 24.50
Write #1, True
Close #1
End Sub
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Practical
Learning: Introducing File Processing
Instead of creating a new file, you may want to open an existing
file. To support this operation, the VBA provides
a procedure named Open. Its syntax is:
Open pathname For Input [Access access] [lock] As [#]filenumber [Len=reclength]
The Open procedure takes many arguments, some are required and
others are not. The Open word, For Input expression,
and the As # expression are required.
|
|
|
The first argument, pathname, is required. This is a
string that can be the name of the file. The file can have an extension or not.
Here is an example:
Open "example.dat"
If you specify only the name of the file, the interpreter would
look for the file in the same folder where the current workbook is. If you want, you can provide a complete
path for the file. This would include the drive, the (optional) folder(s), up to
the name of the file, with or without extension.
Besides the name of the file or its path, the mode
factor is required. To open a file, the mode factor can be:
Here is an example of opening a file:
Private Sub cmdSave_Click()
Open "example.dat" For Input As #1
End Sub
The access factor is optional. This factor can have one of the following values:
If you decide to specify the access factor, precede
its value with the Access keyword.
The lock factor is optional and its possible values can
be:
On the right side of #, type a number, for the filenumber
factor, between 1 and 511. Use the same rules/description we saw for creating a
file.
The reclength factor is optional. If the file was
opened, this factor specifies the length of the record that was read.
After opening a file, you can read values from it. Before
reading the value(s), you should declare one or more variables that would
receive the values to be read. Remember that the idea of using a variable is to
reserve a memory space where you can store a value. In the same way, when reading
a value from a file, you would get the value from the file and then store that
value in the computer memory. A variable would make it easy for you to refer to
that value when necessary.
To support the ability to open a file, the VBA provides two
procedures. If you wrote the values using the Print statement, to read
the values, use the Input or the Line Input statement (using Input
or Line Input is only a suggestion, not a rule). The syntax of the Input
procedure is:
Input #filenumber, varlist
The Input statement takes two required factors but
the second can be made of various parts.
The filenumber factor is the filenumber you
would have used to open the file. The filenumber is followed by a comma.
The varlist factor can be made of 1 or more parts. To
read only one value, after the comma of the filenumber factor, type the
name of the variable that will receive the value. Here is an example:
Private Sub cmdOpen_Click()
Dim FirstName As String
Open "Employee.txt" For Input As #1
Input #1, FirstName
Close #1
End Sub
In the same way, you can read each value on its own
line. One of the better uses of the Input statement is the ability to
read many values using a single statement. To do this, type the variables on the
same Input line but separate them with commas. Here is an example:
Private Sub cmdOpen_Click()
Dim FirstName As String
Dim LastName As String
Dim IsFullTimeEmployee As Boolean
Open "Employee.txt" For Input As #1
Input #1, FirstName, LastName, IsFullTimeEmployee
Close #1
End Sub
If you have a file that contains many lines, to read one
line at a time, you can use the Line Input statement. Its syntax is:
Line Input #filenumber, varname
This statement takes two factors and both are required. The filenumber
is the number you would have used to open the file. When the Line Input
statement is called, it reads a line of text until it gets to the end of the
file. One of the limitations of the Line Input statement is that it has a hard
time reading anything other than text because it may not be able to determine
where the line ends.
When reviewing the ability to write values to a file, we saw
that the Print statement writes a Boolean value as True or False.
If you use the Input statement to read such a value, the interpreter may
not be able to read the value. We saw that an alternative to the Print
statement was Write. We saw that, among the differences between Print and
Write, the latter writes Boolean values using the # symbol. This makes it
possible for the interpreter to easily read such a value. For these reasons, in
most cases, it may be a better idea to prefer using the Write statement
when writing values other than strings to a file.
Besides calling the Show() method of the FileDialog
class, the Application class provides its own means of opening a file. To
support it, the Application class provides the FindFile() method. Its
syntax is:
Public Function Application.FindFile() As Boolean
If you call this method, the Open File dialog with its
default settings would come up. The user can then select a file and click open.
If the file is a workbook, it would be opened and its content displayed in
Microsoft Excel. If the file is text-based, or XML, etc, Microsoft Excel would
proceed to open or convert it.
|
No comments:
Post a Comment