Using Objects in Excel

Sharing data between applications is called Object Linking and Embedding, more commonly known as OLE. This might sound complicated, but it isn't. It means there's an object in a document in one Windows application that is linked to or embedded from a document in another Windows application. Many Windows applications let you link and embed information in any of these applications. For example, you can copy a Microsoft Excel chart and put it into a Microsoft Word document.

This feature lets you copy or move information between documents in different applications using drag-and-drop. Both applications must support OLE.

If the object is linked, it appears in your Office document but it is stored outside of your document. Your document holds the link, which is like an address, and when you want to view or print your document, Excel reads the address, fetches the object, and provides it. If the original object is changed, the next time you link to it, you see the changes.

When you take an existing Excel worksheet or chart in a Word document and copy and paste the information or import it into another Word document, you can embed the worksheet rather than linking it. If the object is embedded, it is moved into your document after it's created and becomes a part of your document, just like the text, numbers, and clip art you create while you work in Excel.

By embedding the worksheet as an object, you can double-click the worksheet in Word, and the Word menus and toolbars are temporarily replaced by the Excel menus and toolbars. It's like working in a "super application," in which one window can perform many different types of applications' tasks?word processor to spreadsheet program and back. The advantage is that you can quickly and easily make changes to objects from different applications in the same window. This process is referred to as OLE. Any changes you make to the worksheet are automatically reflected in the worksheet in Word.

Object Linking

Object linking enables you to share data between programs with OLE. You can link Office documents. For example, you can take an existing Excel worksheet and copy and paste its contents or import it into a Word document. Then you can create a link between the Excel worksheet and the Word document so that each time the worksheet is updated in Excel, the worksheet data in Word is automatically updated to reflect any changes.

When you link Office documents, one document is called the source document and the other is called the target document. The source document contains the information you want to link; the target document receives the linked information. For example, if you want to link an Excel worksheet to a Word document, the worksheet file is the source document and the Word file is the target document.

When you link documents, the source document appears in the target document, but it is not physically there. For instance, you can see an Excel worksheet in a Word document, but the worksheet still resides in Excel.

Follow the steps in the next To Do exercise to link an Excel worksheet to a Word document. You'll be using the Sales 1st Qtr workbook and a blank Word document.

To Do: Link Office Documents
  1. First open the Sales 1st Qtr workbook. Be sure to update the links when you open the workbook. Then click the Summary sheet.

  2. Now it's time to start Word. Click the Start button on the Windows taskbar, and choose Programs, Microsoft Word. You should see the Word window with a blank document on your screen.

  3. With both applications open, let's switch to Excel by clicking the Microsoft Excel button on the Windows taskbar.

  4. Select the data in cells A1:D9 in the Summary sheet. This is the source data for linking the worksheet object to the Word document.

  5. Click the Copy button on the Standard toolbar.

  6. Click the Microsoft Word button on the Windows taskbar to switch to Word.

  7. Choose Edit, Paste Special. The Paste Special dialog box appears. The selected range appears at the top of the dialog box in the Source area.

  8. In the As list, choose Microsoft Excel Worksheet Object. This tells Word which object you want to place in the Word document.

  9. Click the Paste Link option. This tells Excel that you want to create a link between the source data and the Word document. Figure 19.1 shows the Paste Special dialog box with all the selections you should have made.

    Figure 19.1. The Paste Special dialog box.

    graphics/19fig01.jpg

  10. Click OK. The Excel worksheet should appear in the Word document as an object (see Figure 19.2).

    Figure 19.2. A linked worksheet in a Word document.

    graphics/19fig02.jpg

  11. Save the Word document by clicking the Save button on the Standard toolbar. Name the file My Integration, and click the Save button.

    When you copied cells A1:D9, Excel also copied the entire worksheet. The selected cells are the only ones visible, but if you double-click the object, you can scroll and see the entire worksheet.

    If you want to change the information in the Excel worksheet object, just double-click the worksheet in the Word document. The link switches you to Excel, where you make your changes. Next, you can save and close the worksheet. Switch back to Word, and you will see that the linked worksheet reflects the changes you made in Excel.

Object Embedding

Just like linking documents, you have a source document and a target document when you embed an object in a document. Embedding a document as an object physically places a document within another document. For example, if you embed an Excel worksheet or chart in a Word document, the worksheet or chart physically resides in Word as well as Excel.

In the upcoming exercise, you'll see how embedding an Excel worksheet as an object in a Word document is similar to linking Office documents. You'll be using the Sales 1st Qtr workbook and the My Integration document again. Follow the steps in the next To Do exercise to begin embedding an object.

To Do: Embed an Object
  1. In the Word document, press Enter a few times to insert some space beneath the worksheet object, and then press Ctrl+End to move to the bottom of the document. Press Ctrl+Enter to insert a page break. The insertion point should be at the top of page 2 now.

  2. Now we'll switch to Excel by clicking the Microsoft Excel button on the Windows taskbar.

  3. Press the Esc key to clear the flashing marquee border.

  4. Select the data in cells A1:D9 in the Sales 1st Qtr workbook. This is the data that we'll embed in the Word document as an object.

  5. Click the Copy button on the Standard toolbar.

  6. Click the Microsoft Word button on the Windows taskbar to switch to Word.

  7. Choose Edit, Paste Special. The Paste Special dialog box appears. The selected range appears at the top of the dialog box. Leave the Paste option selected.

  8. In the As list, choose Microsoft Excel Worksheet Object. This tells Word which object you want to place in the Word document. Figure 19.3 shows the Paste Special dialog box with the selection you should have made.

    Figure 19.3. The Paste Special dialog box.

    graphics/19fig03.jpg

  9. Click OK. The Excel worksheet is now an embedded object in the Word document. Now let's change a couple of numbers in the Excel worksheet and see how Excel treats an embedded object.

  10. Double-click the worksheet object. Notice the Word window takes on the appearance of an Excel window, displaying Excel's menus, toolbars, and the formula bar (see Figure 19.4).

    Figure 19.4. An embedded worksheet in a Word document.

    graphics/19fig04.jpg

  11. Click cell C6, type 100, and press Enter. Next, click cell C8, type 200, and press Enter. This changes two numbers in the Excel worksheet. Notice that the value in the Total row has been updated to reflect the new numbers.

  12. Click outside the worksheet. The Word menu and toolbars appear. Notice the worksheet now contains the new numbers and the formulas have been recalculated automatically to reflect the changes you made. It's like magic!

    graphics/bookpencil_icon.gif

    Remember, when you embed a worksheet object, the entire worksheet is really inserted. If you display the wrong worksheet in Word, simply double-click the worksheet in the Word document and click the worksheet tab you want. Then click outside the worksheet area in Word.


  13. Close the Word document and save the changes. Then, close Word by double-clicking the Word Control icon in the upper left corner of the Word window. You should see only the Excel window on your screen.



    Part I: Excel Basics
     
    ASPTreeView.com
     
    Evaluation has №ЩОЦexpired.
    Info...