Excel & Word VBA possibilities

Posted on 2004-11-15
Last Modified: 2008-03-04
I have a word document that contains 2 embedded Excel Worksheets.  The first Worksheet contains a list of numbers and the 2nd Worksheet contains the sum of these numbers.  Once I've updated the figures in the 1st Worksheet I would like the 2nd Worksheets to be updated too, is this possible? If so how would I achieve this?
Question by:dattanij
    1 Comment
    LVL 2

    Accepted Solution

    Hi, dattanij.

    The easiest way of doing this is to embed both Excel objects in your word document and copy/paste-link between them. You need to make sure that your sheet #2, the one with the sums, is in the same workbook as the sheet #1, the source sheet - both sheets are included in embedded object #1. In embedded object #1, make sheet 2 visible - ie. the sums. Copy what you need from this sheet, and jump into embedded object #2. Paste the copied cells as a link into object #2, replacing whatever content was there. Then jump back into object #1 and make sheet 1 visible.

    Programmatically this would be difficult. In my test document, a simple sum cell formula turned out to be


    Word appears to be tagging each embedded object with a unique numerical code, and accessing this would be pretty near impossible.

    An alternative option to the above methodology would be to keep the two excel documents external to the word document and paste their contents into the Word document as links. The advantage is that you can edit either worksheet independently, without opening the Word document, and you can link the values in one sheet to the values in the other, and the Word document will always be up to date with the latest changes in the Excel files. The disadvantage is that there is extra processing overhead with linked files - the more links you have, the more involved the open/update method becomes. Also, by default Word will ask you if you want to update the embedded links (you can set this to a default value to avoid being asked) and when you move the word document you will need to ensure that you also move the excel sheets along with it.


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
    Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
    This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
    This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now