Transcript Project 1 - Gonzaga University

Project 10
Sharing Excel 2000 Data with Other
Microsoft Office 2000 Applications
Jason C. H. Chen, Ph.D.
Professor of
Management Information Systems
School of Business Administration
Gonzaga University
Spokane, WA 99258, USA
[email protected]
Excel Skills








Change Workbook Properties.
Complete A Microsoft Word 2000 Mail Merge Using An Excel List
As The Data Source.
Create A Web Query.
Create An Excel Workbook From A Microsoft Access 2000
Database Using Microsoft Query.
Embed An Excel Workbook In A Word 2000 Document.
Link An Excel Worksheet To A PowerPoint Presentation.
Modify A Word Mail Merge Main Document.
Update A Web Query.
2
Key Terms

Microsoft Query


An Office program for bringing data from external sources
into Microsoft Excel. By using Query to retrieve data from
existing databases and files, you do not have to retype the
data you want to enter into a worksheet.
Object Linking and Embedding

A program-integration technology you can use to share
information between Windows programs. All of the Office
programs support OLE, so you can share information
through linked and embedded objects."
3
Key Terms

Query


serial value


In Microsoft Query or Microsoft Access, a means of finding
the records that meet the specific criteria you define about
the data that is stored in a data source."
A sequential number representing dates and times as
fractions, since time is considered a portion of a day. Dates
and times are values and, therefore, can be added,
subtracted, and included in other calculations.
Web Query

A query you define to retrieve text or data that is in tables or
preformatted areas on a Web page.
4
Objectives






Use Microsoft Query to create an Excel workbook
from a Microsoft Access database
Change the properties of an Excel workbook
Embed an Excel worksheet in a Microsoft Word
document
Use an Excel list as a Microsoft Word Mail Merge
source
Link an Excel worksheet to a Microsoft PowerPoint
presentation
Create and Update Web Queries
5
Running Case

You will assist the Selections, Inc. Finance
Department in using Excel and other Office
2000 applications to distribute and update
information.
6
Project Challenge

Mr. Traylor’s department has decided to send
a letter explaining the new employee
retirement plan to all employees. This letter
will contain an Excel workbook employees can
use to estimate their future retirement
earnings. He has a sample Access database
file containing employee data. You will need
to import this data form Access into Excel,
and then add data listing each employee’s
current age.
7
Project Challenge


Then Mr. Traylor wants you to help him build a
PowerPoint presentation explaining the plan that he
will eventually send to the manager of each
Selections, Inc. store. This presentation will also
include the workbook for calculating future
retirement earnings, so that any given presentation
of the plan can be interactive, where the presenter
can explore different investment scenarios.
Finally, Mr. Traylor He wants you to develop a simple
worksheet that will list the major market indices, and
can be updated daily on the Web.
8
Sharing Excel 2000 Data with
other MS/2000 Applications


Use Query to retrieve data from your
corporate databases and files such as
MS/Access databases.
Files needed

Task 1,2


Tasks 3-5


Selections.mdb (Access database)
401K Calculator.xls and 401K Letter.doc
Task 6

401K Calculator.xls and Selections 401K Plan.ppt
9
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)
1. Click Data, Get External
Data, and choose New
Database Query.
2. The Choose Data Source
dialog box will appear.
Choose MS Access 97
Database* (or other
Access) and click OK.
10
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)
3. MS Query will attempt to connect to a
database, so the Select Database dialog box
will appear. Locate the Selections.mdb
database file on the floppy diskette (or
other appropriate location) and click OK.
4. The Query Wizard – Choose Columns
dialog box will appear. Click the button to
expand the list.
11
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)
4 (cont.). The Query Wizard – Choose
Columns dialog box will appear. Click the
button to expand the list.
Then add all columns except HomePhone
and PayRate from the Available Tables and
Columns: list to the Columns in your
Query:list. Click Next when you done.
You may move to step 5 for a
normal process, however, you
may try the following steps:
4.a) If you press [Esc], you
will be asked …
12
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)
4.a) If you press [Esc], you
will be asked “Do you want to
continue ..”
4.b) Click Yes, the Microsoft
Query window (from
MS/Access) will appear.
13
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)
4.c) Click [X]
4 d. Microsoft Query will have you
verify the location for the imported
data. Select the defaults shown in
Figure, and click OK.
Notice that the result from this step
will not include the sorting criteria.
14
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)
The result from step 4 d).
Notice that the result from this step will not include the sorting criteria.
15
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)
5. In the next step of the Query
Wizard you can choose to filter the
database by one or more criteria.
Click Status in the Column to Filter:
list. Click the [Down arrow] key on
the right-side of the options.
Then set two criteria: for the first
criteria filter records where Status
is equal to Three-Quarter Time,
and for the second criteria, filter
records where Status is equal to
Full Time. When the setting
matches those shown in the Figure,
click Next.
16
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)
6. In the next step of the Query
Wizard you can specify a sort order
for you list. Select Lastname and
click Next.
7. You are almost finished! This last
step of Wizard allows you to save
the query and return data to Excel.
Since you are merely working with a
sample of records from the
Selections database, there is no
reason to save the query. When your
options are the same as those shown
in the Figure, click Finish.
17
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)
8. Microsoft Query will have you
verify the location for the imported
data. Select the defaults shown in
Figure, and click OK.
The data is imported into the first
worksheet of the workbook.
18
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)
9. Type Current Age in cell J1 and press [Enter]. Format the cell as Bold, Align
right, and adjust the column width as necessary.
10. Type =TRUNC((NOW()-H2)/365) as the formula in cell J2.
19
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)
11. Use the Fill handle to copy the formula down the column through row 10.
12. Click cell A1. Save the workbook as Employee List.xls.
20
Goals:
1) To specify the purpose of the document in the File
Properties
2) To add a comment in cell A1.
Task 2: To Change Workbook Properties
1. Click File, Properties
2. Type Employee Address List in the Title:
text box.
3. Press [Tab] twice. Type your name in the
Author: text box.
4. Press [Tab] twice. Type Selections, Inc. in
the Company:text box.
5. Press [Tab] three times. Type File created
from the Selections.mdb database.
6. Click the checkbox to save a preview
picture. The settings should match those
shown in the Figure.
7. Click OK.
21
Task 2: To Change Workbook Properties (cont.)
8. Click Insert, Comment.
(cell A1 should be the active cell)
9. Type Workbook created for use as a
mail merge source document as the
comment for cell A1.
10. Click cell A1 and the click Save (or save
as).
11. Place the insertion point over cell A1. A
comment similar to the one shown in
Figure is displayed.
22
Embedding an Excel Workbook in a
MS/Word Document

MS/Office supports Object Linking and
Embedding (OLE), a technology for sharing
data among Office applications.

When you embed an object, such as an Excel
Workbook in a Word document, information in the
destination file (Word document) does not change
if you modify the source file (Excel workbook).
23
Task 3: To Embed an Excel Workbook in a Word
Document
1. Click File, Open and open the 401K
Calculator.xls workbook from the
floppy diskette (or other
appropriate folder).
2. Highlight the range A1:D13 and
click Copy as shown in the
Figure.
3. Launch MS/Word.
4. Click File, Open, and locate the
401K Letter.doc document, select
it and click Open, as shown in the
Figure.
24
Task 3: To Embed an Excel Workbook in a Word
Document (cont.)
4 (cont.). The letter contains placeholders for each of the merge fields you
will insert as well as the Excel workbook.
25
Task 3: To Embed an Excel Workbook in a Word
Document (cont.)
5. Navigate to the second page of the letter and highlight the placeholder
titled [Embed the Worksheet Object Here], as shown in the Figure.
26
Task 3: To Embed an Excel Workbook in a Word
Document (cont.)
6. Press [Del] to delete the placeholder.
7. Press [Enter] twice and move the insertion point to immediately before the second
Paragraph Mark.
8. Click Edit, Past Special (you may need to expand the selections from the Edit
command)
27
Task 3: To Embed an Excel Workbook in a Word
Document (cont.)
9. Select Microsoft Excel
Workbook Object in the
Paste Special dialog box and
click OK.
The workbook is embedded in
the document, as shown in
the Figure. You may resize
the embedded worksheet if
necessary.
10. Save your changes to the
401K Letter.doc document.
11. Close the 401K
Calculator.xls
workbook. When
Excel asks if you want
to save the
information on the
Clipboard, click NO.
28
Word Mail Merge


You are now ready to prepare a Word Mail
Merge.
You have three tasks you must first complete:



Specify the kind of merge document,
Specify the data source,
Edit the main document by adding field codes.
29
Task 4: To Modify a Word Mail Merge Main Document
1. Click the 401K Letter.doc button on the
Windows Taskbar to activate Word.
2. Navigate to the beginning of the document.
3. Click Tools, Mail Merge
4. Click the Create button in the Mail Merge
helper and choose Form Letters.
30
Task 4: To Modify a Word Mail Merge Main Document
(cont.)
5. Click the button to use the
Active Window,
6. Click the Get Data button
and select open Data
Source.
31
Task 4: To Modify a Word Mail Merge Main Document
(cont.)
7. Navigate to your floppy
diskette (or other
appropriate drive), and
change the Files of type
selection in the Open
Data Source dialog box to
MS Excel worksheets.
When your settings match
those shown in the
Figure, click Open.
Word will locate the data source.
When the dialog box shown in
the Figure appears, select Entire
Worksheet and click OK.
32
Task 4: To Modify a Word Mail Merge Main Document
(cont.)
8. Word will inform
you that you need to
edit the main
document. Click Edit
Main Document, as
shown in the Figure.
33
Task 4: To Modify a Word Mail Merge Main Document
(cont.)
9. For each placeholder in
the letter, replace the text
for the placeholder with
the appropriate merge
fields by clicking the Merge
field button on the Mail
Merge toolbar.
34
Task 4: To Modify a Word Mail Merge Main Document
(cont.)
When you are finished, the
letter will resemble the one
shown in the Figure.
10. Save the letter.
35
Task 5: To Complete a MS/Word Mail Merge using an
Excel List as the Data Source
1. Launch MS/Word if it
is not currently running,
and open the 401K
Letter.doc document
from the floppy diskette.
2. Click the Merge
button
on the
mail Merge toolbar.
3. The Merge dialog box
will appear. Click Merge.
36
Task 5: To Complete a MS/Word Mail Merge using an
Excel List as the Data Source (cont.)
Word merges the main document
with the data source as a new
document with the default name
Form Letters1.
You will notice that this document
consists of 18 pages.
4. Click Save, and name the
document containing the merged
letters as 401K Letters-
Merged.doc
37
Task 5: To Complete a MS/Word Mail Merge using an
Excel List as the Data Source (cont.)
Move to the third page to view the
next new document created.
5. Click the Print Preview button on
Word’s Standard toolbar, and
change the preview zoom to 10%.
The nine merged letters will appear
in the Preview window (see next
slide)
6. Close the preview.
7. Close the two open Word
documents. Save any changes if
you are prompted to do so.
Tip:
When you close the main document, Word will also close the Excel
workbook containing the data source.
38
Task 5: To Complete a MS/Word Mail Merge using an
Excel List as the Data Source (cont.)
39
Linking an Excel Worksheet to a
MS/Powerpoint Slide

Link the 401K Calculator workbook to the
PowerPoint presentation




Therefore, during the presentation the presenter
only changes the data in the workbook and then
refresh the slide.
With the a linked object, the original information
remains stored in the source file.
The destination file displays a representation of
the linked information but stores only the location
of the original data.
When you link an object, the size of the
destination file remains virtually unchanged.
40
Task 6: To Link an Excel Worksheet to a Powerpoint
Presentation
1. Open the 401K
Calculator.xls workbook from
your floppy diskette.
2. Highlight cells A1:D13,
right-click, and select Copy.
3. Open the Selections 401K
Plan.ppt presentation from
your floppy diskette.
4. Use the vertical scroll bar to
navigate to the third slide in
the presentation.
41
Task 6: To Link an Excel Worksheet to a Powerpoint
Presentation (cont.)
5. Click Edit, Paste Special.
6. In the Paste Special dialog
box, choose MS/Excel
Worksheet Objects in the AS:
list, and check the Past Link
option button. When the
settings match those shown in
the Figure, click OK.
7. Resize and center the linked
worksheet. When you are
finished, your slide should
look similar to the one shown
in the next Figure.
8. Save the presentation.
9. You may press [F5] to run
the slide show.
42
Task 6: To Link an Excel Worksheet to a Powerpoint
Presentation (cont.)
43
Task 6: To Link an Excel Worksheet to a Powerpoint
Presentation (cont.)
Change the age from 21 to
30 on the worksheet. You
should notice that the
associated information are
updated automatically on
the powerpoint
presentation.
10 When you done, close
Powerpoint and the
workbook.
44
Task 6: To Link an Excel Worksheet to a Powerpoint
Presentation (cont.)
TIP: When you open the
presentation, Office will also
open Excel and the linked
worksheet object.
If you change data in the
linked workbook and switch
back to Powerpoint, you can
right-click the linked
worksheet and choose Update
Link.
45
Creating and Updating Web
Queries

To analyze data on a Web page, you can use
a Web query



to retrieve a single table,
multiple tables, or
all of the text on the a Web page
46
Task 7: To Create a Web Query
1. Click the New button
to create a blank workbook.
2. Click Data, Get External
Data, and select New Web
Query.
47
Task 7: To Create a Web Query
3. The New Web Query dialog box will
appear. Type
http://www.djfpc.com/markets.html
as the Web address in Step 1, check the
option to view the entire page in Step2, and
select the option to display full HTML
formatting in Step 3. When the settings
match the Figure, click OK.
48
Task 7: To Create a Web Query
49
Task 7: To Create a Web Query
50
Task 4: To Modify a Word Mail Merge Main Document
(cont.)
Once the Word file is saved …
its associated merged file must
be placed on the right drive
properly, otherwise, a warning
message will be displayed.
51
Solution- MS Query and OLE
52
Solution – OLE in PowerPoint
53
Solution – Web Queries
54
Project 10
Sharing Excel 2000 Data with Other
Microsoft Office 2000 Applications