Exporting Data Using VBA Code

3 4

Up to now, we’ve explored various ways to export Access objects to other databases and programs. In the remainder of this chapter, you’ll learn how to use VBA code to export to formats that aren’t supported in the interface, such as Microsoft Outlook contacts or appointments or a Word document.

For more information about working with VBA in Access, see Chapter 20, "Customizing Your Database Using VBA Code." Also, see the sidebar "Import/Export Terminology" in Chapter 16 for a review of terminology you ll need to know to understand exporting in Access using VBA.

On the companion CD, you’ll find the sample database and the VBA code that illustrates the techniques shown in this chapter.

To run this code, follow these steps:

1. On your hard disk, create a folder named Documents. The code assumes that this folder is located on the D drive, so if you locate your folder elsewhere, you need to modify the code before you run it.
2. In the Documents folder, create a folder named Access 2002. Copy the file Export.mdb on the companion CD to the Access 2002 folder.
3. In the Access 2002 folder, create an empty database named General.
4. Copy the file Northwind Invoice.dot to the Templates folder. The exact location of the folder depends on your individual settings and the operating system you run. For example, if you run Windows 2000 Server, the defaultlocation is C:\Documents and Settings\<your id> \Application Data\Microsoft\Templates.
5. Open the Export database, and open the Visual Basic Editor (VBE). You’re now ready to run the sample functions by positioning the insertion point inside a function and pressing F5.

Using Transfer Methods to Export

When you export data from Access to other Access databases and applications, you can use the same methods of the Access DoCmd object that you use to import data into Access. (See Chapter 16, "Importing and Linking Data to Access," for more information about importing using VBA.) The TransferDatabase, TransferSpreadsheet, and TransferText methods have been available for many Access versions, and the TransferSQLDatabase method is new to Access version 2002. (This new method isn’t used to export Access data to SQL Server, however, but to copy one SQL Server database to another SQL Server database.) The Transfer methods of the DoCmd object are listed in Table 17-3.

Table 17-3. Exporting Access data in VBA code

Data type	VBA method to use
Access	Access.DoCmd.TransferDatabase method with acExport as the TransferType parameter and "Microsoft Access" as the DatabaseType parameter
dBASE 5	Access.DoCmd.TransferDatabase method with acExport as the TransferType parameter and "dBase 5.0" as the DatabaseType parameter
dBASE III	Access.DoCmd.TransferDatabase method with acExport as the TransferType parameter and "dBase III" as the DatabaseType parameter
dBASE IV	Access.DoCmd.TransferDatabase method with acExport as the TransferType parameter and "dBase IV" as the DatabaseType parameter
Delimited text files	Access.DoCmd.TransferText method with acExportDelim as the TransferType parameter
Excel 3	Access.DoCmd.TransferSpreadsheet method with acExport as the TransferType parameter and acSpreadsheetTypeExcel3 as the SpreadsheetType parameter
Excel 4	Access.DoCmd.TransferSpreadsheet method with acExport as the TransferType parameter and acSpreadsheetTypeExcel4 as the SpreadsheetType parameter
Excel 5	Access.DoCmd.TransferSpreadsheet method with acExport as the TransferType parameter and acSpreadsheetTypeExcel5 as the SpreadsheetType parameter
Excel 7	Access.DoCmd.TransferSpreadsheet method with acExport as the TransferType parameter and acSpreadsheetTypeExcel7 as the SpreadsheetType parameter
Excel 8	Access.DoCmd.TransferSpreadsheet method with acExport as the TransferType parameter and acSpreadsheetTypeExcel8 as the SpreadsheetType parameter
Excel 9	Access.DoCmd.TransferSpreadsheet method with acExport as the TransferType parameter and acSpreadsheetTypeExcel9 as the SpreadsheetType parameter
Fixed-width text files	Access.DoCmd.TransferText method with acExportFixed as the TransferType parameter
HTML files	Access.DoCmd.TransferText method with acExportHTML as the TransferType parameter
Jet 2.x	Access.DoCmd.TransferDatabase method with acExport as the TransferType parameter and "Jet 2.x" as the DatabaseType parameter
Lotus WJ2 (Japanese version only)	Access.DoCmd.TransferSpreadsheet method with acExport as the TransferType parameter and acSpreadsheetTypeLotusWJ2 as the SpreadsheetType parameter
Lotus WK1	Access.DoCmd.TransferSpreadsheet method with acExport as the TransferType parameter and acSpreadsheetTypeLotusWK1 as the SpreadsheetType parameter
Lotus WK3	Access.DoCmd.TransferSpreadsheet method with acExport as the TransferType parameter and acSpreadsheetTypeLotusWK3 as the SpreadsheetType parameter
Lotus WK4	Access.DoCmd.TransferSpreadsheet method with acExport as the TransferType parameter and acSpreadsheetTypeLotusWK4 as the SpreadsheetType parameter
ODBC databases	Access.DoCmd.TransferDatabase method with acExport as the TransferType parameter and "ODBC Database" as the DatabaseType parameter
Paradox 3.x	Access.DoCmd.TransferDatabase method with acExport as the TransferType parameter and "Paradox 3.X" as the DatabaseType parameter
Paradox 4.x	Access.DoCmd.TransferDatabase method with acExport as the TransferType parameter and "Paradox 4.X" as the DatabaseType parameter
Paradox 5.x	Access.DoCmd.TransferDatabase method with acExport as the TransferType parameter and "Paradox 5.X" as the DatabaseType parameter
Paradox 7.x	Access.DoCmd.TransferDatabase method with acExport as the TransferType parameter and "Paradox 7.X" as the DatabaseType parameter
Word Merge	Access.DoCmd.TransferText method with acExportMerge as the TransferType parameter

As with linking and importing, the TransferDatabase method’s DatabaseType parameter takes a text string, whereas the SpreadsheetType method’s SpreadsheetType parameter and the TransferText method’s TransferType parameter both take a named constant from an enum (an enumerated list of constants; Access enums are visible in the Object Browser as classes with the ac prefix). Note that the DataType parameter does not have a corresponding enum.

Exporting Database Objects to Other Access Databases

You can use the TransferDatabase method to export any type of Access database object to another database, with the same limitations as in the interface: Database objects other than tables can be exported only to Access 2002 or Access 2000 databases. Tables can also be exported to Access 97 databases.

The following function exports a table to the General.mdb database (located in the D:\Documents\Access 2002 folder) in structure-only mode. In other words, an empty table is created in the target database, with the same structure as the original table. The function uses the tblMembers table defined in the Export.mdb database, as described at the beginning of this section. You can find this database and the code for this function on the companion CD.

 Function ExportToAccess() DoCmd.TransferDatabase transfertype:=acExport, _     databasetype:="Microsoft Access", _     databasename:="D:\Documents\Access 2002\General.mdb", _     ObjectType:=acTable, Source:="tblMembers", _     Destination:="tblMembers", structureonly:=True End Function 

Exporting Data to Excel Worksheets

Depending on how you use your applications to work with each other, you might routinely need to re-create Excel spreadsheets with updated data that you gather in your Access databases. For example, suppose you use an Access database to track open orders and the employees to whom those orders are assigned for fulfillment. At the end of the week, you publish a report that shows how many orders were fulfilled and lists the number of orders filled per employee. You’d like to summarize the data and export it to a large Excel spreadsheet so that you can analyze it in the context of quarterly and annual data.

The following function exports an Access query named qryOrdersAndEmployees to an Excel 9 workbook named Orders and Employees.xls (located in the D:\Documents folder) using the Access field names as column headings. The Excel worksheet produced by this function could then be used by colleagues who don’t have Access or who prefer to analyze data in Excel, using its advanced data analysis tools. Make sure that the query and the location both exist before you run this function, as described at the beginning of this section.

 Function ExportToExcel() DoCmd.TransferSpreadsheet transfertype:=acExport, _     spreadsheettype:=acSpreadsheetTypeExcel9, _     tablename:="qryOrdersAndEmployees", _     filename:="D:\Documents\Orders and Employees.xls", _     hasfieldnames:=True End Function 

Exporting Data to Text Files

As you’ve seen, text files can be output in two formats: as a delimited file in which data items are separated by a separator character (such as a comma, tab, or space) and as a fixed-width file that stores data in a columnar format in which each data item is stored in the same position in a record and is given the same width. When you export to a file using one of these file types using VBA code, you must use an export specification to define precisely how the text file should be formatted.

Of the two text file export choices, a delimited text file is generally more useful because it can be imported into almost any type of program, including mainframe databases. Fixed-width files are useful when you want a text file with data lined up neatly in columns. (And as mentioned earlier in the chapter, fixed-width text files are displayed correctly only when a fixed-width font (such as Courier New) is used.

Creating Export Specifications for Recurring Export Tasks

If you export Access database objects to a text file format on a regular basis, it’s helpful to create an export specification to store the options you select, to streamline the export process. It might be part of your weekly routine, for example, to export a comma-delimited text file of all new clients and contacts you made during the previous week. A saved export specification file (often called a spec) can do double duty: It saves time when you’re using the interface to export a file, as you can just select the spec by clicking the Advanced button on the first page of the Export Wizard instead of selecting all the export options manually, and the same spec can also be used with the SpecificationName parameter of the TransferText method when you’re using VBA code to export.

If you export Access data to a comma-delimited text file, you can then import the data from the text file into a wide range of applications, and the file can be used by colleagues working in a variety of database and spreadsheet programs. To create an export specification for exporting data from an Access table or query to a delimited text file, follow these steps:

1. In the Database window, select the table or query you want to export.
2. Choose File, Export. The Export dialog box will open.
3. In the Save As Type drop-down list, select Text Files.
4. Click Export to open the Export Text Wizard, as shown in Figure 17-22. The default setting is Delimited. Click Next to accept the default.

   

   Figure 17-22. Accept the Delimited option on the first page of the Export Text Wizard.

5. Click the Tab option for the delimiter, leave the text qualifier set to a double quotation mark, and then select Include Field Names On First Row, as shown in Figure 17-23.

   

   Figure 17-23. Select the options for the export.

6. Click Advanced to open the Export Specification dialog box. (See Figure 17-24.)

   

   Figure 17-24. Save export settings you use often in the Export Specification dialog box.

7. Make the adjustments you want, click Save As, and enter (or accept) the name of the spec as shown in Figure 17-25.

   

   Figure 17-25. Save the export specification with a name of your choice.

8. Click OK to save the spec.

You can now use the spec in code as the setting of the SpecificationName parameter of the TransferText method or in the interface by selecting it from the dialog box that opens when you click Specs on the Export Specification dialog box.

The following function, which is also included on the companion CD, exports the tblContacts table to a tab-delimited text file named Contacts.csv located in the D:\Documents folder. The function uses the saved export specification "Contacts Export Specification" you just created and includes the Access field names as the first row of the text file. You can run this function after you perform the preparatory steps described at the beginning of this section. The comma-delimited file created by running this function can be opened in Notepad, Word, or Excel or imported into many other programs.

 Function ExportToTextFile() DoCmd.TransferText transfertype:=acExportDelim, _     specificationname:="Contacts Export Specification", _     tablename:="tblContacts", _     filename:="D:\Documents\Contacts.csv", _     hasfieldnames:=True End Function 

Exporting Data to dBASE Databases

If you want to create a contacts or members list as a dBASE database to be used by a colleague working in dBASE or perhaps for importing into another database program that has a dBASE import selection, you can do so quickly using a VBA function. The following function exports the tblMembers table to a dBASE IV database named Members.dbf located in the D:\Documents folder. For dBASE (and other databases that store each table in a different file), the database name is set using the Destination parameter and the path is set using the Databasename parameter. Be sure to complete the preparatory steps, as described at the beginning of this section, before you run this code.

 Function ExportToDBase() DoCmd.TransferDatabase transfertype:=acExport, _     databasetype:="dBASE IV", _     databasename:="D:\Documents", _     ObjectType:=acTable, Source:="tblMembers", _     Destination:="Members.dbf", structureonly:=True End Function 

Exporting Data to Outlook Appointments

As when you import from Outlook, you need to understand the Outlook object model to be able to export Access data to Outlook. Since the Outlook object model is hierarchical, you have to work down the object model to the folder you want to populate and then create Outlook items of a specific type, filling them with data from Access records. When you create new Outlook items in VBA code, you start with the Outlook Application object, then move down to the NameSpace object, then to a specific Outlook folder, and finally to Outlook items in that folder.

The following function creates appointments in the default local Outlook Calendar folder. Note that this function requires references to the Microsoft Outlook 10.0 Object Library and the Microsoft DAO 3.6 Object Library. You can set these references by opening the References dialog box from the Tools menu in the VBE window and then checking those selections, as shown in Figure 17-26.

Figure 17-26. You set references to the Outlook and DAO object libraries in the References dialog box.

 Function ExportToOutlook()    Dim appOutlook As New Outlook.Application    Dim nms As Outlook.NameSpace    Dim fld As Outlook.MAPIFolder    Dim appt As Outlook.AppointmentItem    Dim itms As Outlook.Items    Dim dbs As DAO.Database    Dim rst As DAO.Recordset    Dim lngCount As Long    Dim strStart As String    Dim strEnd As String    Set nms = appOutlook.GetNamespace("MAPI")    Set fld = nms.GetDefaultFolder(olFolderCalendar)    Set itms = fld.Items    'Get reference to data table.    Set dbs = CurrentDb    'Create recordset based on query.    Set rst = dbs.OpenRecordset("qryAppointments", dbOpenDynaset)    rst.MoveLast    rst.MoveFirst    lngCount = rst.RecordCount    Debug.Print lngCount & " appointments to transfer to Outlook"    'Loop through table, exporting each record to Outlook.    Do Until rst.EOF      'Create an appointment item.      Set appt = itms.Add("IPM.Appointment")      With appt        Debug.Print "Exporting " & Nz(rst![Description]) & " record"        .Subject = Nz(rst![Description])        .Categories = Nz(rst![ApptType])        'Concatenate Access date and time fields into a single        'expression for the Outlook Start and End fields.        strStart = rst![StartDate] & " " & rst![StartTime]        strEnd = rst![EndDate] & " " & rst![EndTime]        .Start = strStart        .End = strEnd        .Body = Nz(rst![Notes])        .Close (olSave)      End With      rst.MoveNext   Loop   rst.Close End Function 

note

---

This function uses the data in the table tblAppointments. Outlook won’t accept appointments made in the past, so if the dates in this table are earlier than the date on which you run the code, simply change the dates in the table so that the appointments occur in the future.

When you run this code, you’ll get the message shown in Figure 17-27. This message comes from the Object Model Guardian, a security feature that safeguards your Outlook contacts against a possible virus.

Figure 17-27. This warning message appears when you run the preceding code.

Selecting Allow Access For and then selecting 5 Minutes from the drop-down list should give you plenty of time to run the code. Figure 17-28 shows the newly created appointments in the Outlook Calendar.

Figure 17-28. These Outlook appointments were created from an Access table.

Exporting Data to Word Tables

note

---

The following example is based on a column I wrote a couple of years ago. The full article is available for download from my Web site, at http://www.helenfeddema.com/access.htm. Look for Access Archon article #44.

Since the earliest versions of Access, users have wanted to export a complex Access report to Word, retaining all of its formatting. Unfortunately, there’s no quick and easy way to do this, as noted earlier in the discussion of exporting to the Word RTF format (see the section "Exporting Reports"). The Northwind Invoice report (see Figure 17-29), for example, doesn’t export well to Word (see Figure 17-30). But that doesn’t mean it’s impossible to export Access data to Word and create a properly formatted invoice filled with the data—it just takes some code-writing skills. Once you have the code written and working, you can do the export with a single click of a toolbar button or by running a macro with a RunCode action.

Figure 17-29. The Northwind Invoice report uses graphics and other formatting.

Figure 17-30. When you export the Northwind Invoice report to Word using the interface, it loses some of its formatting.

Because Word has advanced formatting options, the most efficient technique for exporting an Access report to Word is to create a Word template with as much formatting as possible already built in and then write the Access data to a new document made from the Word template. You can place tabular data into cells of a Word table; other data that you might need in several places in the Word document can be written to Word custom document properties and displayed in the document in DocProperty fields. This method has a few advantages over Word Mail Merge:

• Each document contains its own data, so you don’t need to have a separate data source open. This helps conserve memory and system resources and prevent sharing violations.
• Once the Word template and Access procedure have been set up, creating a new Word document (or set of documents) from updated data is fast and automatic. You don’t need to step through the many pages of the Word Mail Merge Wizard every time you re-create the report.
• When you use this method to create multiple documents (by means of a multiselect list box, for example), you create separate Word documents, not pages in a huge multipage document.

To produce a Word invoice for a specific order based on Northwind data, I created a small dialog form (shown in Figure 17-31) that lets the user select the Order Number from a drop-down list that also displays the Company Name and Order Date. These fields are displayed in text boxes after a selection is made from the combo box.

Figure 17-31. You can use this dialog form for selecting an order number to be invoiced.

The following event procedure behind the Create Invoice command button creates the Word invoice for the selected order. Figure 17-32 shows a sample invoice. The procedure is included in the Export.mdb sample code on the companion CD.

Figure 17-32. This Word invoice was created from Northwind data.

Before you run this code, be sure to perform the preparatory steps as described at the beginning of this section. In addition, the code requires a reference to the Word 10.0 object model. Open the References dialog box from the Tools menu in the VBE window and check the Microsoft Word 10.0 Object Library selection, as shown in Figure 17-33, to make sure the code will compile and run without errors.

Figure 17-33. You must set a reference to the Word 10.0 object library to run the following code.

 Private Sub cmdCreateInvoice_Click() On Error Resume Next     Dim dbs As DAO.Database     Dim objDocs As Object     Dim objWord As Object     Dim prps As Object     Dim rst As DAO.Recordset     Dim blnSaveNameFail As Boolean     Dim lngOrderID As Long     Dim strShipName As String     Dim strShipAddress As String     Dim strShipCityStateZip As String     Dim strShipCountry As String     Dim strCustomerID As String     Dim strCompanyName As String     Dim strBillToAddress As String     Dim strBillToCityStateZip As String     Dim strBillToCountry As String     Dim strSalesperson As String     Dim dteTodayDate As Date     Dim dteOrderDate As Date     Dim dteRequiredDate As Date     Dim dteShippedDate As Date     Dim strShipper As String     Dim curSubtotal As Currency     Dim curFreight As Currency     Dim curTotal As Currency     Dim lngProductID As Long     Dim strProductName As String     Dim dblQuantity As Double     Dim strUnitPrice As String     Dim strDiscount As String     Dim strExtendedPrice As String     Dim strDoc As String     Dim strDocsPath As String     Dim strSaveName As String     Dim strSaveNamePath As String     Dim strShortDate As String     Dim strTemplatePath As String     Dim strTest As String     Dim strTestFile As String     Dim strWordTemplate As String     Dim strMessageTitle As String     Dim strMessage As String     Dim intReturn As Integer     Dim intCount As Integer         'Create a Word instance to use for the invoice. Uses the existing      'Word instance if there is one; otherwise, creates a new instance.     Set objWord = GetObject(, "Word.Application")     If Err.Number = 429 Then         'Word isn’t running; creating a Word object.         Set objWord = CreateObject("Word.Application")         Err.Clear     End If     'Sets up error handler for the rest of the procedure On Error GoTo cmdWordInvoice_ClickError     'Run make-table queries to create tables to use for export.     'I use make-table queries instead of select queries because the     'queries have a criterion limiting the Order ID to the one      'selected on the form and such parameter queries can’t be used     'in a recordset. Instead, the make table queries are run to      'create tables that will be used in the recordsets later      'in the code.     DoCmd.SetWarnings False     DoCmd.OpenQuery "qmakInvoice"     DoCmd.OpenQuery "qmakInvoiceDetails"         'Check that there’s at least one detail item before      'creating invoice.     intCount = DCount("*", "tmakInvoiceDetails")     Debug.Print "Number of Detail items: " & intCount         If intCount < 1 Then         MsgBox "No detail items for invoice; canceling"         Exit Sub     End If         'Create recordset and get needed doc properties for this invoice.     Set dbs = CurrentDb     Set rst = dbs.OpenRecordset("tmakInvoice", dbOpenDynaset)     With rst         'The Nz function is used to convert any Nulls to zeros or         'zero-length strings, to prevent problems with exporting         'to Word.         lngOrderID = Nz(![OrderID])         Debug.Print "Order ID: " & lngOrderID         strShipName = Nz(![ShipName])         strShipAddress = Nz(![ShipAddress])         strShipCityStateZip = Nz(![ShipCityStateZip])         strShipCountry = Nz(![ShipCountry])         strCompanyName = Nz(![CompanyName])         strCustomerID = Nz(![CustomerID])         strCompanyName = Nz(![CompanyName])         strBillToAddress = Nz(![BillToAddress])         strBillToCityStateZip = Nz(![BillToCityStateZip])         strBillToCountry = Nz(![BillToCountry])         strSalesperson = Nz(![Salesperson])         dteOrderDate = Nz(![OrderDate])         dteRequiredDate = Nz(![RequiredDate])         dteShippedDate = Nz(![ShippedDate])         strShipper = Nz(![Shipper])         curSubtotal = Nz(![Subtotal])         curFreight = Nz(![Freight])         curTotal = Nz(![Total])     End With     rst.Close         'The paths for Templates and Documents folders are picked up      'from the File Locations page of the Word Options dialog box.     strDocsPath = objWord.Options.DefaultFilePath(wdDocumentsPath) & "\"     Debug.Print "Docs path: " & strDocsPath     strTemplatePath = _         objWord.Options.DefaultFilePath(wdUserTemplatesPath) & "\"     strWordTemplate = strTemplatePath & "Northwind Invoice.dot"         'This date string is used in creating the invoice’s save name.     strShortDate = Format(Date, "m-d-yyyy")         'This date variable is used to print today’s date on the invoice.     '(Unlike a Word date code, it remains stable when the invoice is     'reopened later.)     dteTodayDate = Date         'Check for existence of template in template folder,     'and exit if not found.     strTestFile = Nz(Dir(strWordTemplate))     If strTestFile = "" Then         MsgBox strWordTemplate & " template not found; can’t create letter"         Exit Sub     End If         Set objDocs = objWord.Documents     objDocs.Add strWordTemplate         'Write information to Word custom document properties from     'previously created variables.     Set prps = objWord.ActiveDocument.CustomDocumentProperties     prps.Item("TodayDate").Value = dteTodayDate     prps.Item("OrderID").Value = lngOrderID     prps.Item("ShipName").Value = strShipName     prps.Item("ShipAddress").Value = strShipAddress     prps.Item("ShipCityStateZip").Value = strShipCityStateZip     prps.Item("ShipCountry").Value = strShipCountry     prps.Item("CompanyName").Value = strCompanyName     prps.Item("CustomerID").Value = strCustomerID     prps.Item("CompanyName").Value = strCompanyName     prps.Item("BillToAddress").Value = strBillToAddress     prps.Item("BillToCityStateZip").Value = strBillToCityStateZip     prps.Item("BillToCountry").Value = strBillToCountry     prps.Item("Salesperson").Value = strSalesperson     prps.Item("OrderDate").Value = dteOrderDate     prps.Item("RequiredDate").Value = dteRequiredDate     prps.Item("ShippedDate").Value = dteShippedDate     prps.Item("Shipper").Value = strShipper     prps.Item("Subtotal").Value = curSubtotal     prps.Item("Freight").Value = curFreight     prps.Item("Total").Value = curTotal         'Highlight the entire Word document and update fields so that      'the data written to the custom doc props is displayed in the       'DocProperty fields.     With objWord         .Selection.WholeStory         .Selection.Fields.Update         .Selection.HomeKey Unit:=6         .Visible = True         .Activate     End With         'Go to table to fill with Details data.     With objWord.Selection         .GoTo What:=wdGoToTable, Which:=wdGoToFirst, Count:=3,  _              Name:=""         .MoveDown Unit:=wdLine, Count:=1     End With         'Set up recordset of linked Details data to put in table on     'Word invoice.     Set rst = dbs.OpenRecordset("tmakInvoiceDetails", dbOpenDynaset)         'Save Details information to variables.     'Use Format function to apply appropriate formatting to     'Currency and Percent fields.     With rst         .MoveFirst         Do While Not .EOF             lngProductID = Nz(![ProductID])             Debug.Print "Product ID: " & lngProductID             strProductName = Nz(![ProductName])             Debug.Print "Product Name: " & strProductName             dblQuantity = Nz(![Quantity])             Debug.Print "Quantity: " & dblQuantity             strUnitPrice = Format(Nz(![UnitPrice]), "$##.00")             Debug.Print "Unit price: " & strUnitPrice             strDiscount = Format(Nz(![Discount]), "0%")             Debug.Print "Discount: " & strDiscount             strExtendedPrice = Format(Nz(![ExtendedPrice]),  _                 "$#,###.00")             Debug.Print "Extended price: " & strExtendedPrice                       'Move through the table, writing values from the              'variables to cells in the Word table.             With objWord.Selection                 .TypeText Text:=CStr(lngProductID)                 .MoveRight Unit:=wdCell                 .TypeText Text:=strProductName                 .MoveRight Unit:=wdCell                 .TypeText Text:=CStr(dblQuantity)                 .MoveRight Unit:=wdCell                 .TypeText Text:=strUnitPrice                 .MoveRight Unit:=wdCell                 .TypeText Text:=strDiscount                 .MoveRight Unit:=wdCell                 .TypeText Text:=strExtendedPrice                 .MoveRight Unit:=wdCell             End With             .MoveNext          Loop          .Close     End With     dbs.Close         'Delete last, empty row.     Selection.SelectRow     Selection.Rows.Delete         'Check for existence of previously saved letter in documents ,     'folder and append an incremented number to save name if found.     strSaveName = "Invoice to " & strCompanyName & " for Order " _         & lngOrderID & " on " & strShortDate & ".doc"          intCount = 2     blnSaveNameFail = True     Do While blnSaveNameFail         strSaveNamePath = strDocsPath & strSaveName         Debug.Print "Proposed save name and path: " _             & vbCrLf & strSaveNamePath         strTestFile = Nz(Dir(strSaveNamePath))         If strTestFile = strSaveName Then                       'Create new save name with incremented number.             blnSaveNameFail = True             strSaveName = "Invoice " & CStr(intCount) & _             " to " & strCompanyName _             & " for Order " & lngOrderID & " on " & strShortDate & ".doc"             strSaveNamePath = strDocsPath & strSaveName             intCount = intCount + 1         Else             blnSaveNameFail = False         End If     Loop         'Ask whether user wants to save the document.     'If you prefer, you can eliminate the prompt and just     'save the document automatically using the save name.     strMessageTitle = "Save document?"      strMessage = "Save this document as " & strSaveName     intReturn = MsgBox(strMessage, vbYesNoCancel + _         vbQuestion + vbDefaultButton1, strMessageTitle)         If intReturn = vbNo Then         objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges         GoTo cmdWordInvoice_ClickExit     ElseIf intReturn = vbYes Then         objWord.ActiveDocument.SaveAs strSaveNamePath     ElseIf intReturn = vbCancel Then         GoTo cmdWordInvoice_ClickExit     End If     cmdWordInvoice_ClickExit:     'Close any open recordset or database, in case code stops because     'of an error.     On Error Resume Next     rst.Close     On Error Resume Next     dbs.Close     Exit Sub cmdWordInvoice_ClickError:     MsgBox "Error No: " & Err.Number & "; Description: " & _     Err.Description     Resume cmdWordInvoice_ClickExit End Sub 

Using Make-Table Queries to Create Flat-File Tables for Export

The CmdCreateInvoice_Click function shown in the previous section uses a select query (shown in Design view in Figure 17-34) to combine data from several Northwind tables into a single query, effectively converting data in linked, normalized tables to a flat-file recordset—something you can’t do with any of the standard export selections. This technique is useful when you need to export data to a program that isn’t a relational database. For example, you can use it to create tables of flat-file data to export to another Office application, such as Word, Excel, or Outlook, or for exporting to dBASE files and text files.

Figure 17-34. This select query combines data from linked, normalized tables into a single flat-file recordset.

A totals query adds up the price of the order details, and two make-table queries produce two temporary tables from the data, one for the body of the invoice and one for the invoice details. Figure 17-35 shows the make-table query that produces the table of data for the body of the invoice.

Figure 17-35. This make-table query produces a table used as the source of the invoice body data.