[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel & Word VBA possibilities

Posted on 2004-11-15
2
Medium Priority
?
316 Views
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?
0
Comment
Question by:dattanij
1 Comment
 
LVL 2

Accepted Solution

by:
Fenric earned 750 total points
ID: 12588806
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

=Excel.Sheet.8|Document1!'!_1162102723!Sheet2!R1C1:R1C2'

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.

Cheers
Fenric!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

873 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