Linking or embedding an Excel 2007 workbook in Word 2007

[MikeJacobs]

Office 2000 did not run fast enough back then, so I wrote VBA to better
manage exchanges from Excel to Word. Now that things are faster, I'm trying
to do the same thing with Linking and/or Embedding.

I have two template files, Word and Excel. Excel has 25 worksheets with
hundreds of calcs. I need the results in Excel, which are both values in
cells and "pictures" of formatted tables to be presented in Word.

Sounds easy, right? Linking seems to be made for this. Not so - the links
are not stable, apparently, with files of this complexity.

For example, I create my "templates" which are linked in the usual ways
(text, unformatted text, pictures, etc.). When I change the source using the
"PREPARE -> EDIT LINKS" dialog, strange things happen. Some links remain
associated with the original source file while showing the new source file!
Some are just blown out! Some even work. I therefore have no confidence in
the files.

It's also a nightmare to manage that many links when I have external links I
do NOT want changed, and OLE links within the doc itself.

Embedding isn't working out either, since I need every Excel object in Word
to be the SAME object - a change in one filters through to all. Embedding a
worksheet simply seems to create a new copy of that worksheet. Each is
different.

So, am I back to writing code to do this? Am I missing something or perhaps
somebody knows of an Add-In for sale?

 

[macropod]

Hi Mike,

Perhaps my (free) Field Link Updater will point you in the right direction. It's available at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=261488
It's not the most efficient code (I've learnt more about vba since then), but it works. I also have a (more efficient) version that
automatically updates all links on opening, but only if the source & target files are in the same folder.

Cheers

 

[MikeJacobs]

Perhaps my (free) Field Link Updater will point you in the right direction. It's available at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=261488

Thanks for the quick reply. You did a lot of work! Let me ask you - is this
working in Office 2007/Windows Vista? My code won't, and I assumed that VBA
was no-more (I've left programming behind and no longer have the time to keep
up!).

 

[Doug Robbins - Word MVP]

Office 2007 still supports VBA. I don't see anything in the macro that
would prevent it from running in Word 2007 - at least, it can be compiled
without throwing any errors.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[MikeJacobs]

Office 2007 still supports VBA. I don't see anything in the macro that
would prevent it from running in Word 2007 - at least, it can be compiled
without throwing any errors.

Well, that is good news. Where did I get the idea that VBA was dead - I
don't know. There were a few errors when I upgraded to Office 2003, so I
stayed with Office 2000 until now. I'll have to dust my VBA skills off and
see what I can do about cleaning them up & figuring out the other issues.
Thanks to both Doug and macropod.