Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

1. Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition Integrating Word, Excel, and Access

2. Objectives • Integrate data among Word, Excel, and Access • Import an Excel worksheet into Access • Copy a Word table to Access • Link an Access table to Excel and Word • Link an Access table to Word Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

3. Unit Introduction • Typical business to-do list: • Create sales charts • Order new products • Complete purchase orders • Produce proposals for new contracts • Using integration to perform tasks • Build Access database to store records • Link Access database to Excel workbook • Also link Access database to Word report Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

4. Integrating Data Among Word, Excel, and Access • Integrating data from three programs • Enter data into an Access database • Make calculations using data in Excel • Link Word report to Excel data and table • Importing Excel worksheet into Access • Enter data into table directly or via import • Access import tool: Get External Data Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

5. Integrating Data Among Word, Excel, and Access • Copying a Word table into Access • Copy and paste into new or existing table • Linking Access table to Excel and Word • Keep data current in all three applications • Use Copy and Paste Special commands • Linking an Access table to Word • You can reformat the linked table in Word Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

6. Integrating Data Among Word, Excel, and Access • Excel data imported to an Access table Field Name changed from Participants to Guests during the import process Excel data Excel data imported to Access and named Tours table Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

7. Integrating Data Among Word, Excel, and Access • Word report linked to Excel and Access Table linked to Excel and to Access Table copied from Access and pasted as a link Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

8. Importing an Excel Worksheet into Access • Ways to import Excel data to Access • Import Excel data into new Access table • Append Excel data to an existing table • How to import Excel data into Access • Click the Get External Data tab in Access • Click Excel button in the Import Group • Navigate to Excel file and select an option • Follow Import Spreadsheet Wizard Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

9. Importing an Excel Worksheet into Access • Options in Get External Data dialog box The file path will be different on your computer Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

10. Importing an Excel Worksheet into Access • Changing field names in imported table Field name changes in column heading Guests entered in the Field Name box Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

11. Importing an Excel Worksheet into Access • Excel table imported to Access Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

12. Copying a Word Table to Access • Copy table in Word, Paste in Access • Pasted Word table is not linked • Changes in one table do not affect other • Two methods for adding Word table • Paste table into new, blank Access table • Paste table into existing Access table • Rule for pasting into existing table • Tables to have same number of records Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

13. Copying a Word Table to Access • Selecting the location for copied data Add New Field selected Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

14. Linking an Access Table to Excel and Word • Benefits of linking three programs: • Use each program’s strengths • Reduce amount of data entry • Use Copy and Paste Special tools • Step 1: link Access table to Excel file • Step 2: link Excel data to Word file • Effects of changing table in Access • Data in Excel and Word will also change Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

15. Linking an Access Table to Excel and Word • Copied data formatted in Excel Accounting Number Format Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

16. Linking an Access Table to Excel and Word • Excel data pasted into Word Linked table Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

17. Linking an Access Table to Word • Use Copy and Paste Special tools • Effect of updating Access table • Linked table in Word also updated • Reason for excluding Excel link • Excel-type calculations are not needed • Pasted table can be formatted in Word • Rule for enabling content in linked files • Open linked files in order of creation Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

18. Linking an Access Table to Word • Completed Word report Revenue updated to $75,000 Burns changed to Abrams Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

19. Summary • Edits to Access data are reflected in linked Excel or Word data • Get External Data: Access import tool • Import Spreadsheet Wizard: adapts Excel data to an Access table • A Word table is pasted into an Access table without links • Link Word, Access, and Excel data to utilize each program’s strengths Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition