Integrating Word Excel Access

XP

Integrating Microsoft Office XP Tutorial 2 – Integrating Word, Excel, and Access

New Perspectives on Integrating Microsoft Office XP Tutorial

1

XP

Collect text from Office documents on the Clipboard Task Pane • The Clipboard Task Pane (also called the Clipboard) is a feature that is common to all of the Office applications. • It is a simple way to cut/copy and paste information from one application into another. • The items you cut or copy stay on the Clipboard until you exit Office.

New Perspectives on Integrating Microsoft Office XP Tutorial

2

Use the clipboard

XP

• To use the Clipboard, open the application you want to use, click Edit on the menu bar and then click Office Clipboard. • The Clipboard Task Pane opens. • As you work in your document, anything you copy or cut will appear in the Clipboard. • When you want to paste that item somewhere else (either in the same document or a different one), place your cursor in the appropriate spot in your document and then click the item on the Clipboard to be inserted. New Perspectives on Integrating Microsoft Office XP Tutorial

3

An empty clipboard

XP

This figure shows the Clipboard Task Pane on the right side of the window, and you can see that the Clipboard is currently empty. This logo has been selected and is about to be copied to the Clipboard

New Perspectives on Integrating Microsoft Office XP Tutorial

4

The Clipboard with an item copied to it

XP

The selected logo seen in the previous slide has now been copied to the Clipboard.

New Perspectives on Integrating Microsoft Office XP Tutorial

5

XP

Paste selections from Office documents to Word • Once you have copied an item to the Office Clipboard, click where you want to insert the item, then click the item in the Clipboard. • The text or object will appear in the document. • When you paste text, the Paste Options button appears. • You use this button to determine how the information you pasted should be formatted in the Word document. New Perspectives on Integrating Microsoft Office XP Tutorial

6

Pasting text and the Paste Options button

XP

The text shown as selected in the Clipboard Task Pane has been pasted into the document. Note the Paste Options button has appeared.

New Perspectives on Integrating Microsoft Office XP Tutorial

7

Paste Options menu commands

XP

If you click the Paste Options button shown in the previous slide, the Paste Options menu will appear. The table below shows the commands on that menu, and a description of each command’s actions.

New Perspectives on Integrating Microsoft Office XP Tutorial

8

Find Office documents with the Search TaskXP Pane • The Search Task Pane is a tool that enables you to find files that contain the text you specify. • This is referred to as a basic search. • You can also use the Search Task Pane to search for a file based on it's properties. • To conduct a search, you enter your search criteria and then click the Search button.

New Perspectives on Integrating Microsoft Office XP Tutorial

9

Search Pane features

XP

The Search Pane allows you to enter a word or phrase you want to find, the location where you want to search, and the type of documents you want to search.

New Perspectives on Integrating Microsoft Office XP Tutorial

10

The Search Task Pane with search criteria

XP

In this illustration, the text “tulip price table” is the text for which the search is being conducted.

New Perspectives on Integrating Microsoft Office XP Tutorial

11

The Search Results Task Pane

XP

This figure shows that the search being done in the previous slide has located one document containing the text being searched for. In this case, it found one document with the search text. Click the file name in the Search Results pane to open that file.

New Perspectives on Integrating Microsoft Office XP Tutorial

12

XP

Learn about importing and exporting data • Importing data from one Office application to another converts the data from its original source program format to a format that is supported by the destination program. – When you import, you start in the destination program and import from the source program

• Exporting is like importing in that it converts data from one program format to another. – When you export, you start in the source program and export to the destination program

• The advantage of importing and exporting is that you can use the destination program's tools and features to view and manipulate the data. New Perspectives on Integrating Microsoft Office XP Tutorial

13

XP

Use the Import Spreadsheet Wizard • Depending upon which applications you are trying to import or export to or from, you will see different dialog boxes. • When you import Excel data into Access, the Import Spreadsheet Wizard will appear. • The Import Spreadsheet Wizard will step you through the process and allow you to determine exactly how the data will be imported. New Perspectives on Integrating Microsoft Office XP Tutorial

14

Import an Excel list into an Access database

XP

• To Import an Excel list into an Access database: – Open the Access database into which you want to import the Excel list – Click Get External Data from the File menu and select Import – When the Import dialog box opens, click the File of type list arrow and then click Microsoft Excel – Locate the Excel workbook that you want to import the list from and then double-click on the filename

• This will open the Access Import Spreadsheet Wizard dialog box. • Follow the directions in the wizard to complete the import process. New Perspectives on Integrating Microsoft Office XP Tutorial

15

XP

The Import Spreadsheet Wizard dialog box 1 This dialog box allows you to specify if you want all worksheets imported or specific ranges of sheets. When you click the Next button, the next wizard dialog box will give you the option of storing your data in a new table, or an existing table.

New Perspectives on Integrating Microsoft Office XP Tutorial

16

Import Spreadsheet Wizard dialog box 3

XP

This wizard dialog box allows you to specify information about the fields being imported.

New Perspectives on Integrating Microsoft Office XP Tutorial

17

A database imported as a worksheet

XP

This figure shows an Access database into which an Excel worksheet has been imported. The column headings in Excel were converted to field headings in Access. The rows in Excel were converted to records in Access. New Perspectives on Integrating Microsoft Office XP Tutorial

18

XP

Query an Access database • The easiest way to create a query in Access is to use the Simple Query Wizard, which takes you step-by-step through setting up a query. • To create a query, select Queries on the Object bar and then double-click Create query by using wizard. • This will open the Simple Query Wizard.

New Perspectives on Integrating Microsoft Office XP Tutorial

19

Create a Query

XP

To create a new query, click the Queries object in the Objects bar, then click one of the options in the window.

New Perspectives on Integrating Microsoft Office XP Tutorial

20

The Simple Query Wizard dialog box 1

XP

This dialog box appears when you click the “Create query by using wizard” option in the Queries window. In this dialog box you select the table or other query that the new query is to be based on, and choose the fields to be contained in the query.

New Perspectives on Integrating Microsoft Office XP Tutorial

21

Simple Query Wizard options

XP

• As you move through the Simple Query Wizard, you can make a variety of selections: – The table or query from which the query will be created – The fields that will be used – Whether you want to view detail or summary information – The title of the query

New Perspectives on Integrating Microsoft Office XP Tutorial

22

The Simple Query Wizard with table selected

XP

This figure shows the same dialog box as a previous slide, but a table to use for the query has now been selected. The Available fields box shows the fields for that table. Click on a field name to select it.

Then click the button to move it to the Selected fields box. New Perspectives on Integrating Microsoft Office XP Tutorial

23

Results of the new query

XP

After all fields have been selected, click the Finish button in the Simple Query Wizard dialog box. The query results will then appear in a datasheet.

New Perspectives on Integrating Microsoft Office XP Tutorial

24

Open the query in Design view

XP

Once the query has been created, you can click the View button list arrow, and then click Design View to open the query in the Design view window. You can then add criteria to limit the selection, choose to show or hide fields, or add sort keys.

New Perspectives on Integrating Microsoft Office XP Tutorial

25

Results of the modified query

XP

After making changes to the query in Design view, click the Run button on the Query Design toolbar to view the results datasheet with the changes made to the query. If you want to make additional changes, return to Design view, or save the query.

New Perspectives on Integrating Microsoft Office XP Tutorial

26

Export an Access query to a Word document

XP

• A query created in Access can be exported. • To export a query, convert it to rich text format—a format that preserves the layout of the data. • This conversion makes the data into a text file with the .rtf extension. • This file can then be inserted into a Word document.

New Perspectives on Integrating Microsoft Office XP Tutorial

27

XP

How to export a query • To export an Access query to a Word document: – Open the query in datasheet view, and click the File menu – Click the Export option to open the Export dialog box – Specify a file name or keep the default name – Change the Save as type option to “Rich Text Format” – Change the Save in location to the same folder containing the Word document, if necessary – Click the Export All button to save the file in the specified location in rich text format New Perspectives on Integrating Microsoft Office XP Tutorial

28

The Export dialog box

XP

Specify drive and folder location here. Enter the name to save the file as in this box.

Specify Rich Text Format here. This is a special text format that retains formatting options.

After all settings have been entered, click the Export All button to save the file. New Perspectives on Integrating Microsoft Office XP Tutorial

29

XP

Insert an exported query into a Word document • To insert the exported query into Word: – Open or select from the taskbar the Word document to receive the query – Place the insertion point at the location in the Word document where you want the query to go – Click the Insert menu, then click File to open the Insert dialog box – Change the Files of type option to Rich Text Format, and locate the drive and folder containing the file – Select the file name and click the Insert button to insert the query into the Word document

New Perspectives on Integrating Microsoft Office XP Tutorial

30

A query imported into a Word document

XP

This figure shows a database query that has been exported to an RTF file and then inserted into a Word document.

Like importing, that data is now stored and maintained in two separate locations.

New Perspectives on Integrating Microsoft Office XP Tutorial

31