Excel & Word VBA possibilities

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?
dattanijAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FenricCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.