8  Integration with Office

Keywords

Access, databases, tables, queries, forms, reports, macros, textbook

OBJECTIVES

  • Exchange data between Access and Excel or Word.
  • Create a mail merge letter in Word using an Access address table.
  • Export a table to a new RTF document.
  • Export a table to Excel.

Access, Excel, PowerPoint, and Word are often used as standalone programs. However, in many business scenarios, collaboration between them is essential—and Microsoft Office offers plenty of options to support this integration.

8.1 About Office Integration

The Microsoft Office applications are designed to work together, allowing seamless data exchange across programs. Still, many users treat Access, Excel, and Word as entirely separate tools—each with its own strengths:

  • Access for managing structured data (databases).
  • Excel for calculations and numerical analysis.
  • Word for word processing.

Access allows you to export data from tables, queries, and forms to Excel for calculations that may not be possible within Access. Similarly, Access can import data from Excel worksheets.

You can also export Access data to Word in RTF format (Rich Text Format). Additionally, Access tables or queries can be used as data sources in Word’s Mail Merge function, which can be initiated from either Word or Access.

8.2 Mail Merge

In this task, you’ll send a form letter to customers announcing a new voucher box called Snow White. The address data will come from the Customers table.

Task 8.1 File: candy365.accdb

  1. Open the database.

  2. Select the Customers table.

  3. Right-click the table and choose Export > Word Merge.

Figure 8.1: Microsoft Word Mail Merge Wizard.

  1. Choose Link your data to an existing Microsoft Word document and click OK.

  2. Select the practice file snow-white.docx and click Open. Microsoft Word opens with the document loaded. The Mailings tab is active, and the Mail Merge panel appears on the right. You’re now in step 3 of 6.

Figure 8.2: Progress wizard mail merge: step 3 of 6.

  1. Click Next: Write your letter.

  2. Place the cursor at the top of the letter. Go to Mailings > Insert Merge Field > FirstName.

  3. Then add the following fields in the format below:

    <<FirstName>> <<LastName>>
<<Address>>
<<ZipCode>>  <<City>>

  4. In the Mail Merge panel, click Next: Preview your letters (Step 4 of 6). The first customer’s letter will be displayed.

  5. Then click Next: Complete the merge (Step 5 of 6).

Figure 8.3: Finishing mail merge.

You now have two options:

  • Print to send letters directly to the printer.
  • Edit individual letters to generate one document with all letters.
  1. Click Edit individual letters.
Figure 8.4: Selection of records.

  1. In the prompt, choose to generate letters for the first 10 customers. Click OK. A new Word document with 10 letters is created.

  2. Save the document as Invitation new box and close Word.

8.3 Export to Word

Goal: Export an Access table to a new RTF document.

If you want to insert the content of a table or query into an existing Word document, the easiest method is to copy and paste. However, you can also export directly to a new Word file. This creates a Rich Text Format (.rtf) file that can be opened in Word.

Task 8.2 File: candy365.accdb

  1. Open the database.

  2. Select the Boxes table.

  3. Right-click the table and choose Export > Word RTF File. The Export - RTF File dialog appears.

  4. Enter the file name and path, then click OK. The Save Export Steps dialog appears.

  5. You don’t need to save the export steps. Click Close.

  6. Open the exported file in Word to verify.

8.4 Export to Excel

Goal: Export an Access table to a new Excel workbook.

Exporting a table from Access to Excel is straightforward.

Task 8.3 File: candy365.accdb

  1. Open the database.

  2. Select the Boxes table.

  3. Right-click the table and choose Export > Excel. The Export - Excel Spreadsheet dialog appears.

  4. Specify a file name and path, select Export data with formatting and layout, then click OK.

  5. In the next dialog (Save Export Steps), click Close—saving is optional.

  6. Open the file in Excel to check the export.

8.5 Exercises

Exercise 8.1 Form Letter (intg001)

The marketing department wants to promote the new voucher box to customers who have previously purchased a box containing white chocolate pralines.

Create the required query, and then use it as the source for a form letter.

  • Criteria: If a box contains at least one white chocolate praline and was ordered by a customer, that customer should receive the letter.

  • Ensure that each customer receives no more than one letter.

Exercise 8.2 Export Customer Data (intg002)

Export all customer information to an Excel file.