File Processing

Introduction to Documents and Files

Introduction to File Processing

File processing consists of creating a document, filling it with the necessary values, and saving it as a file. File processing is also the ability to open an existing file and use it as necessary. Microsoft Office provides support for file processing by making it possible to create a regular document and save it as a file. As a result, almost any application that is a member of Microsoft Office family of applications can perform file processing.

As a member of the Microsoft Office family, Microsoft Access has all the tools and functions necessary to perform file processing.

Practical Learning: Introducing File Processing

1. Start Microsoft Access
2. Click Blank Desktop Database
3. In the File Name text box, type CollegeParkAutoRepair1
4. Click Create
5. On the Ribbon, click File and click Options
6. In the left list of the Access Options dialog box, click Current Database
7. In the main list, click Overlapping Windows
8. Click OK on the dialog box
9. Click OK on the message box
10. On the Ribbon, click File and click Close
11. In the list of files, click CollegeParkAutoRepair1
12. Close the default table without saving it
13. On the Ribbon, click Create and, in the Forms section, click Form Design
14. Right-click the Form1 tab and click Save
15. Type NewRepairOrder as the name of the form and click OK
16. While the NewRepairOrder form is still in design view, design the form as follows:

    

    Control		Name	Caption	Other Properties
    Label		lblMainTitle	College Park Auto Repair	Fore Color: #FFFFFF
    Label		lblMainTitleShadow	College Park Auto Repair	Fore Color: Text 1
    Option Group			Order Identification
    Text Box		txtCustomerName	Customer Name:
    Text Box		txtAddress	Address:
    Text Box		txtCity	City:
    Text Box		txtState	State:
    Text Box		txtZIPCode	ZIP Code:
    Text Box		txtMake	Make/Model:
    Text Box		txtModel
    Text Box		txtCarYear	Year:
    Text Box		txtProblemDescription	Problem Description:
    Tab Control
    Page		pgePartsUsed	Parts Used
    Label			Part Name
    Label			Unit Price
    Label			Qty
    Label			Sub-Total
    Text Box		txtPartName1
    Text Box		txtUnitPrice1		Format: Fixed
    Text Box		txtQuantity1
    Text Box		txtSubTotal1		Format: Fixed
    Text Box		txtPartName2
    Text Box		txtUnitPrice2		Format: Fixed
    Text Box		txtQuantity2
    Text Box		txtSubTotal2		Format: Fixed
    Text Box		txtPartName3
    Text Box		txtUnitPrice3		Format: Fixed
    Text Box		txtQuantity3
    Text Box		txtSubTotal3		Format: Fixed
    Text Box		txtPartName4
    Text Box		txtUnitPrice4		Format: Fixed
    Text Box		txtQuantity4
    Text Box		txtSubTotal4		Format: Fixed
    Text Box		txtPartName5
    Text Box		txtUnitPrice5		Format: Fixed
    Text Box		txtQuantity5
    Text Box		txtSubTotal5		Format: Fixed
    Page		pgeJobsPerformed	Jobs Performed
    Label			Job Name
    Label			Cost
    Text Box		txtJobName1
    Text Box		txtJobCost1		Format: Fixed
    Text Box		txtJobName2
    Text Box		txtJobCost2		Format: Fixed
    Text Box		txtJobName3
    Text Box		txtJobCost3		Format: Fixed
    Text Box		txtJobName4
    Text Box		txtJobCost4		Format: Fixed
    Text Box		txtJobName5
    Text Box		txtJobCost5		Format: Fixed
    Text Box		txtTotalParts	Total Parts:
    Text Box		txtTotalLabor	Total Labor:
    Text Box		txtTotalRepair	Total Repair:
    Text Box		txtCarYear	Year:
    Text Box		txtRecommendations	Recommendations:
    Button		cmdSaveRepairOrder	Save Repair Order
    Button		cmdResetForm	Reset Form
    Button		cmdClose	Close

17. Save the form
18. Right-click the Reset Form button and click Build Event
19. In the Choose Builder dialog box, click Code Builder and click OK
20. Implement the event as follows:

    Private Sub cmdResetForm_Click()
        txtCustomerName = ""
        txtAddress = ""
        txtCity = ""
        txtState = ""
        txtZIPCode = ""
        txtMake = ""
        txtModel = ""
        txtCarYear = ""
        txtProblemDescription = ""
        
        txtPartName1 = "": txtUnitPrice1 = ""
        txtQuantity1 = "": txtSubTotal1 = ""
        txtPartName2 = "": txtUnitPrice2 = ""
        txtQuantity2 = "": txtSubTotal2 = ""
        txtPartName3 = "": txtUnitPrice3 = ""
        txtQuantity3 = "": txtSubTotal3 = ""
        txtPartName4 = "": txtUnitPrice4 = ""
        txtQuantity4 = "": txtSubTotal4 = ""
        txtPartName5 = "": txtUnitPrice5 = ""
        txtQuantity5 = "": txtSubTotal5 = ""
        
        txtJobName1 = "": txtJobCost1 = ""
        txtJobName2 = "": txtJobCost2 = ""
        txtJobName3 = "": txtJobCost3 = ""
        txtJobName4 = "": txtJobCost4 = ""
        txtJobName5 = "": txtJobCost5 = ""
        
        txtTotalParts = "0.00"
        txtTotalLabor = "0.00"
        txtTotalRepair = "0.00"
        txtRecommendations = ""
    End Sub
    
    Private Sub CalculateOrder()
        Dim UnitPrice1 As Double, UnitPrice2 As Double, _
            UnitPrice3 As Double, UnitPrice4 As Double, _
            UnitPrice5 As Double
        Dim Quantity1 As Integer, Quantity2 As Integer, _
            Quantity3 As Integer, Quantity4 As Integer, _
            Quantity5 As Integer
        Dim SubTotal1 As Double, SubTotal2 As Double, _
            SubTotal3 As Double, SubTotal4 As Double, _
            SubTotal5 As Double
        Dim TotalParts As Double, TotalLabor As Double
        Dim JobCost1, JobCost2, JobCost3, JobCost4, JobCost5 As Double
                     
        If txtPartName1 = "" Then
            SubTotal1 = 0
        Else
            SubTotal1 = CDbl(Nz(txtUnitPrice1)) * CInt(Nz(txtQuantity1))
        End If
        
        If txtPartName2 = "" Then
            SubTotal2 = 0
        Else
            SubTotal2 = CDbl(Nz(txtUnitPrice2)) * CInt(Nz(txtQuantity2))
        End If
        
        If txtPartName3 = "" Then
            SubTotal3 = 0
        Else
            SubTotal3 = CDbl(Nz(txtUnitPrice3)) * CInt(Nz(txtQuantity3))
        End If
        
        If txtPartName4 = "" Then
            SubTotal4 = 0
        Else
            SubTotal4 = CDbl(Nz(txtUnitPrice4)) * CInt(Nz(txtQuantity4))
        End If
        
        If txtPartName5 = "" Then
            SubTotal5 = 0
        Else
            SubTotal5 = CDbl(Nz(txtUnitPrice5)) * CInt(Nz(txtQuantity5))
        End If
        
        If txtJobCost1 = "" Then
            JobCost1 = 0
        Else
            JobCost1 = CDbl(Nz(txtJobCost1))
        End If
        If txtJobCost2 = "" Then
            JobCost2 = 0
        Else
            JobCost2 = CDbl(Nz(txtJobCost2))
        End If
        If txtJobCost3 = "" Then
            JobCost3 = 0
        Else
            JobCost3 = CDbl(Nz(txtJobCost3))
        End If
        If txtJobCost4 = "" Then
            JobCost4 = 0
        Else
            JobCost4 = CDbl(Nz(txtJobCost4))
        End If
        If txtJobCost5 = "" Then
            JobCost5 = 0
        Else
            JobCost5 = CDbl(Nz(txtJobCost5))
        End If
        
        TotalParts = SubTotal1 + SubTotal2 + SubTotal3 + _
                     SubTotal4 + SubTotal5
        TotalLabor = JobCost1 + JobCost2 + JobCost3 + JobCost4 + JobCost5
        
        txtTotalParts = TotalParts
        txtTotalLabor = TotalLabor
        txtTotalRepair = FormatNumber(TotalParts + TotalLabor)
    End Sub

21. In the Object combo box, select txtUnitPrice1
22. In the Procedure combo box, select LostFocus
23. Implement the event as follows:

    Private Sub txtUnitPrice1_LostFocus()
        If (txtPartName1 <> "") And (txtUnitPrice1 <> "") Then
            txtQuantity1 = "1"
            txtSubTotal1 = txtUnitPrice1
            CalculateOrder
        End If
    End Sub

24. In the Object combo box, select txtQuantity1
25. In the Procedure combo box, select LostFocus
26. Implement the event as follows:

    Private Sub txtQuantity1_LostFocus()
        If txtPartName1 <> "" Then
            txtSubTotal1 = CDbl(Nz(txtUnitPrice1)) * CInt(Nz(txtQuantity1))
            CalculateOrder
        End If
    End Sub

27. In the Object combo box, select txtUnitPrice2
28. In the Procedure combo box, select LostFocus
29. Implement the event as follows:

    Private Sub txtUnitPrice2_LostFocus()
        If (txtPartName2 <> "") And (txtUnitPrice2 <> "") Then
            txtQuantity2 = "1"
            txtSubTotal2 = txtUnitPrice2
            CalculateOrder
        End If
    End Sub

30. In the Object combo box, select txtQuantity2
31. In the Procedure combo box, select LostFocus
32. Implement the event as follows:

    Private Sub txtQuantity2_LostFocus()
        If txtPartName2 <> "" Then
            txtSubTotal2 = CDbl(Nz(txtUnitPrice2)) * CInt(Nz(txtQuantity2))
            CalculateOrder
        End If
    End Sub

33. In the Object combo box, select txtUnitPrice3
34. In the Procedure combo box, select On Lost Focus
35. Implement the event as follows:

    Private Sub txtUnitPrice3_LostFocus()
        If (txtPartName3 <> "") And (txtUnitPrice3 <> "") Then
            txtQuantity3 = "1"
            txtSubTotal3 = txtUnitPrice3
            CalculateOrder
        End If
    End Sub

36. In the Object combo box, select txtQuantity3
37. In the Procedure combo box, select On Lost Focus
38. Implement the event as follows:

    Private Sub txtQuantity3_LostFocus()
        If txtPartName3 <> "" Then
            txtSubTotal3 = CDbl(Nz(txtUnitPrice3)) * CInt(Nz(txtQuantity3))
            CalculateOrder
        End If
    End Sub

39. In the Object combo box, select txtUnitPrice4
40. In the Procedure combo box, select On Lost Focus
41. Implement the event as follows:

    Private Sub txtUnitPrice4_LostFocus()
        If (txtPartName4 <> "") And (txtUnitPrice4 <> "") Then
            txtQuantity4 = "1"
            txtSubTotal4 = txtUnitPrice4
            CalculateOrder
        End If
    End Sub

42. In the Object combo box, select txtQuantity4
43. In the Procedure combo box, select On Lost Focus
44. Implement the event as follows:

    Private Sub txtQuantity4_LostFocus()
        If txtPartName4 <> "" Then
            txtSubTotal4 = CDbl(Nz(txtUnitPrice4)) * CInt(Nz(txtQuantity4))
            CalculateOrder
        End If
    End Sub

45. In the Object combo box, select txtUnitPrice5
46. In the Procedure combo box, select On Lost Focus
47. Implement the event as follows:

    Private Sub txtUnitPrice5_LostFocus()
        If (txtPartName5 <> "") And (txtUnitPrice5 <> "") Then
            txtQuantity5 = "1"
            txtSubTotal5 = txtUnitPrice5
            CalculateOrder
        End If
    End Sub

48. In the Object combo box, select txtQuantity5
49. In the Procedure combo box, select On Lost Focus
50. Implement the event as follows:

    Private Sub txtQuantity5_LostFocus()
        If txtPartName5 <> "" Then
            txtSubTotal5 = CDbl(txtUnitPrice5) * CInt(txtQuantity5)
            CalculateOrder
        End If
    End Sub

51. In the Object combo box, select txtJobPrice1
52. In the Procedure combo box, select LostFocus
53. Implement the event as follows:

    Private Sub txtJobCost1_LostFocus()
        If (Not IsNull(txtJobName1)) And (Not IsNull(txtJobName1)) Then
            CalculateOrder
        End If
    End Sub

54. In the Object combo box, select txtJobPrice2
55. In the Procedure combo box, select LostFocus
56. Implement the event as follows:

    Private Sub txtJobCost2_LostFocus()
        CalculateOrder
    End Sub

57. In the Object combo box, select txtJobPrice3
58. In the Procedure combo box, select LostFocus
59. Implement the event as follows:

    Private Sub txtJobCost3_LostFocus()
        CalculateOrder
    End Sub

60. In the Object combo box, select txtJobPrice4
61. In the Procedure combo box, select LostFocus
62. Implement the event as follows:

    Private Sub txtJobCost4_LostFocus()
        CalculateOrder
    End Sub

63. In the Object combo box, select txtJobPrice5
64. In the Procedure combo box, select LostFocus
65. Implement the event as follows:

    Private Sub txtJobCost5_LostFocus()
        CalculateOrder
    End Sub

Introductin to the File Dialog Box

There are various ways you can create a document in Microsoft Windows, depending on the type of document and sometimes depending on the application. In some cases, an application may be configured to save its file automatically but in most cases, you must explicitly indicate that you want to save the document. The reverse operation is to open a file that was created already.

To assist you saving a document or opening a file, Microsoft Windows provides a special dialog box. Before using the file dialog box, you must add a reference to the Microsoft Office Object Library.

To give you access to the File dialog box, the Office namespace of the Microsoft Office Object Library is equipped with a class named FileDialog. This class is mostly used to initialize the actual class that would handle the file operation. Before using it, you can declare a variable of type Office.FileDialog. This can be done as follows:

Private Sub cmdFileDialog_Click()
    Dim dlgFile As Office.FileDialog
End Sub

This can also be done as follows:

Private Sub cmdFileDialog_Click()
    Dim dlgFile As FileDialog
End Sub

The Application class is equiped with a method named FileDialog. You use that method to call the dialog box used to save or open a file. Actually, you must assign the call of that method to an Office.FileDialog variable you would have declared. This can be done as follows:

Private Sub cmdFileDialog1_Click()
    Dim dlgFile As Office.FileDialog
  
    Set dlgFile = Application.FileDialog(...)
End Sub

Once the dialog box is ready, you can display it to the user. To support this, the Office.FileDialog class is equipped with a method named Show. Its syntax is:

Function Show() As Long

This method doesn't take an argument. It returns a Long integer that indicates what button the user clicked. If the user clicks the default button (because the default button depends on the type of dialog box that is displaying, we will see what that default button could be) or presses Enter, the method returns -1. If the user clicks Cancel or presses Esc, the method returns 0.

The File dialog box serves many goals such as to save a file or to open a file. There are many other operations the File dialog can perform. In fact, when you call the Application.FileDialog() method, you must specify what you want it to do. Therefore, the syntax of that method is:

Application.FileDialog(TypeOfAction As MsoFileDialogType) As Office.FileDialog

The TypeOfAction argument is used to specify what actual dialog box to display.

Practical Learning: Introducing the File Dialog Box

1. On the main menu of Microsoft Visual Basic, click Tools -> References
2. Click the check box of Microsoft Office X.x Object Library:

   

3. Click OK

The Save File Dialog Box

 If you want to save a document, pass the argument of the Application.FileDialog() method as msoFileDialogSaveAs. Here is an example:

Private Sub cmdFileDialog_Click()
    Dim dlgSaveFile As FileDialog
    
    Set dlgSaveFile = Application.FileDialog(msoFileDialogSaveAs)
End Sub

Remember that, to display the dialog box to the user, call the Show() method and get its return value. You must still take (you have the responsibility of taking) appropriate actions to save the document. After all, the dialog box is there only as an accessory. It doesn't know why or how you want to save the file, nor what you want to actually save.

Practical Learning: Introducing File Saving

1. In the Object combo box, select cmdSaveRepairOrder
2. Implement the event as follows:

   Private Sub cmdSaveRepairOder_Click()
       Dim Action As Integer
       Dim dlgSaveFile As FileDialog
       
       Set dlgSaveFile = Application.FileDialog(msoFileDialogSaveAs)
   
       Action = dlgSaveFile.Show
       
       If Action = 0 Then
           MsgBox "The action was canceled. The file will not be saved"
       Else
           MsgBox "The file will be saved."
       End If
   End Sub

3. Switch to Microsoft Access and switch the form to Form View
4. Click the Save Repair Order button
5. Close or dismiss the dialog box and return to Microsoft Access
6. Switch the form back to Design View
7. Return to Microsoft Visual Basic

The Open File Dialog Box

As opposed to saving a document, Microsoft Windows provides the Open File dialog box used to open a file. To display it, call the FileDialog() method of the Application class and pass the argument as msoFileDialogOpen). Here is an example:

Private Sub cmdFileDialog_Click()
    Dim Action As Integer
    Dim dlgOpenFile As FileDialog
    
    Set dlgOpenFile = Application.FileDialog(msoFileDialogOpen)
    
    Action = dlgOpenFile.Show
End Sub

Remember that the Open File dialog is only an accessory. It doesn't specify what you are doing and why.

Creating a File

Introduction

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.

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

Writing to a File

Printing to a File

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

Writing Values

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

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

2. Close Microsoft Visual Basic
3. Close the form
4. When asked whether you want to save, click Yes

Reading From a File

Introduction

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

1. In the Navigation Pane, right-click the NewRepairOrder form and click Copy
2. Right-click an empty area in the Navigation Pane and click Paste
3. Set the name of the form to OpenRepairOrder and click OK
4. In the Navigation Pane, right-click the OpenRepairOrder form and click Design View
5. 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

6. Double-click the button at the intersection of the rulers
7. In the Properties window, click Event and double-click On Load
8. Click the ellipsis button and change the file as follows:

   Option Compare Database
   
   Private strFileName As String
   
   Private Sub Form_Load()
       strFileName = ""
   End Sub

Reading Values

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

1. In the object combo box, select cmdOpenRepairOder
2. 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

3. In the Object combo box, select cmdSaveRepairOrder
4. 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

5. Close Microsoft Visual Basic
6. Close the form
7. 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.

Practical Learning: Ending the Lesson

• Close Microsoft Access