|
Before performing file processing, the first action you
must take is to create a file. To support file creation, the VBA language
provides a procedure named Open. Its syntax is:
Open pathname For Output [Access access] [lock] As [#]FileNumber [Len=reclength]
The Open statement takes many arguments, some are
required and others are not. The Open (the name of the procedure)
word, the For Output expression, and the As # clause 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 database is (the database
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 and/or its path, the
mode is required. It specifies the actual action you want to perform,
such as creating a new file or only opening an existing one. It can be one
of the following keywords:
- Output: The file will be created and ready to receive regular
values
- Binary: The file will be created and ready to receive values
in binary format (as combinations of 1s and 0s)
- Append: If the file exists already, it will be opened and new
values can be added to the end
Here is an example of creating a file:
Private Sub cmdSave_Click()
Open "example.dat" For Output ...
End Sub
The access argument is optional. It specifies the
types of actions that will be performed on the file, such as writing values
to it or only reading existing values. It can have one of the following
values:
- Write: After a new file has been created, new values will be
written to it
- Read Write: When a new file has been created or an existing
file has been opened, values can be read from it or written to it
If you decide to specify the access, precede its
value with the Access keyword.
The lock argument is optional. It indicates how
the processor should behave while the file is being used. Its possible
values are:
- Shared: Other applications (actually called processes) can
access this file while the current application is accessing it
- Lock Write: Do not allow other applications (processes) to
access this file while the current application (process) is writing to
it
- Lock Read Write: Do not allow other applications (processes)
to access this file while the current application (process) is using it
On the right side of #, type a number, for the
FileNumber parameter, 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 numbers 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 parameter is optional. If the file
was opened, this parameter 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 language
provides a procedure named Close. Its syntax is:
Close [FileNumberList]
The FileNumberList argument 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 language provides two procedures. One of them
is called Print and its syntax is:
Print #FileNumber, [OutputList]
The Print statement takes two arguments but only
the first is required.
The FileNumber argument is the FileNumber
parameter you would have used to create the file. The FileNumber
parameter is followed by a comma.
The OutputList argument can be made of 0, 1 or
more parts. Because it is optional, if you don't 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 argument and follow these rules:
- If you want to start the value with empty spaces, use the Spc()
function and pass an integer (in the parentheses) that represents the
number of empty spaces. For example Spc(4) would include 4 empty
spaces. This argument is optional, which means you can omit it
- Instead of a specific number of empty spaces, you can let the
operating system specify a built-in number of empty spaces. To do this,
call the Tab() function as part of your OutputList
argument. The Tab() function specifies the number of columns to
include before the value. The Tab() function can be more useful
if you are concerned with the alignment of the value(s) you will write
in the file.
This argument is optional
- To write a string, include it in double-quotes
- To write a number, whether an integer, a float, or a double, simply
include the number normally
- To write a Boolean value, type it as True or
False
- To write a date or time value, type it between # and # and follow
the rules of dates or times of your language such as US English
- To write a null value, type Null
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 the values with
either a semi-colon or an empty space. Here are examples:
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 language
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 argument is required. It must be
the FileNumber specified when creating the file.
The OutputList argument 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:
- To start the value with empty spaces, call the Spc() function
as mentioned for the Print statement
- The Tab() function is used as it was described for the
Print statement
- To write a string, include it in double-quotes
- To write a number, include it normally
- To write a Boolean value, type it as #TRUE# or #FALSE#
- To write a null value, type #NULL#
- To write a date or time value, type it between # and #
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 are
examples:
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: Saving a File
|
|
- Change the code of the Click event of the Save Repair Order as
follows:
Private Sub cmdSaveRepairOder_Click()
On Error GoTo cmdSaveRepairOder_Error
Dim Action As Integer
Dim dlgSaveFile As FileDialog
Set dlgSaveFile = Application.FileDialog(msoFileDialogSaveAs)
Action = dlgSaveFile.Show
If Action <> 0 Then
Open dlgSaveFile.SelectedItems(1) & ".cpr" For Output As #1
Write #1, txtCustomerName
Write #1, txtAddress
Write #1, txtCity
Write #1, txtState
Write #1, txtZIPCode
Write #1, txtMake
Write #1, txtModel
Write #1, txtCarYear
Write #1, txtProblemDescription
Write #1, txtPartName1
Write #1, txtUnitPrice1
Write #1, txtQuantity1
Write #1, txtSubTotal1
Write #1, txtPartName2
Write #1, txtUnitPrice2
Write #1, txtQuantity2
Write #1, txtSubTotal2
Write #1, txtPartName3
Write #1, txtUnitPrice3
Write #1, txtQuantity3
Write #1, txtSubTotal3
Write #1, txtPartName4
Write #1, txtUnitPrice4
Write #1, txtQuantity4
Write #1, txtSubTotal4
Write #1, txtPartName5
Write #1, txtUnitPrice5
Write #1, txtQuantity5
Write #1, txtSubTotal5
Write #1, txtJobName1
Write #1, txtJobCost1
Write #1, txtJobName2
Write #1, txtJobCost2
Write #1, txtJobName3
Write #1, txtJobCost3
Write #1, txtJobName4
Write #1, txtJobCost4
Write #1, txtJobName5
Write #1, txtJobCost5
Write #1, txtTotalParts
Write #1, txtTotalLabor
Write #1, txtTotalRepair
Write #1, txtRecommendations
Close #1
End If
Set dlgSaveFile = Nothing
cmdResetForm_Click
Exit Sub
cmdSaveRepairOder_Error:
MsgBox "There is a problem with the repair order. It cannot be saved.", _
vbOKOnly Or vbInformation, _
"College Park Auto Repair"
Resume Next
End Sub
- Close Microsoft Visual Basic
- Close the form
- When asked whether you want to save, click Yes
Instead of creating a new file, you may want to open an
existing file. To support this operation, the VBA language provides a
procedure named Open. Its syntax is:
Open PathName For Input [Access access] [lock] As [#]FileNumber [Len=RecordLength]
The Open word, the For Input expression,
and the As # combination 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
database 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
is required. To open a file, the mode argument can be:
- Binary: The file will be opened and its value(s) would be
read as (a) binary value(s)
- Append: The file will be opened and new values can be added
to the end of the existing values
- Input: The file will be opened normally
- Random: The file will be opened for random access
Here is an example of opening a file:
Private Sub cmdSave_Click()
Open "example.dat" For Input As #1
End Sub
The access argument is optional and can have one
of the following values:
- Read: After the file has been opened, values will be read
from it
- Read Write: Whether the file was created or opened, values
can be read from it and/or written to it
If you decide to specify the access argument,
precede its value with the Access keyword.
The lock parameter is optional and its possible
values are:
- Shared: Other applications can access this file while the
current application is accessing it
- Lock Read: Other applications are not allowed to access this
file while the current application is reading from it
- Lock Read Write: Other applications are not allowed to access
this file while the current application is using it
On the right side of #, type a number for the
FileNumber, between 1 and 511. Use the same rules/description we saw for
creating a file.
The RecordLength parameter is optional. If the
file was opened, this argument specifies the length of the record that was
read.
|
Practical
Learning: Introducing File Opening
|
|
- In the Navigation Pane, right-click the NewRepairOrder form and
click Copy
- Right-click an empty area in the Navigation Pane and click Paste
- Set the name of the form to OpenRepairOrder and click OK
- In the Navigation Pane, right-click the OpenRepairOrder form and
click Design View
- Change the design of the form as follows:
 |
| Control |
Name |
Caption |
Other Properties |
| Button |
 |
cmdOpenRepairOrder |
Open Repair Order |
|
| Button |
|
cmdSaveRepairOrder |
Save Repair Order |
Enabled: No |
| Button |
|
cmdClose |
Close |
|
|
- Double-click the button at the intersection of the rulers
- In the Properties window, click Event and double-click On Load
- Click the ellipsis button and change the file as follows:
Option Compare Database
Private strFileName As String
Private Sub Form_Load()
strFileName = ""
End Sub
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 language
provides two procedures. If you wrote the values using the Print
statement, to read the values, use the Input or the Line Input
statement. The syntax of the Input procedure is:
Input #FileNumber, VariableList
The Input statement takes two required arguments
but the second can be made of various parts.
The FileNumber argument is the FileNumber
parameter you would have used to open the file. The FileNumber is
followed by a comma.
The VariableList argument can be made of 1 or
more parts. To read only one value, after the comma of the FileNumber
argument, 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, VariableName
This statement takes two arguments 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 VBAYou are 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.
|
Practical
Learning: Reading From a File
|
|
- In the object combo box, select cmdOpenRepairOder
- Implement the event as follows:
Private Sub cmdOpenRepairOder_Click()
On Error GoTo cmdOpenRepairOder_Error
Dim Action As Integer
Dim dlgOpenFile As FileDialog
Dim CustomerName As String, Address As String
Dim City As String, State As String
Dim ZIPCode As String, Make As String
Dim Model As String, CarYear As String
Dim ProblemDescription As String
Dim PartName1 As String, UnitPrice1 As String
Dim Quantity1 As String, SubTotal1 As String
Dim PartName2 As String, UnitPrice2 As String
Dim Quantity2 As String, SubTotal2 As String
Dim PartName3 As String, UnitPrice3 As String
Dim Quantity3 As String, SubTotal3 As String
Dim PartName4 As String, UnitPrice4 As String
Dim Quantity4 As String, SubTotal4 As String
Dim PartName5 As String, UnitPrice5 As String
Dim Quantity5 As String, SubTotal5 As String
Dim JobName1 As String, JobCost1 As String
Dim JobName2 As String, JobCost2 As String
Dim JobName3 As String, JobCost3 As String
Dim JobName4 As String, JobCost4 As String
Dim JobName5 As String, JobCost5 As String
Dim TotalParts As String, TotalLabor As String
Dim TotalRepair As String, Recommendations As String
Set dlgOpenFile = Application.FileDialog(msoFileDialogOpen)
Action = dlgOpenFile.Show
If Action <> 0 Then
Open dlgOpenFile.SelectedItems(1) For Input As #1
strFileName = dlgOpenFile.SelectedItems(1)
Caption = "College Park Auto-Repair - Open Repair Order: " & strFileName
cmdSaveRepairOrder.Enabled = True
Input #1, CustomerName
Input #1, Address
Input #1, City
Input #1, State
Input #1, ZIPCode
Input #1, Make
Input #1, Model
Input #1, CarYear
Input #1, ProblemDescription
Input #1, PartName1
Input #1, UnitPrice1
Input #1, Quantity1
Input #1, SubTotal1
Input #1, PartName2
Input #1, UnitPrice2
Input #1, Quantity2
Input #1, SubTotal2
Input #1, PartName3
Input #1, UnitPrice3
Input #1, Quantity3
Input #1, SubTotal3
Input #1, PartName4
Input #1, UnitPrice4
Input #1, Quantity4
Input #1, SubTotal4
Input #1, PartName5
Input #1, UnitPrice5
Input #1, Quantity5
Input #1, SubTotal5
Input #1, JobName1
Input #1, JobCost1
Input #1, JobName2
Input #1, JobCost2
Input #1, JobName3
Input #1, JobCost3
Input #1, JobName4
Input #1, JobCost4
Input #1, JobName5
Input #1, JobCost5
Input #1, TotalParts
Input #1, TotalLabor
Input #1, TotalRepair
Input #1, Recommendations
If CustomerName = "#NULL#" Then
txtCustomerName = ""
Else
txtCustomerName = CustomerName
End If
If Address = "#NULL#" Then
txtAddress = ""
Else
txtAddress = Address
End If
If City = "#NULL#" Then
txtCity = ""
Else
txtCity = City
End If
If State = "#NULL#" Then
txtState = ""
Else
txtState = State
End If
If ZIPCode = "#NULL#" Then
txtZIPCode = ""
Else
txtZIPCode = ZIPCode
End If
If Make = "#NULL#" Then
txtMake = ""
Else
txtMake = Make
End If
If Model = "#NULL#" Then
txtModel = ""
Else
txtModel = Model
End If
If CarYear = "#NULL#" Then
txtCarYear = ""
Else
txtCarYear = CarYear
End If
If ProblemDescription = "#NULL#" Then
txtProblemDescription = ""
Else
txtProblemDescription = ProblemDescription
End If
If PartName1 = "#NULL#" Then
txtPartName1 = ""
Else
txtPartName1 = PartName1
End If
If UnitPrice1 = "#NULL#" Then
txtUnitPrice1 = ""
Else
txtUnitPrice1 = UnitPrice1
End If
If Quantity1 = "#NULL#" Then
txtQuantity1 = ""
Else
txtQuantity1 = Quantity1
End If
If SubTotal1 = "#NULL#" Then
txtSubTotal1 = ""
Else
txtSubTotal1 = SubTotal1
End If
If PartName2 = "#NULL#" Then
txtPartName2 = ""
Else
txtPartName2 = PartName2
End If
If UnitPrice2 = "#NULL#" Then
txtUnitPrice2 = ""
Else
txtUnitPrice2 = UnitPrice2
End If
If Quantity2 = "#NULL#" Then
txtQuantity2 = ""
Else
txtQuantity2 = Quantity2
End If
If SubTotal2 = "#NULL#" Then
txtSubTotal2 = ""
Else
txtSubTotal2 = SubTotal2
End If
If PartName3 = "#NULL#" Then
txtPartName3 = ""
Else
txtPartName3 = PartName3
End If
If UnitPrice3 = "#NULL#" Then
txtUnitPrice3 = ""
Else
txtUnitPrice3 = UnitPrice3
End If
If Quantity3 = "#NULL#" Then
txtQuantity3 = ""
Else
txtQuantity3 = Quantity3
End If
If SubTotal3 = "#NULL#" Then
txtSubTotal3 = ""
Else
txtSubTotal3 = SubTotal3
End If
If PartName4 = "#NULL#" Then
txtPartName4 = ""
Else
txtPartName4 = PartName4
End If
If UnitPrice4 = "#NULL#" Then
txtUnitPrice4 = ""
Else
txtUnitPrice4 = UnitPrice4
End If
If Quantity4 = "#NULL#" Then
txtQuantity4 = ""
Else
txtQuantity4 = Quantity4
End If
If SubTotal4 = "#NULL#" Then
txtSubTotal4 = ""
Else
txtSubTotal4 = SubTotal4
End If
If PartName5 = "#NULL#" Then
txtPartName5 = ""
Else
txtPartName5 = PartName5
End If
If UnitPrice5 = "#NULL#" Then
txtUnitPrice5 = ""
Else
txtUnitPrice5 = UnitPrice5
End If
If Quantity5 = "#NULL#" Then
txtQuantity5 = ""
Else
txtQuantity5 = Quantity5
End If
If SubTotal5 = "#NULL#" Then
txtSubTotal5 = ""
Else
txtSubTotal5 = SubTotal5
End If
If JobName1 = "#NULL#" Then
txtJobName1 = ""
Else
txtJobName1 = JobName1
End If
If JobCost1 = "#NULL#" Then
txtJobCost1 = ""
Else
txtJobCost1 = JobCost1
End If
If JobName2 = "#NULL#" Then
txtJobName2 = ""
Else
txtJobName2 = JobName2
End If
If JobCost2 = "#NULL#" Then
txtJobCost2 = ""
Else
txtJobCost2 = JobCost2
End If
If JobName3 = "#NULL#" Then
txtJobName3 = ""
Else
txtJobName3 = JobName3
End If
If JobCost3 = "#NULL#" Then
txtJobCost3 = ""
Else
txtJobCost3 = JobCost3
End If
If JobName4 = "#NULL#" Then
txtJobName4 = ""
Else
txtJobName4 = JobName4
End If
If JobCost4 = "#NULL#" Then
txtJobCost4 = ""
Else
txtJobCost4 = JobCost4
End If
If JobName5 = "#NULL#" Then
txtJobName5 = ""
Else
txtJobName5 = JobName5
End If
If JobCost5 = "#NULL#" Then
txtJobCost5 = ""
Else
txtJobCost5 = JobCost5
End If
If TotalParts = "#NULL#" Then
txtTotalParts = ""
Else
txtTotalParts = TotalParts
End If
If TotalLabor = "#NULL#" Then
txtTotalLabor = ""
Else
txtTotalLabor = TotalLabor
End If
If TotalRepair = "#NULL#" Then
txtTotalRepair = ""
Else
txtTotalRepair = TotalRepair
End If
If Recommendations = "#NULL#" Then
txtRecommendations = ""
Else
txtRecommendations = Recommendations
End If
Close #1
End If
Set dlgOpenFile = Nothing
Exit Sub
cmdOpenRepairOder_Error:
MsgBox "There is a problem with the repair order. It cannot be opened.", _
vbOKOnly Or vbInformation, _
"College Park Auto Repair"
Resume Next
End Sub
- In the Object combo box, select cmdSaveRepairOrder
- Implement the Click event as follows:
Private Sub cmdSaveRepairOrder_Click()
On Error GoTo cmdSaveRepairOder_Error
Dim Action As Integer
If strFileName <> "" Then
Open strFileName For Output As #1
Write #1, txtCustomerName
Write #1, txtAddress
Write #1, txtCity
Write #1, txtState
Write #1, txtZIPCode
Write #1, txtMake
Write #1, txtModel
Write #1, txtCarYear
Write #1, txtProblemDescription
Write #1, txtPartName1
Write #1, txtUnitPrice1
Write #1, txtQuantity1
Write #1, txtSubTotal1
Write #1, txtPartName2
Write #1, txtUnitPrice2
Write #1, txtQuantity2
Write #1, txtSubTotal2
Write #1, txtPartName3
Write #1, txtUnitPrice3
Write #1, txtQuantity3
Write #1, txtSubTotal3
Write #1, txtPartName4
Write #1, txtUnitPrice4
Write #1, txtQuantity4
Write #1, txtSubTotal4
Write #1, txtPartName5
Write #1, txtUnitPrice5
Write #1, txtQuantity5
Write #1, txtSubTotal5
Write #1, txtJobName1
Write #1, txtJobCost1
Write #1, txtJobName2
Write #1, txtJobCost2
Write #1, txtJobName3
Write #1, txtJobCost3
Write #1, txtJobName4
Write #1, txtJobCost4
Write #1, txtJobName5
Write #1, txtJobCost5
Write #1, txtTotalParts
Write #1, txtTotalLabor
Write #1, txtTotalRepair
Write #1, txtRecommendations
Close #1
End If
Exit Sub
cmdSaveRepairOder_Error:
MsgBox "There is a problem with the repair order. It cannot be saved.", _
vbOKOnly Or vbInformation, _
"College Park Auto Repair"
Resume Next
End Sub
- Close Microsoft Visual Basic
- Close the form
- When asked whether you save, click Yes
|
Other Techniques of Opening 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.
|
|
