integrating ms office tools
Skip this Video
Download Presentation
Integrating MS Office tools

Loading in 2 Seconds...

play fullscreen
1 / 12

Integrating MS Office tools - PowerPoint PPT Presentation

  • Uploaded on

Integrating MS Office tools. 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”

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Integrating MS Office tools' - eloise

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
importing excel worksheet into access
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.
importing excel worksheet into access1
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.
query find all tours less than 3 000
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.
importing a word table to access
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.
importing a word table to access1
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.
importing a word table to access2
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.
linking access table to word excel
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.
linking access table to word
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
linking access table to word excel1
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