Writing Data to a Text File

If you don’t have WinFax installed, when you open the sample database, you will see the error message shown in Figure 14.10, and the large button on the main menu will have the caption #Error, as shown in Figure 14.11.

Figure 14.10

Figure 14.11

If you open the References dialog from the Tools menu in the Visual Basic window, you will see the reference marked as “MISSING”, as shown in Figure 14.12. If you uncheck this reference, you will be able to select and use the Export Contacts to Outlook form for testing text file creation, but of course you won’t be able to send faxes.

Figure 14.12

Chapter 13, Working with Excel, covered exporting Access data to two different text formats—comma-delimited (.csv) and plain text (.txt). However, this is not the only way you can export to a text file from Access. Fossilized in Access VBA there are some antique DOS methods that you can use to create and write to text files. I like to use these methods to write information about exports, in particular to record any problems that result in Access records being skipped. Several of the earlier chapters used the Write # method to write information about skipped records to a text file. In this chapter, I expand the technique to write a line to a text file about every record selected for export, to give the user a complete record of the export, including both the records that were processed and those that were skipped.

Export Contacts to Outlook

The Export Contacts to Outlook form uses this technique. This form (shown in Figure 14.13) has a command button for selecting an Outlook folder (see Chapter 12, Working with Outlook, for an explanation of how this button works), a listbox for selecting contacts to export, and a command button to do the export (the command button is only enabled after selecting an Outlook folder).

Figure 14.13

When you click the Create Contact(s) button, the Click event procedure that follows runs. It is substantially similar to the procedure for exporting data to Outlook contacts on the Export to Outlook (Listbox) form in Chapter 12. The portions of the code related to creating and writing to the text file are commented in the code listed below.

 Private Sub cmdCreateContacts_Click() On Error GoTo ErrorHandler 

Declare Outlook, DAO, and text variables for use in exporting.

    Dim blnSomeSkipped As Boolean    Dim con As Outlook.ContactItem    Dim dbs As DAO.Database    Dim fldCalendar As Outlook.MAPIFolder    Dim fldContacts As Outlook.MAPIFolder    Dim i As String    Dim itm As Object    Dim lngContactID As Long    Dim nms As Outlook.NameSpace    Dim strBody As String    Dim strCity As String    Dim strCompanyName As String    Dim strContactName As String    Dim strCountry As String    Dim strDocName As String    Dim strDocsPath As String    Dim strDocType As String    Dim strEMailRecipient As String    Dim strFile As String    Dim strFullName As String    Dim strJobTitle As String    Dim strMessage As String    Dim strName As String    Dim strNameAndJob As String    Dim strPostalCode As String    Dim strPrompt As String    Dim strSalutation As String    Dim strShortDate As String    Dim strStateProv As String    Dim strStreetAddress As String    Dim strTest As String    Dim strTestFile As String    Dim strText As String    Dim strTextFile As String    Dim strTitle As String    Dim txt As Access.TextBox    Dim varItem As Variant 

Check that at least one record has been selected.

    Set lst = Me![lstSelectMultiple]    If lst.ItemsSelected.Count = 0 Then       MsgBox "Please select at least one record."       lst.SetFocus       GoTo ErrorHandlerExit    Else       intColumns = lst.ColumnCount       intRows = lst.ItemsSelected.Count    End If 

Set Outlook application variable; if Outlook is not running, the error handler defaults to CreateObject.

    Set gappOutlook = GetObject(, "Outlook.Application") 

Set a reference to the textbox for writing information about exported or skipped records.

    Set txt = Me![txtExportProgress]    If CheckDocsDir = False Then       GoTo ErrorHandlerExit    End If    strDocsPath = GetDocsDir 

Open a text file in the \My Documents\Access Merge folder for writing information about exported or skipped records.

    strFile = strDocsPath & "Export Progress.txt"    Debug.Print "Text file: " & strFile    Open strFile For Output As #1 

Write header information to the text file.

    Print #1, "Information on progress exporting contacts to Outlook" 

The following command creates a blank line in the text file.

    Print #1, 

Write the Outlook folder name to the text file.

    Print #1, "Target folder: " & pfld.Name    Print #1,    Print #1,        blnSomeSkipped = False    strText = ""        For Each varItem In lst.ItemsSelected       ‘Get Contact ID for reference       lngContactID = Nz(lst.Column(0, varItem))       Debug.Print "Contact ID: " & lngContactID              ‘Check for required information       strTest = Nz(lst.Column(1, varItem))       Debug.Print "Contact name: " & strTest       If strTest = "" Then 

If the contact name is missing, write information on this to the textbox and the text file.

          blnSomeSkipped = True          strText = "Contact No. " & lngContactID & " skipped; no name"          Print #1,          Print #1, strText 

For writing to the textbox, append the new text to the existing text, and remove the leading CR + LF (Chr(10) & Chr(13)) so there won’t be a blank line before the first entry.

          strText = txt.Value & vbCrLf & strText          If Asc(Left(strText, 1)) = 10 Then             strText = Mid(strText, 2)          End If          txt.Value = strText          GoTo NextItemContact       Else          strFullName = Nz(lst.Column(7, varItem))       End If              strEMailRecipient = Nz(lst.Column(12, varItem))       strTest = strEMailRecipient       Debug.Print "Email address: " & strTest       If strTest = "" Then 

If the email address is missing, write information on this to the textbox and the text file.

          blnSomeSkipped = True          strText = "Contact No. " & lngContactID & " ("              & strFullName & ") skipped; no email address"          Print #1,          Print #1, strText          strText = txt.Value & vbCrLf & strText          If Asc(Left(strText, 1)) = 10 Then             strText = Mid(strText, 2)          End If          txt.Value = strText          GoTo NextItemContact       End If              strStreetAddress = Nz(lst.Column(2, varItem))       strTest = strStreetAddress       Debug.Print "Street address: " & strTest       If strTest = "" Then 

If the street address is missing, write information on this to the textbox and the text file.

          blnSomeSkipped = True          strText = "Contact No. " & lngContactID & " ("              & strFullName & ") skipped; no street address"          Print #1,          Print #1, strText          strText = txt.Value & vbCrLf & strText          Debug.Print Asc(Left(strText, 1))          If Asc(Left(strText, 1)) = 13 Then             strText = Mid(strText, 3)          End If          txt.Value = strText          GoTo NextItemContact       End If 

All required information is present; create a contact.

Write a line about the creation of the contact to the textbox and the text file.

       strText = "Contact No. " & lngContactID & " (" & strFullName           & ") has all required information; creating an Outlook contact"       Print #1,       Print #1, strText       strText = txt.Value & vbCrLf & strText       txt.Value = strText              strJobTitle = Nz(lst.Column(10, varItem))       strCity = Nz(lst.Column(3, varItem))       strStateProv = Nz(lst.Column(4, varItem))       strPostalCode = Nz(lst.Column(5, varItem))       strCountry = Nz(lst.Column(6, varItem))       strCompanyName = Nz(lst.Column(9, varItem))       strSalutation = Nz(lst.Column(11, varItem)) 

Create a new contact item in default local Contacts folder.

       Set gappOutlook = GetObject(, Outlook.Application)       Set con = gappOutlook.CreateItem(olContactItem)       With con          .CustomerID = lngContactID          .FullName = strFullName          .JobTitle = strJobTitle          .BusinessAddressStreet = strStreetAddress          .BusinessAddressCity = strCity          .BusinessAddressState = strStateProv          .BusinessAddressPostalCode = strPostalCode          .BusinessAddressCountry = strCountry          .CompanyName = strCompanyName          .NickName = strSalutation          .Email1Address = strEMailRecipient          .Close (olSave)       End With NextItemContact:    Next varItem ErrorHandlerExit: 

Close the text file.

    Close #1    Exit Sub ErrorHandler:    If Err = 429 Then       ‘Outlook is not running; open Outlook with CreateObject.       Set gappOutlook = CreateObject("Outlook.Application")       Resume Next    Else       MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description       Resume ErrorHandlerExit    End If     End Sub 

The form also has a textbox that lists the same data that is written to the text file. You can see information about the export in the textbox in Figure 14.14.

Figure 14.14

If you are exporting only a few records, you can see all the data you need right in the textbox. However, when more than a few contacts are selected for export, you will need to refer to the text file for full information on the export. Figure 14.15 shows the text file produced by an export open in Notepad.

Figure 14.15