Integrating MS Office tools

1. Integrating MS Office tools

2. Importing Excel Worksheet into Access • Start Excel • Copy VancouverTours_B1.xlsx to VancouverTourPrices.xlsx • Delete the first line ( “Vancouver Tours”) • Start Access • Click Blank Database • In the File Name text box, enter “VancouverTours” • Click the Browse button, next to the text box • Navigate to the directory where you want to store this file. • Click OK, then click Create. • A new database opens in Access.

3. Importing Excel Worksheet into Access • Click External Data tab • In the Import group, click Excel. • Click Browse, and navigate to the drive and folder where you stored your file. • Click VancouverTourPrices.xlsx and click open. • In the Get External Data menu, you have 3 options • 1 & 2 will not update data when changes are made in Excel • 3 will update data. • Choose 1 for this exercise. • In the Import Spreadsheet Wizard, click Next to accept Sheet1 • Make sure that the first row contains column headings check box is selected. • Click Next , Click Participants column, type Guests.

5. Click Next, click Next to let Access set the primary key • Type Tours as the table name.

6. Query: Find all tours less than $3,000 • Click Create tab • Click Query Design • Show Table message box appears • Click the table(s) you are interested • The table(s) will show • Enter query criteria such as • All tours whose cost is less than $3,000.

7. Importing a Word Table to Access • You are given a Word document containing two tables. You are to incorporate into the Word report. • Table 1 contains tour information that was not included in the tour list you imported to Access from Excel. • Table 2 contains tour guide information.

8. Importing a Word Table to Access • Open VancouverTourInformation.docx • Move the mouse pointer over the upper-left corner of the top table. Click the table select button. • Copy. • Bring up Microsoft Access Program we invoked earlier. • Click Add New Field in the Tours table. • We want to add the data from the Word table into the Tours database table. Make sure that the number of records is the same in both tables. • Click Home tab, click Paste button, then click yes. • Now, we have duplicate columns, TourName. Delete one. Switch to Design View. • Move the last two rows above the Price row. • Close the Tours table.

9. Importing a Word Table to Access • Switch to Word, copy the 2nd table. • Back to Access. Select the first column and paste it. • Will create a new Access table for Tour guides. • Double-click ID and replace it with GuidesID. • Close the table and name it as Guides.

10. Linking Access table to Word & Excel • Increase efficiency • Reduce the need to enter the same data more than once • Can create Copy and Paste Special commands to create a link between an Access database object and an Excel file, where you can perform calculations and create charts. • You can then copy the Excel data, calculations, and charts to a Word document. • When you change the data in the source Access database, the linked data in both Excel and Word update to reflect the new information.

11. Linking Access table to Word • In Vancouver Tours database, click Guides table, copy to the clipboard • Open Word • Click the Paste button list arrow • Click Paste Special • Click Paste link option button • Click Formatted Text (RTF) • Click OK. • Switch to Word • You will see the changes

12. Linking Access table to Word & Excel • Open Tours table in Access. Copy to the clipboard • Create a new workbook in Excel • Click Paste button list arrow, then click Paste Link