Word Template linked with Excel.

**jmadsen** · 09-24-2011, 10:17 AM

Dear all,

I have an Excel-workbook were I do alot of calculating, creating tables and graphs.
These elements are linked into word-template. The document contains more than
70 pages.

I use Office 2007.

After I linked in all the data from Excel, the paths (to the Excel-workbook) in the word-template has become physical links.
I have the workbook and the template in the same folder. But if I move these two
til another location, the template try to get data from the workbook located were
the physical address point to.

Is there an easy way to change the physical path to a virtual path?
The template has many paths to objects in the workbook.

The workbook and template will always be in the same folder.

Regards,
Jørn

**snb** · 09-25-2011, 05:14 PM

What kind of links do yo use ? (the field insertfile e.g. ?)

Is this a docx file or a dotm file ?

**jmadsen** · 09-25-2011, 06:32 PM

Originally Posted by snb

What kind of links do yo use ? (the field insertfile e.g. ?)

Is this a docx file or a dotm file ?

It is a dotm file.

The links were automaticly added to the dotm file while I was Paste Spesial the graphs and tables into the dotm file.

I have found all the links in the dotm file, but they are all physical links. I am looking
for a way to change them without having to re-type every link in the dotm file.

Thanks for replying.

**snb** · 09-26-2011, 03:53 AM

Have a look how the links look like: use Alf-F9 to switch between different viewmodes.
Tell us what you see or post a sample document here. Otherwise it's rather guessing what you are talking about.

**jmadsen** · 09-26-2011, 07:50 AM

Originally Posted by snb

Have a look how the links look like: use Alf-F9 to switch between different viewmodes.
Tell us what you see or post a sample document here. Otherwise it's rather guessing what you are talking about.

Hi again and thank you for trying to solve this for me.

This is a link to a table:

{LINK Excel.Sheet.8 D:\\rapport.xlsm Beregninger!R40C2:R71C7 \a \f 4 \r \* MERGEFORMAT}

This is a link to a graph:

{LINK Excel.Sheet.8 D:\\rapport.xlsm "Grafer![rapport.xlsm]Grafer Diagram 28" \a \p \* MERGEFORMAT}

This is a link to a footer:

{LINK Excel.Sheet.8 D:\\rapport.xlsm Normalregnskap!R1C2 \a \t}

The template contains hundreds of these links. The problem is the D:\\. I would like to replace D:\\ with nothing, so the template look for rapport.xlsm in the same folder as the template is opened.

Regards.

**snb** · 09-26-2011, 12:21 PM

I'd suggest a combination of VBA and Wordfields

in the document:

{LINK Excel.Sheet.8 "{DOCVARIABLE path}\\rapport.xlsm" Beregninger!R40C2:R71C7 \a \f 4 \r }

In the VBA Document_open event:

Please Login or Register  to view this content.

**jmadsen** · 09-26-2011, 01:01 PM

Originally Posted by snb

I'd suggest a combination of VBA and Wordfields

in the document:

{LINK Excel.Sheet.8 "{DOCVARIABLE path}\\rapport.xlsm Beregninger!R40C2:R71C7" \a \f 4 \r }

In the VBA Document_open event:

Please Login or Register  to view this content.

Ok. So I have to manually change all links from D:\\rapport.xlsm to {DOCVARIABLE path}.... in the template? That is a **** of a job... ;-)

Where do I put the VBA code? In the template as well?

**snb** · 09-26-2011, 03:39 PM

Please don't quote nor swear.....
Instead you could have thanked me for this incredibly smart solution, could have clicked the reputation icon to add to my reputation...
And could have tried to figure out how to change the links in the document and how to add the VBAcode into those documents.

It's not complicated to do....

**snb** · 09-26-2011, 04:49 PM

Probably this is what you need.
Adapt the path (c00=....) to your own situation.

Please Login or Register  to view this content.

**jmadsen** · 09-26-2011, 05:35 PM

Hi again,

First of all, I did not swear, and second I am thankfull for Your help on this matter. I will try to use the
Sub snd() on the document.

Thanks alot for all the help.

Best regards.