Microsoft Access Database Development With VBA

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 LearningPractical Learning: Introducing File Processing

  1. Start Microsoft Access
  2. Click Blank Desktop Database
  3. In the File Name text box, type CollegeParkAutoRepair1 (if Microsoft Access had started already, click File, click New, and type the file name)
  4. Click Create
  5. Close the default table without saving it
  6. On the Ribbon, click Create and, in the Forms section, click Form Design
  7. Right-click the Form1 tab and click Save
  8. Type NewRepairOrder as the name of the form and click OK
  9. While the NewRepairOrder form is still in design view, design the form as follows:
     
    College Park Auto Repair - New Repair Order
    Control Name Caption Other Properties
    Label Label lblMainTitle College Park Auto Repair Fore Color: #FFFFFF
    Label Label lblMainTitleShadow College Park Auto Repair Fore Color: Text 1
    Option Group Option Group   Order Identification  
    Text Box Text Box txtCustomerName Customer Name:  
    Text Box Text Box txtAddress Address:  
    Text Box Text Box txtCity City:  
    Text Box Text Box txtState State:  
    Text Box Text Box txtZIPCode ZIP Code:  
    Text Box Text Box txtMake Make/Model:  
    Text Box Text Box txtModel    
    Text Box Text Box txtCarYear Year:  
    Text Box Text Box txtProblemDescription Problem Description:  
    Tab Control Tab Control      
    Page   pgePartsUsed Parts Used  
    Label Label   Part Name  
    Label Label   Unit Price  
    Label Label   Qty  
    Label Label   Sub-Total  
    Text Box Text Box txtPartName1    
    Text Box Text Box txtUnitPrice1   Format: Fixed
    Text Box Text Box txtQuantity1    
    Text Box Text Box txtSubTotal1   Format: Fixed
    Text Box Text Box txtPartName2    
    Text Box Text Box txtUnitPrice2   Format: Fixed
    Text Box Text Box txtQuantity2    
    Text Box Text Box txtSubTotal2   Format: Fixed
    Text Box Text Box txtPartName3    
    Text Box Text Box txtUnitPrice3   Format: Fixed
    Text Box Text Box txtQuantity3    
    Text Box Text Box txtSubTotal3   Format: Fixed
    Text Box Text Box txtPartName4    
    Text Box Text Box txtUnitPrice4   Format: Fixed
    Text Box Text Box txtQuantity4    
    Text Box Text Box txtSubTotal4   Format: Fixed
    Text Box Text Box txtPartName5    
    Text Box Text Box txtUnitPrice5   Format: Fixed
    Text Box Text Box txtQuantity5    
    Text Box Text Box txtSubTotal5   Format: Fixed
    Page   pgeJobsPerformed Jobs Performed  
    Label Label   Job Name  
    Label Label   Cost  
    Text Box Text Box txtJobName1    
    Text Box Text Box txtJobCost1   Format: Fixed
    Text Box Text Box txtJobName2    
    Text Box Text Box txtJobCost2   Format: Fixed
    Text Box Text Box txtJobName3    
    Text Box Text Box txtJobCost3   Format: Fixed
    Text Box Text Box txtJobName4    
    Text Box Text Box txtJobCost4   Format: Fixed
    Text Box Text Box txtJobName5    
    Text Box Text Box txtJobCost5   Format: Fixed
    Text Box Text Box txtTotalParts Total Parts:  
    Text Box Text Box txtTotalLabor Total Labor:  
    Text Box Text Box txtTotalRepair Total Repair:  
    Text Box Text Box txtCarYear Year:  
    Text Box Text Box txtRecommendations Recommendations:  
    Button Button cmdSaveRepairOrder Save Repair Order  
    Button Button cmdResetForm Reset Form  
    Button Button cmdClose Close  
  10. Save the form
  11. Right-click the Reset Form button and click Build Event
  12. In the Choose Builder dialog box, click Code Builder and click OK
  13. 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
  14. In the Object combo box, select txtUnitPrice1
  15. In the Procedure combo box, select LostFocus
  16. Implement the event as follows:
    Private Sub txtUnitPrice1_LostFocus()
        If (txtPartName1 <> "") And (txtUnitPrice1 <> "") Then
            txtQuantity1 = "1"
            txtSubTotal1 = txtUnitPrice1
            CalculateOrder
        End If
    End Sub
  17. In the Object combo box, select txtQuantity1
  18. In the Procedure combo box, select LostFocus
  19. Implement the event as follows:
    Private Sub txtQuantity1_LostFocus()
        If txtPartName1 <> "" Then
            txtSubTotal1 = CDbl(Nz(txtUnitPrice1)) * CInt(Nz(txtQuantity1))
            CalculateOrder
        End If
    End Sub
  20. In the Object combo box, select txtUnitPrice2
  21. In the Procedure combo box, select LostFocus
  22. Implement the event as follows:
    Private Sub txtUnitPrice2_LostFocus()
        If (txtPartName2 <> "") And (txtUnitPrice2 <> "") Then
            txtQuantity2 = "1"
            txtSubTotal2 = txtUnitPrice2
            CalculateOrder
        End If
    End Sub
  23. In the Object combo box, select txtQuantity2
  24. In the Procedure combo box, select LostFocus
  25. Implement the event as follows:
    Private Sub txtQuantity2_LostFocus()
        If txtPartName2 <> "" Then
            txtSubTotal2 = CDbl(Nz(txtUnitPrice2)) * CInt(Nz(txtQuantity2))
            CalculateOrder
        End If
    End Sub
  26. In the Object combo box, select txtUnitPrice3
  27. In the Procedure combo box, select On Lost Focus
  28. Implement the event as follows:
    Private Sub txtUnitPrice3_LostFocus()
        If (txtPartName3 <> "") And (txtUnitPrice3 <> "") Then
            txtQuantity3 = "1"
            txtSubTotal3 = txtUnitPrice3
            CalculateOrder
        End If
    End Sub
  29. In the Object combo box, select txtQuantity3
  30. In the Procedure combo box, select On Lost Focus
  31. Implement the event as follows:
    Private Sub txtQuantity3_LostFocus()
        If txtPartName3 <> "" Then
            txtSubTotal3 = CDbl(Nz(txtUnitPrice3)) * CInt(Nz(txtQuantity3))
            CalculateOrder
        End If
    End Sub
  32. In the Object combo box, select txtUnitPrice4
  33. In the Procedure combo box, select On Lost Focus
  34. Implement the event as follows:
    Private Sub txtUnitPrice4_LostFocus()
        If (txtPartName4 <> "") And (txtUnitPrice4 <> "") Then
            txtQuantity4 = "1"
            txtSubTotal4 = txtUnitPrice4
            CalculateOrder
        End If
    End Sub
  35. In the Object combo box, select txtQuantity4
  36. In the Procedure combo box, select On Lost Focus
  37. Implement the event as follows:
    Private Sub txtQuantity4_LostFocus()
        If txtPartName4 <> "" Then
            txtSubTotal4 = CDbl(Nz(txtUnitPrice4)) * CInt(Nz(txtQuantity4))
            CalculateOrder
        End If
    End Sub
  38. In the Object combo box, select txtUnitPrice5
  39. In the Procedure combo box, select On Lost Focus
  40. Implement the event as follows:
    Private Sub txtUnitPrice5_LostFocus()
        If (txtPartName5 <> "") And (txtUnitPrice5 <> "") Then
            txtQuantity5 = "1"
            txtSubTotal5 = txtUnitPrice5
            CalculateOrder
        End If
    End Sub
  41. In the Object combo box, select txtQuantity5
  42. In the Procedure combo box, select On Lost Focus
  43. Implement the event as follows:
    Private Sub txtQuantity5_LostFocus()
        If txtPartName5 <> "" Then
            txtSubTotal5 = CDbl(txtUnitPrice5) * CInt(txtQuantity5)
            CalculateOrder
        End If
    End Sub
  44. In the Object combo box, select txtJobPrice1
  45. In the Procedure combo box, select LostFocus
  46. Implement the event as follows:
    Private Sub txtJobCost1_LostFocus()
        If (Not IsNull(txtJobName1)) And (Not IsNull(txtJobName1)) Then
            CalculateOrder
        End If
    End Sub
  47. In the Object combo box, select txtJobPrice2
  48. In the Procedure combo box, select LostFocus
  49. Implement the event as follows:
    Private Sub txtJobCost2_LostFocus()
        CalculateOrder
    End Sub
  50. In the Object combo box, select txtJobPrice3
  51. In the Procedure combo box, select LostFocus
  52. Implement the event as follows:
    Private Sub txtJobCost3_LostFocus()
        CalculateOrder
    End Sub
  53. In the Object combo box, select txtJobPrice4
  54. In the Procedure combo box, select LostFocus
  55. Implement the event as follows:
    Private Sub txtJobCost4_LostFocus()
        CalculateOrder
    End Sub
  56. In the Object combo box, select txtJobPrice5
  57. In the Procedure combo box, select LostFocus
  58. 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 LearningPractical 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:

    Microsoft Office 15.0 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 LearningPractical 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 LearningPractical 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 LearningPractical 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:
     
    College Park Auto Repair - Opem Repair Order
    Control Name Caption Other Properties
    Button Button cmdOpenRepairOrder Open Repair Order  
    Button Button cmdSaveRepairOrder Save Repair Order Enabled: No
    Button 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 LearningPractical 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.

 
 
   
 

Previous Copyright © 2013 FunctionX, Inc. Next