In Microsoft Excel, instead of a worksheet,
you could create a form-based application that your users would use. If (since)
you have already used Windows-based applications, you are surely familiar with
data entry on a form, in which case you use Windows controls.

File processing is the ability to store the values of a
document in the computer so you can retrieve such values another time.

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.

Practical
Learning: Introducing File Processing

Start Microsoft Excel

Double-click Sheet1, type Switchboard

Double-click Sheet2 and type Employees

Double-click Sheet3 and type Customers

Click the next sheet tab (the Insert Worksheet)

Double-click the new sheet tab and type Cars

Click the next sheet tab (the Insert Worksheet)

Double-click the new sheet tab and type Rental Rates

Click the Switchboard tab

Press and hold Shift

Click the Rental Rates tab

Release Shift

Click Cell B2 and type Bethesda Car Rental

Click the Enter button

Format it as you see fit:

Click the Employees sheet tab

To save the workbook, press Ctrl + S

In the top combo box, select the C drive (or any drive you want)

Click the Create New Folder (Windows XP) or New Folder (Windows
Vista) button

Type Bethesda Car Rental as the name of the new folder and
press Enter

Make sure the new folder is selected.
Change the file name to Business Records

Private Sub txtEmployeeNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo txtEmployeeNumber_Error
' Check if the user left the Employee Number empty
If txtEmployeeNumber.Text = "" Then
' If so, put leave the Employee Name empty
txtEmployeeName.Text = ""
Else
' If the user entered a valid employee #, use the Microsoft Excel's
' VLOOKUP() function to get the corresponding employee name
' We are using the range of cells from B7 to E13 but you can use a
' range of your choice as long as it contains the employees records
txtEmployeeName.Text = _
Application.WorksheetFunction.VLookup(txtEmployeeNumber.Text, _
Worksheets(2).Range("B7:E13"), 4, False)
End If
Exit Sub
txtEmployeeNumber_Error:
' If the user entered an invalid employee #, put Unknown in the name
If Err.Number = 1004 Then
txtEmployeeNumber.Text = ""
txtEmployeeName.Text = "Unknown clerk"
End If
End Sub

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:

Output: The file will be created and ready to receive (normal)
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 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:

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 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:

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
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.

Closing a File

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

Printing to a File

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:

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 factor 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 factor. 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 factor is optional, which means you can omit it

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

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

Writing to a File

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:

To start the value with empty spaces, call the Spc() function and
pass a number that represents the number of empty spaces.
This factor is optional, which means you can omit it

To start the value with a specific number of columns, call the Tab()
function and pass the number of columns as argument.
This factor is optional, which means you can omit it

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: Saving a File

Display the form

Double-click the Save button

Implement its Click event as follows:

Private Sub cmdSave_Click()
On Error GoTo cmdSave_Error
Rem Make sure the user enters a valid employee number
If txtEmployeeNumber.Text = "" Then
MsgBox "You must enter a valid employee number."
Exit Sub
End If
Rem Make sure the user enters a valid car tag number
If txtTagNumber.Text = "" Then
MsgBox "You must enter a valid tag number."
Exit Sub
End If
Rem Make sure the user enters a valid customer
If txtDrvLicenseNbr.Text = "" Then
MsgBox "You must specify a valid car."
Exit Sub
End If
Open "C:\Bethesda Car Rental\" & txtReceiptNumber.Text & _
".bcr" For Output As #1
Write #1, txtEmployeeNumber.Text
Rem Some people would not include the Employee Name in
Rem the file because it is already stored in the workbook.
Rem But we will include it in our file
Write #1, txtEmployeeName.Text
Write #1, txtDrvLicenseNbr.Text
Rem Some people would not include the customer name, address,
Rem city, state, and ZIP code in the file because they are
Rem already part of a workbook.
Rem But we will include them in our file
Write #1, txtCustomerName.Text
Write #1, txtAddress.Text
Write #1, txtCity.Text
Write #1, txtState.Text
Write #1, txtZIPCode.Text
Write #1, txtStartDate.Text
Write #1, txtEndDate.Text
Write #1, txtTagNumber.Text
Write #1, cbxCarConditions.Text
Rem Some people would not include the car make, model,
Rem and year in the file because they are
Rem already stored in a workbook.
Rem But we will include them here
Write #1, txtMake.Text
Write #1, txtModel.Text
Write #1, txtCarYear.Text
Write #1, cbxTankLevels.Text
Write #1, txtMileageStart.Text
Write #1, txtMileageEnd.Text
Write #1, txtRateApplied.Text
Write #1, txtTaxRate.Text
Write #1, txtDays.Text
Write #1, txtTaxAmount.Text
Write #1, txtSubTotal.Text
Write #1, txtOrderTotal.Text
Write #1, "Car Rented"
Write #1, txtNotes.Text
Close #1
Exit Sub
cmdSave_Error:
MsgBox "There is a problem with the form. It cannot be saved."
Resume Next
End Sub

On the Standard toolbar, click the Save button

Return to Microsoft Excel and click the Switchboard tab sheet if
necessary

In the Developer tab of the Ribbon, in the Controls section, click
Insert

In the ActiveX Controls section, click Command Button

Click the worksheet

Right-click the new button and click Properties

In the properties window, change the following characteristics
(Name): cmdCreateRentalOrder
Caption: Create New Rental Order

In the Controls section of the Ribbon, click the Design Mode
button to uncheck it

Click the button to display the form

Enter some values for a rental order

Write down the receipt number on a piece of paper

Click the Save button

Click the Reset button

Enter some values for another rental order

Click the Save button

Close the form and return to Microsoft Visual Basic

aaa

Opening a File

Opening a File

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:

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 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 factor is optional. This factor 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 factor, precede
its value with the Access keyword.

The lock factor is optional and its possible values can
be:

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
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.

Practical
Learning: Introducing File Opening

Click the body of the form.
From the properties window, write down the values of the Height and
the Width properties

Click the body of the form

Press Ctrl + A to select all controls on the form

To add a new form, on the main menu, click Insert -> UserForm

In the Properties window, change the following characteristics:
(Name): frmRentalOrderReview
Caption: Car Rental - Order Processing - Rental Order Review

Private Sub CalculateRentalOrder()
Dim RateApplied As Double
Dim Days As Integer
Dim SubTotal As Double
Dim TaxRate As Double
Dim TaxAmount As Double
Dim OrderTotal As Double
' Check the value in the Rate Applied text box
' If there is no valid value, set the Rate Applied to 0
If txtRateApplied.Text = "" Then
RateApplied = 0
ElseIf Not IsNumeric(txtRateApplied.Text) Then
RateApplied = 0
Else
' Otherwise, get the rate applied
RateApplied = CDbl(txtRateApplied.Text)
End If
' We will let the employee enter the number of days the car was rented
' Check whether the employee entered a valid number
' If the number is not good, set the number of days to 0
If txtDays.Text = "" Then
Days = 0
ElseIf Not IsNumeric(txtDays.Text) Then
Days = 0
Else
' Otherwise, get the number of days
Days = CInt(txtDays.Text)
End If
If txtTaxRate.Text = "" Then
TaxRate = 0
ElseIf Not IsNumeric(txtTaxRate.Text) Then
TaxRate = 0
Else
TaxRate = CDbl(txtTaxRate.Text)
End If
' Calculate the things
SubTotal = RateApplied * Days
TaxAmount = SubTotal * TaxRate / 100
OrderTotal = SubTotal + TaxAmount
txtSubTotal.Text = FormatNumber(SubTotal)
txtTaxAmount.Text = FormatNumber(TaxAmount)
txtOrderTotal.Text = FormatNumber(OrderTotal)
End Sub
Private Sub txtRateApplied_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo txtRateApplied_Error
Call CalculateRentalOrder
Exit Sub
txtRateApplied_Error:
MsgBox "There is something wrong with the " & _
"value you entered for the rate applied"
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 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:

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.

In the Controls section of the Ribbon, click the Design Mode
button to uncheck it

Click the button to display the form

Click the Receipt # text box

Type a receipt number of one of the rental orders you created
earlier

Click the Open button

Select a different option in the order status combo box

Change the value of mileage end, the end date and the days

Click the Update and Save rental Order button

Select the number in the Receipt # text box

Type another receipt number you saved previously

Click the Open button

Select different values on the rental order:

Click the Update and Save rental Order button

Close the form and return to Microsoft Visual Basic

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.