CREATING AN EXPORT DIALOG TO EXPORT AN APPLICATION'S TABLES

Many times, it's a good idea to give users—or at least the administrator—an option for exporting tables. The point is to allow them to do so without the hassle of having them get to the database container.

To take this a step further, how about giving users the choice of exporting to various file formats? Sometimes it can be a challenge to give users the flexibility they need with the control that's necessary.

Looking at What the Export Utility Demonstrates

The ap_SystemUtilities form can export various tables in the applications. Figure 28.1 shows this form with multiple tables selected to export.

This example of exporting tables does the following:

• It uses the multi-select list box feature and shows how to code for it.

• It allows users to get or set a default export type by using custom properties off the database container.

• It lets users choose from a list of different file types (including exports to various database formats, spreadsheets, and text files).

Examining the Parts That Make Up the Export Utility

The Export utility uses a table and a form. The table, tblSharedTables, was originally used for linking tables in Chapter 26, “Startup Checking System Routines Using DAO.” It's used again in this chapter to track a DOS filename for each exportable table in the application. You can see the tblSharedTables table in Figure 28.2.

The ap_SystemUtilities form, used for this example, has a list box named lboTablesToExport, which allows users to select which tables they want to export. The list box uses a feature that allows multiple entries to be highlighted. Multiple-selection list boxes are covered in more detail in Chapter 10, “Expanding the Power of Your Forms with Controls.”

The lboTablesToExport list box uses the SharedTables table as its row source. Figure 28.3 shows the property sheet of lboTablesToExport with the rest of the ap_SystemUtilities form.

The control used to allow users to choose the export file type is a combo box named cboExportFileType. This combo box has a list of file types supported for exporting as a row source. Table 28.1 lists the supported file types by the Export utility.

You can add other file types as needed. To see which types can be exported, look at the macro commands TransferDatabase, TransferSpreadsheet, and TransferText. By selecting these commands and then clicking the Database Type action argument, you get a list of possible file types. This can be seen in Figure 28.4, where TransferDatabase has been selected. You can then take the text given for the file type and use it with your VBA code, as is shown in a little while with Listing 28.1.

Before getting into the code that performs the actual exporting of the tables, look at the subroutine for setting a default value for the export type. This subroutine is attached to the OnClick event of the cmdSetDefault command button. The cmdSetDefault_Click event procedure, listed here, calls ap_SetDatabaseProp():

Private Sub cmdSetDefault_Click()

   ap_SetDatabaseProp CurrentDb, "ExportTypeDefault", Me!cboExportFileType

End Sub

The ap_SetDatabaseProp routine is discussed in more detail in Chapter 26.

The ExportTypeDefault property is a custom property of the database. You can see this property, and other custom properties used by the VideoApp.mdb database, in Figure 28.5. To open this dialog, choose Database Properties from any of the Access File menus, and then click the Custom tab.

To set the default export type, follow these steps:

The next time someone uses this form, whatever export type that user selects as the default will be initially displayed.

Before examining the code for the actual exporting of files in Listing 28.1, look at the Export utility in action in Figure 28.6.

Examining the Code Creating the Export Utility

The code that does the actual exporting is attached to the cmdExportTables command button. Listing 28.1 shows the code attached to the OnClick event.

Listing 28.1. VideoApp.mdb: Performing the Actual Export

Private Sub cmdExportTables_Click()

   Dim strAppPath As String
   Dim strExportFileName As String
   Dim varCurrent As Variant

   On Error GoTo Error_cmdExportTables_Click

   DoCmd.Hourglass True

   '-- Establish application path
   strAppPath = CurrentProject.Path

   Dim lboTablesToExp As ListBox
   Set lboTablesToExp = Me!lboTablesToExport

   For Each varCurrent In lboTablesToExp.ItemsSelected

      '-- Perform export if table is Selected

      DoCmd.Echo True, "Exporting " & _
                    lboTablesToExp.ItemData(varCurrent) & "..."

      '-- Get the DOS export file name
      strExportFileName = DLookup("ExportFileName", _
                    "tblSharedTables", "[TableName]= '" & _
                    lboTablesToExp.ItemData(varCurrent) & "'")
      '-- Export to text file
      If cboExportFileType = "Text" Then

         DoCmd.TransferText acExportDelim, "", _
                    lboTablesToExp.ItemData(varCurrent), strAppPath & _
                    "" & strExportFileName & ".txt"

      '-- Export to spreadsheet
      ElseIf InStr(cboExportFileType, "Excel") <> 0 Then

         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
                    lboTablesToExp.ItemData(varCurrent), strAppPath & "" & _
                    strExportFileName

      '-- Export to database
      Else

         DoCmd.TransferDatabase acExport, cboExportFileType, strAppPath, _
                    acTable, lboTablesToExp.ItemData(varCurrent), strExportFileName

      End If

   Next

   '-- Clean up and notify
   DoCmd.Hourglass False
   DoCmd.Echo True
   Beep
   MsgBox "Table(s) Exported Successfully!"

   Exit Sub

Error_cmdExportTables_Click:

   DoCmd.Hourglass False
   DoCmd.Echo True
   MsgBox Err.Description

   Exit Sub

End Sub

The routine in Listing 28.1 uses a number of interesting commands:

• The first is the command that cycles through each selected table, listed in the lboTablesToExport list box. In the following code snippet, the For Each command cycles through the ItemsSelected collection off the list box. The ItemData property displays the value of the current selected item.

  Dim lboTablesToExp As ListBox
  Set lboTablesToExp = Me!lboTablesToExport
  
  For Each varCurrent In lboTablesToExp.ItemsSelected
  
     '-- Perform export if table is Selected
  
     DoCmd.Echo True, "Exporting " & _
                   lboTablesToExp.ItemData(varCurrent) & "..."
  

• The DLookup() function is used to retrieve the DOS file name stored in the SharedTables table:

  '-- Get the DOS export file name
  strExportFileName = DLookup("ExportFileName", _
                "tblSharedTables", "[TableName]= '" & _
                lboTablesToExp.ItemData(varCurrent) & "'")
  

• The type of exporting is examined, and the appropriate Transfer command is used. You can examine the various Transfer-type commands in Listing 28.1.

Again, if you want, you can expand on this utility to cover additional file types.