8 Integration with Office

OBJECTIVES

Exchange data from Access with Excel and Word.
Create a form letter in Word with an address file in Access.
Export a table to a new RTF document.
Export a table to Excel.

Access, Excel, PowerPoint, and Word are standalone programs for many users. For many business applications, a collaboration between the programs is important. And there are plenty of options for that.

8.1 About integration of Office applications

The individual programs within the Microsoft Office package can work well together so that integration within processes is possible. However, for many users, Access, Excel, and Word are separate programs, each with their strong and weak sides. Access for databases, Excel for the calculations, and Word for word processing.

Access can export data from tables, queries, and forms to Excel, so you can do there the calculations that are not possible in Access. Besides, Acess can import data from Excel worksheets.

Access can also export from tables, queries, and forms to a Word RTF file. Additionally, an Access database can serve as a data source when creating default letters and labels in Word through the wizard. This wizard can be started from both Access and Word.

8.2 Mailmerge

In this task, you will send customers a standard letter containing the announcement of a new voucher box with the name Snow White. The source for the addresses will be table Customers.

Task 8.1 File: candy365.accdb

Open the database.
Select table Customers.
Right-click on the table name and choose Export > Word Merge.

Figure 8.1: Microsoft Word Mail Merge Wizard.

Select Link your data to an existing Microsoft Word document and click OK.
Select practice file snow-white.docx in the dialog box and click Open. Microsoft Word is started with the practice file in it. On the ribbon, tab Mailings is activated and at the right side, the panel Mail Merge is displayed. On the bottom of this panel, you can see that the wizard is in step 3 of 6.

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

Click on the link Next: Write your letter.
Put the cursor in the first line and choose tab Mailings > Insert Merge Field (group Write & Insert Fields) > FirstName.
After that, add the fields LastName, Address, ZipCode and City according to the following model.
```
<<FirstName>> <<LastName>>
<<Address>>
<<ZipCode>>  <<City>>
```
Click in panel Mail Merge below Step 4 of 6 on the link Next: Preview your letters. An example letter for the first customer is shown.
Click in panel Mail Mail Merge below Step 5 of 6 on the link Next: Complete the merge. You can now complete the merge in panel Mail Merge:

You now have two choices:

With Print, you can send the letters to the printer.
With Edit individual letters, one document is created with the letters.

Click on the link Edit individual letters.

Specify that you want to create letters for the first 10 customers. Then click OK. A new Word document will be created containing 10 letters.
Save the document with name Invitation new box and close Word.

8.3 Export to Word

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

When you want to place the contents of a table or the result of a query into an existing Word document, then copy and paste is the simplest method. But you can also export the data to a new Word document. Then a document will be created in RTF format (Rich Text Format) that ca be opened in Word.

Task 8.2 File: candy365.accdb

Open the database.
Select table Boxes.
Right-click on the table name and choose Export > Word RTF File. The dialog box Export - RTF file is displayed.
Specify file name and path, then click OK. The dialog box Save Export Steps is displayed.
It’s not necessary to save the export steps. Click Close.
Check the export by opening the file in Word.

8.4 Export to Excel

Purpose: Export an Access table to a new Excel document.

Je kunt eenvoudig een tabel vanuit Access naar Excel exporteren.

Task 8.3 File: candy365.accdb

Open the database.
Select table Boxes.
Right-click on the table name and choose Export > Excel. The dialog box Export - Excel Spreadsheet is displayed.
Specify file name and path, select option Export data with formatting and layout, and click OK. The dialog box Save Export Steps is displayed.
It’s not necessary to save the export steps. Click Close.
Check the export by opening the file in Excel.

8.5 Exercises

Exercise 8.1 Standard letter (intg001)

The marketing department wants to promote the new box among the customers who have ever bought a box of pralines with white chocolate. Create the required query and then a standard letter with this query.

If a box contains at least 1 praline with white chocolate and this box has been purchased by a particular customer, we would like to send this customer a letter. Make sure that a customer receives no more than one letter.

Exercise 8.2 Export customer data (intg002)

Export all customer data to an Excel file.