Linking tables to word from excel - Picture (enhanced metafile)

[tmleynek]

I have a macro-enabled excel template that I use with a Microsoft word document for a report template. I am pasting the links as a Picture (enhanced metafile) as these give me the best results in quality for PDF and printing purposes. I use this template for several reports and re link the word document by finding and replacing the old file paths with the new. (Only thing that changes is the excel name.

After replacing all the links with the new file name and try to update the tables, the tables & links revert back to what they were originally. Is there a way to not allow this to happen? I've tried disabling automatic linking, tried saving the excel file before updating the tables, and several other methods with no luck.

Any help would be wonderful.

Thanks!
Travis

[6StringJazzer]

After replacing all the links with the new file name...

Can you be specific about what steps you take to do this?

[tmleynek]

I push Alt + F9 to display the linking path. The original template path looks something like this (Redacted for confidential purposes):

{LINK Excel SheetMacroEnabled.12 "C:\\Work Files\\Template Folder\\Template Excel.xlsm" ExecSum!R129C10:R189C15 \a\p }

So basically the only thing that technically needs to be changed for each different report would be what's inside the quotations.. So I do a "Find & Replace ALL" with this:

FIND: C:\\Work Files\\Template Folder\\Template Excel.xlsm
REPLACE ALL: C:\\Work Files\\Company A Folder\\Company A Excel.xlsm

Since I have about 20-30 tables per report, I use the replace all function to save time. All of the links in the report now look like this.

{LINK Excel SheetMacroEnabled.12 "C:\\Work Files\\Company A Folder\\Company A Excel.xlsm" ExecSum!R129C10:R189C15 \a\p }

All of the links look correct, so I push alt + f9 to close the links and display the tables. When I go through each table and click on it and push F9 (to update the table) it reverts back to the original file path:

{LINK Excel SheetMacroEnabled.12 "C:\\Work Files\\Template Folder\\Template Excel.xlsm" ExecSum!R129C10:R189C15 \a\p }

Let me know if you have any further questions.

Thanks,
Travis

[6StringJazzer]

I don't think you can change the links by editing the fields directly. Try this.

[tmleynek]

Using the link manager does work, but I have several (100's) of tables. Is there a way that I could change the source for all? It would be timely to change the source for each individual link, which is why the "replace all" function is handy.

[6StringJazzer]

I know of no way to actually modify the links in mass (although it may be possible with a macro). Here is a workaround that I was successful with on a small scale.

1. Open your Word File.
2. Open the Excel file where the Word file currently links (C:\\Work Files\\Template Folder\\Template Excel.xlsm)
3. Rename the desired link file to something like this, exact name is not important C:\\Work Files\\Company A Folder\\Company A Excel SAVE.xlsm
4. In Excel do a Save As to C:\\Work Files\\Company A Folder\\Company A Excel.xlsm. This should cause all the links to be updated. At this point you should check a link to see if that happened. If it did not work then this method failed. You'll need to continue the following steps anyway.
5. Close both Excel and Word
6. Delete C:\\Work Files\\Company A Folder\\Company A Excel.xlsm
7. Rename C:\\Work Files\\Company A Folder\\Company A Excel SAVE.xlsm back to C:\\Work Files\\Company A Folder\\Company A Excel.xlsm