<< . .

. 26
( : 51)



. . >>

cally. If it does not, select Edit _ Office Clipboard.

To see an example of how this works, try copying an Excel chart into a Microsoft Word
report. First, select the chart in Excel by clicking it once. Then copy it to the Clipboard by
choosing Edit _ Copy. Next, activate the Word document into which you want to paste the
copy of the chart and move the insertion point to the place where you want the chart to
appear. When you select Edit _ Paste from the Word menu bar, the chart is pasted from the
Clipboard and appears in your document (see Figure 12-1).




Figure 12-1: An Excel chart has been added to this Word document.
Part II ¦ Collaborating and Integrating with Office 2003
282


Windows applications vary in the way that they respond to pasted data. If the Edit _ Paste
Note command is not available (is grayed on the menu) in the destination application, the application
can™t accept the information from the Clipboard. If you copy a range of data from Excel and
paste it into Word, Word creates a table when you paste the data. Other applications may
respond differently to Excel data.

The copy-and-paste technique is static. In other words, no link exists between the information
that you copy from the source application and the information that you paste into the
destination application. If you™re copying from Excel to a Word document, for example, the
Word document will not reflect any subsequent changes that you make in your Excel
worksheet or charts. Consequently, you have to repeat the copy-and-paste procedure to update
the destination document with the source document changes. The next topic presents a way to
get around this limitation.

Linking data
If you want to share data that may change, the static copy-and-paste procedure described in
the preceding section isn™t your best choice. Instead, you can create a dynamic link between
the data that you copy from one Windows application to another. In this way, if you change
the data in the source document, you don™t also need to make the changes in the destination
document because the link automatically updates the destination document.

Applications vary in how they handle linked data. In some situations, you may need to update
Note
the linked data manually.

When would you want to use this technique? If you generate proposals by using Word, for
example, you may need to refer to pricing information that you store in an Excel worksheet.
If you set up a link between your Word document and the Excel worksheet, you can be sure
that your proposals always quote the latest prices. Not all Windows applications support
dynamic linking, so you must make sure that the application to which you are copying is
capable of handling such a link.
Setting up a link from one Windows application to another varies slightly from application to
application. These are the general steps to take:
1. Copy the information to the Clipboard.
2. Switch to the destination application.
3. Select the appropriate command in the destination application to paste a link. This is
usually Edit _ Paste Special.
4. In the dialog box that appears, specify the type of link that you want to create. (See
the next section, “Copying Excel data to Word,” for an example.)
Chapter 12 ¦ 283
Sharing Excel Data with Other Applications


Keep in mind the following information when you™re using links between two applications:
¦ Not all Windows applications support linking. Furthermore, you can link from, but
not to, some programs.
¦ When you save an Excel file that has a link, you save the most recent values with the
document. When you reopen this document, Excel asks whether you want to update
the links.
¦ Links can be broken rather easily. If you move the source document to another
directory or save it under a different name, for example, the destination document™s
application won™t be able to update the link. In such a case, you™ll need to re-
establish the link manually.
¦ You can use the Edit _ Links command to break a link. After breaking a link, the
data remains in the destination document, but it is no longer linked to the source
document.
¦ In Excel, external links are sometimes stored in array formulas. If so you can
modify a link by editing the array formula.

Copying Excel data to Word
One of the most frequently used software combinations is a spreadsheet and a word
processor. This section discusses the types of links that you can create by using Microsoft
Word to create documents that include data from Excel.
Figure 12-2 shows the Paste Special dialog box from Microsoft Word after a range of data
has been copied from Excel. The result that you get depends on whether you select the Paste
or the Paste Link option and on your choice of the type of item to paste. If you select the
Paste Link option, you can check the Display as Icon check box in order to have the
information pasted as an icon. If you do so, you can double-click this icon to activate the
source worksheet.




Figure 12-2: Use the Paste Special dialog box to specify the type of link to create.
Part II ¦ Collaborating and Integrating with Office 2003
284


Pasting without a link
Often, you don™t need a link when you copy data. For example, if you™re preparing a report in
your word processor and you simply want to include a range of data from an Excel
worksheet, you probably don™t need to create a link.
If you select one of the choices in the Paste Special dialog box with the Paste option selected,
the data is pasted without creating a link.

The pasted data looks the same regardless of whether the Paste or Paste Link option is
Tip
selected.

Some Excel formatting does not transfer when pasted to Word as formatted text. For example,
Word doesn™t support vertical alignment for table cells (but you can use Word™s paragraph
formatting commands to apply vertical alignment).

Pasting with a link
If you think the data that you™re copying will change, you may want to paste a link. If you
paste the data by using the Paste Link option in the Paste Special dialog box, you can make
changes to the source document, and the changes appear in the destination application (a few
seconds of delay may occur). You can test these changes by displaying both applications on-
screen, making changes to the source document, and watching for them to appear in the
destination document.


Embedding Objects in Documents
Using Object Linking and Embedding (OLE), you can also embed an object to share
information between Windows applications. This technique enables you to insert an object
from another program and use that program™s editing tools to manipulate it. The OLE objects
can be such items as these:
¦ Text documents from other products, such as word processors
¦ Drawings or pictures from other products
¦ Information from special OLE server applications, such as Microsoft Equation
¦ Sound files
¦ Video or animation files
Many (but certainly not all) Windows applications support OLE. Embedding is often used for
a document that you will distribute to others. It can eliminate the need to send multiple
document files and help avoid broken link problems.
You can embed an object into your document in either of two ways:
Chapter 12 ¦ 285
Sharing Excel Data with Other Applications


¦ Choose Edit _ Paste Special and then select the “object” choice (if it™s available). If
you do this, select the Paste option rather than the Paste Link option.
¦ Select Insert _ Object.
Caution
Embedding an object can cause a dramatic increase in the size of your document.


Some applications ” such as those in Microsoft Office ” allow you to embed an object by
Tip
dragging it from one application to another.

The following sections discuss these two methods and provide a few examples using Excel
and Word.

Embedding an Excel range in a Word document
This example embeds in a Word document the Excel range shown in Figure 12-3.




Figure 12-3: This worksheet includes a range that will be embedded in
a Word document.

To start, select A1:C17 and copy the range. Then activate (or start) Word, open the document
in which you want to embed the range, and move the insertion point to the location in the
document where you want the table to appear. Choose Word™s Edit _ Paste Special
command. Select the Paste option (not the Paste Link option) and choose the Microsoft Excel
Worksheet Object format. Click OK, and the range appears in the Word document.
The pasted object is not a standard Word table. For example, you can™t select or format
individual cells in the table. Furthermore, it™s not linked to the Excel source range. If you
change a value in the Excel worksheet, the change does not appear in the embedded object in
the Word document.
Part II ¦ Collaborating and Integrating with Office 2003
286

If you double-click the object, however, you notice something unusual: Word™s menus and
toolbars change to those used by Excel. In addition, the embedded object appears with
Excel™s familiar row and column borders. In other words, you can edit this object in place by
using Excel™s commands. Figure 12-4 shows how this looks. To return to Word, just click
anywhere in the Word document.




Figure 12-4: Double-clicking the embedded Excel object enables you to edit it in place.
Note that Word now displays Excel™s menus and toolbars.


Remember that no link is involved here. If you make changes to the embedded object in Word,
Caution
these changes do not appear in the original Excel worksheet. The embedded object is com-
pletely independent from the original source.

By using this technique, you have access to all of Excel™s features while you are still in Word.
Chapter 12 ¦ 287
Sharing Excel Data with Other Applications



You can accomplish the embedding previously described by selecting the range in Excel and
Tip
then dragging it to your Word document. In fact, you can use the Windows desktop as an
intermediary storage location. For example, you can drag a range from Excel to the desktop
and create a scrap. Then you can drag this scrap into your Word document. The result is an
embedded Excel object.


Creating a new Excel object in Word
The preceding example embeds a range from an existing Excel worksheet into a Word
document. This section demonstrates how to create a new (empty) Excel object in Word. This
may be useful if you™re creating a report and need to insert a table of values that doesn™t exist
in a worksheet.

Tip You could insert a normal Word table, but you can take advantage of Excel™s formulas and
functions in an embedded Excel worksheet.

To create a new Excel object in a Word document, choose Insert _ Object in Word. Word
responds with the Object dialog box. The Create New tab lists the types of objects that you
can create. (The contents of the list depend on the applications that you have installed on your
system.) Choose the Microsoft Excel Worksheet option and click OK.
Word inserts an empty Excel worksheet object into the document and activates it for you, as
shown in Figure 12-5. You have full access to Excel commands, so you can enter whatever
you want into the worksheet object. After you finish, click anywhere in the Word document.
You can, of course, double-click this object at any time to make changes or additions.




Figure 12-5: This Word document now contains an empty Excel worksheet object.
Part II ¦ Collaborating and Integrating with Office 2003
288

You can change the size of the object while it™s activated by dragging any of the sizing
handles (the little black squares and rectangles) that appear on the borders of the object. You
also can crop the object so that when it isn™t activated, the object displays only cells that
contain information. To crop an object in Word, select the object so that you can see sizing
handles. Then, display Word™s Picture toolbar (right-click any toolbar button and choose
Picture). Click the Cropping tool (it looks like a pair of plus signs), and then drag any sizing
handle on the object.

Note Even if you crop an Excel worksheet object in Word, double-clicking the object gives you ac-
cess to all rows and columns in Excel. Cropping changes only the displayed area of the object.


Tip When you click outside the Excel worksheet object, the worksheet™s scrollbars, tabs, gridlines,
and so on will disappear. Any data that you have added will remain visible, however.


Embedding objects in an Excel worksheet
The preceding examples involve embedding Excel objects in a Word document. The same
procedures can be used to embed other objects into an Excel worksheet.
For example, if you have an Excel workbook that requires a great amount of explanatory text,
you have several choices:
¦ You can enter the text into cells. This, however, is tedious and doesn™t allow much
formatting.
¦ You can use a text box. This is a good alternative, but it doesn™t offer many
formatting features.
¦ You can embed a Word document in your worksheet. This gives you full access to
all of Word™s formatting features.
To embed an empty Word document into an Excel worksheet, choose Excel™s Insert _ Object
command. In the Object dialog box, click the Create New tab and select Microsoft Word
Document from the Object type list.
The result is a blank Word document, activated and ready for you to enter text. Notice that
Word™s menus and toolbars replace Excel™s menus and toolbars. You can resize the document
as you like, and the words wrap accordingly.
You can embed many other types of objects, including audio clips, video clips, MIDI
sequences, and even an entire Microsoft PowerPoint presentation.
Microsoft Office includes several additional applications that you may find useful. For
example, you can embed a Microsoft Equation object in an Excel document to graphically
illustrate a formula that you use in a worksheet.

Some of the object types listed in the Object dialog box can result in quite useful and interesting
Tip
items when inserted into an Excel worksheet. If you™re not sure what an object type is, try
adding the object to a blank Excel workbook to see what is available.
Chapter 12 ¦ 289
Sharing Excel Data with Other Applications



Working with XML Data
This section introduces the new XML features found in Excel 2003. This feature provides
another way to share data with other applications.

This section is relevant only to those who use Excel 2003. If you™re using Excel 2000 or Excel
New
2002, you™ll find that you can open some XML files in Excel (using the File _ Open command).
Feature
But the features described here will not work.


What is XML?
XML is a accepted standard that enables exchange of data between different applications.
XML is a markup language, just as HTML is a markup language.
XML uses tags to define elements within a document. XML tags define the document™s
structural elements and the meaning of those elements. Unlike HTML tags, which specify
how a document looks or is formatted, XML can be used to define the document structure
and content. Consequently, XML separates a document™s content from its presentation.
Following is a very simple XML file that contains data from an e-mail message.
<?xml version=”1.0" encoding=”UTF-8"?>
<message>
<to>Bill Smith</to>
<from>Mark Jackson</from>
<subject>Meeting date</subject>
<body>The meeting will be at 8:00 a.m. on Tuesday</body>
</message>

When the file is viewed in Internet Explorer, the browser displays it as a structured document
(as shown in Figure 12-6).




Figure 12-6: Internet Explorer displays XML files in a structured format.
Part II ¦ Collaborating and Integrating with Office 2003
290

Unlike HTML, the XML specification does not specify the tags themselves. Rather, it
provides a standard way to define tags and relationships. Because there are no predefined
tags, XML can be used to model virtually any type of document.

This is an admittedly cursory overview of XML. Fact is, XML can be extremely complex. Many
Note
entire books are devoted to XML.

The two sections that follow consist of simplistic examples to give you a feel for how Excel
handles XML.

Importing XML data by using a map
This example uses the worksheet shown in Figure 12-7. This worksheet uses data in column
B to generate a loan amortization schedule. Assume that a back-end system generates XML
files, and each file contains data for a customer. An example of such a file is shown below:
<?xml version=”1.0"?>
<Customer>
<Name>Joe Smith</Name>
<AcctNo>32374-94</AcctNo>
<LoanAmt>$325,983</LoanAmt>
<IntRate>6.25%</IntRate>
<Term>30</Term>
</Customer>




Figure 12-7: This worksheet uses imported XML data.

This file has five data elements: Name, AcctNo, LoanAmt, IntRate, and Term. Two of the
fields (Prepared and Number of Pmt Periods) are calculated with formulas and are not
considered data elements.
The trick here is to be able to import files, such as this, and have the data sent to the
appropriate cells in the worksheet.
Chapter 12 ¦ 291
Sharing Excel Data with Other Applications


The first step is to add a Map to the workbook. Make sure that XML Source is displayed in
the task pane (select Data _ XML _ XML Source).
To add the Map, follow these steps:
1. Click the Workbook Maps button at the bottom of the task pane. The XML Maps
dialog box appears.
2. Click Add to display the XML Source dialog box.
3. Select one of the customer XML files. The exact file doesn™t matter. This will be
used only to infer the schema.
4. Click OK to dismiss the XML Maps dialog box.
The task pane displays the data elements from the file (as shown in Figure 12-8).




Figure 12-8: The task pane shows the XML data elements.

The next step is to map the data elements to the appropriate worksheet cells.
1. In the task pane, click the Name element and drag it to cell B3.
2. Drag the AcctNo element to cell B5.
3. Drag the LoanAmt element to cell B6.
4. Drag the IntRate element to cell B7.
5. Drag the Term element to cell B8.
Finally, you can import an XML file. Choose Data _ XML _ Import, and select a customer
XML file. You™ll find that the data is fed into the appropriate cells. To calculate another
amortization schedule, just import another XML file.
Part II ¦ Collaborating and Integrating with Office 2003
292



Importing XML data to a list
The example in the preceding section used XML files that contained only a single “record.”
XML files often contain multiple records, called repeating elements. Examples include a
customer list or data for all employees in an organization.
You can use Excel™s File _ Open command to open an XML file that contains repeating
elements. After you specify the filename, Excel presents the Open XML dialog box, as shown
in Figure 12-9. This dialog box has three options:
¦ As an XML List: The file opens, and Excel converts the data to a List Range.
¦ As a Read-Only Workbook: The data is imported into the worksheet, but the
workbook is read-only. This is to prevent you from accidentally over-writing the
original file.
¦ Use the XML Source Task Pane: Excel infers the schema for the XML data and
displays it in the task pane. (The data is not actually imported.) You can then map
the elements to cells and import the actual data.




Figure 12-9: The Open XML dialog box.

Figure 12-10 shows an XML file that has been imported to a worksheet.
Chapter 12 ¦ 293
Sharing Excel Data with Other Applications




Figure 12-10: The imported XML data.

<< . .

. 26
( : 51)



. . >>