Microsoft Office 2007-Illustrated Microsoft Office 2007-Illustrated Introductory, Windows Vista EditionIntroductory, Windows Vista Edition

Integrating Word, Excel, Integrating Word, Excel, and Accessand Access

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

ObjectivesObjectives

• Integrate data among Word, Excel, Integrate data among Word, Excel, and Accessand Access

• Import an Excel worksheet into Import an Excel worksheet into AccessAccess

• Copy a Word table to AccessCopy a Word table to Access

• Link an Access table to Excel and Link an Access table to Excel and WordWord

• Link an Access table to WordLink an Access table to Word

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Unit IntroductionUnit Introduction

• Typical business to-do list:Typical business to-do list:• Create sales chartsCreate sales charts• Order new productsOrder new products• Complete purchase ordersComplete purchase orders• Produce proposals for new contractsProduce proposals for new contracts

• Using integration to perform tasks Using integration to perform tasks • Build Access database to store records Build Access database to store records • Link Access database to Excel workbook Link Access database to Excel workbook • Also link Access database to Word report Also link Access database to Word report

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Integrating Data Among Word, Integrating Data Among Word, Excel, and AccessExcel, and Access

• Integrating data from three programsIntegrating data from three programs• Enter data into an Access databaseEnter data into an Access database• Make calculations using data in Excel Make calculations using data in Excel • Link Word report to Excel data and tableLink Word report to Excel data and table

• Importing Excel worksheet into AccessImporting Excel worksheet into Access• Enter data into table directly or via import Enter data into table directly or via import • Access import tool: Get External DataAccess import tool: Get External Data

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Integrating Data Among Word, Integrating Data Among Word, Excel, and AccessExcel, and Access

• Copying a Word table into AccessCopying a Word table into Access• Copy and paste into new or existing tableCopy and paste into new or existing table

• Linking Access table to Excel and WordLinking Access table to Excel and Word• Keep data current in all three applicationsKeep data current in all three applications• Use Copy and Paste Special commandsUse Copy and Paste Special commands

• Linking an Access table to WordLinking an Access table to Word• You can reformat the linked table in Word You can reformat the linked table in Word

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Integrating Data Among Word, Integrating Data Among Word, Excel, and AccessExcel, and Access

• Excel data imported to an Access tableExcel data imported to an Access table

Excel data

Excel data importedto Access and named Tours table

Field Name changed from Participants to Guests during the import process

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Integrating Data Among Word, Integrating Data Among Word, Excel, and AccessExcel, and Access• Word report linked to Excel and Access Word report linked to Excel and Access

Table copiedfrom Accessand pastedas a link

Table linked toExcel and toAccess

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Importing an Excel Worksheet Importing an Excel Worksheet into Accessinto Access

• Ways to import Excel data to Access Ways to import Excel data to Access • Import Excel data into new Access tableImport Excel data into new Access table• Append Excel data to an existing tableAppend Excel data to an existing table

• How to import Excel data into Access How to import Excel data into Access • Click the Get External Data tab in AccessClick the Get External Data tab in Access• Click Excel button in the Import GroupClick Excel button in the Import Group• Navigate to Excel file and select an optionNavigate to Excel file and select an option• Follow Import Spreadsheet Wizard Follow Import Spreadsheet Wizard

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Importing an Excel Worksheet Importing an Excel Worksheet into Accessinto Access

• Options in Get External Data dialog boxOptions in Get External Data dialog box

The file path willbe different onyour computer

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Importing an Excel Worksheet Importing an Excel Worksheet into Accessinto Access

• Changing field names in imported table Changing field names in imported table

Guests enteredin the FieldName box

Field name changesin column heading

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Importing an Excel Worksheet Importing an Excel Worksheet into Accessinto Access

• Excel table imported to AccessExcel table imported to Access

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Copying a Word Table to Copying a Word Table to AccessAccess

• Copy table in Word, Paste in AccessCopy table in Word, Paste in Access• Pasted Word table is not linked Pasted Word table is not linked • Changes in one table do not affect otherChanges in one table do not affect other

• Two methods for adding Word tableTwo methods for adding Word table• Paste table into new, blank Access tablePaste table into new, blank Access table• Paste table into existing Access tablePaste table into existing Access table

• Rule for pasting into existing tableRule for pasting into existing table• Tables to have same number of records Tables to have same number of records

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Copying a Word Table to Copying a Word Table to Access Access

• Selecting the location for copied dataSelecting the location for copied data

Add New Fieldselected

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Linking an Access Table to Linking an Access Table to Excel and WordExcel and Word

• Benefits of linking three programs:Benefits of linking three programs:• Use each program’s strengths Use each program’s strengths • Reduce amount of data entry Reduce amount of data entry

• Use Copy and Paste Special toolsUse Copy and Paste Special tools• Step 1: link Access table to Excel fileStep 1: link Access table to Excel file• Step 2: link Excel data to Word fileStep 2: link Excel data to Word file

• Effects of changing table in AccessEffects of changing table in Access• Data in Excel and Word will also changeData in Excel and Word will also change

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Linking an Access Table to Linking an Access Table to Excel and Word Excel and Word

• Copied data formatted in Excel Copied data formatted in Excel

Accounting Number Format

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Linking an Access Table to Linking an Access Table to Excel and Word Excel and Word

• Excel data pasted into Word Excel data pasted into Word

Linked table

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Linking an Access Table to Linking an Access Table to WordWord• Use Copy and Paste Special toolsUse Copy and Paste Special tools

• Effect of updating Access tableEffect of updating Access table• Linked table in Word also updated Linked table in Word also updated

• Reason for excluding Excel linkReason for excluding Excel link• Excel-type calculations are not neededExcel-type calculations are not needed

• Pasted table can be formatted in WordPasted table can be formatted in Word

• Rule for enabling content in linked filesRule for enabling content in linked files• Open linked files in order of creationOpen linked files in order of creation

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

Linking an Access Table to Linking an Access Table to Word Word

• Completed Word reportCompleted Word report

Burns changedto Abrams

Revenue updatedto $75,000

Microsoft Office 2007-Illustrated Introductory, Windows Vista Edition

SummarySummary

• Edits to Access data are reflected in Edits to Access data are reflected in linked Excel or Word datalinked Excel or Word data

• Get External Data: Access import toolGet External Data: Access import tool

• Import Spreadsheet Wizard: adapts Import Spreadsheet Wizard: adapts Excel data to an Access table Excel data to an Access table

• A A Word table is pasted into an Word table is pasted into an Access table without linksAccess table without links

• Link Word, Access, and Excel data to Link Word, Access, and Excel data to utilize each program’s strengthsutilize each program’s strengths