Thread: Word Template linked with Excel.

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

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

Is this a docx file or a dotm file ?

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.

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:

Private Sub Document_open()
 ThisDocument.Variables("path")=ThisDocument.Path
 ThisDocument.Fields.Update
End Sub

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....

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

 
Sub snb()
 c00 = "E:\"
 c01 = Dir(c00 & "*.doc*")
    
 Do Until c01 = ""
  With GetObject(c00 & c01)
    .VBProject.VBComponents(1).CodeModule.AddFromString Replace("Private Sub Document_Open()~ThisWorkbook.Variables(""path"")=ThisDocument.Path~ThisDocument.fields.update~End Sub", "~", vbCrLf)
    For Each fld In Fields
     fld.Code.Text = Replace(Replace(fld.Code, ".xls", ".xls"""), "D:\\", """D:\\")
    Next
    
    On Error Resume Next
    .Selection.HomeKey wdStory
    Do
     .Selection.Find.Execute "D:", , , , , , , , , ""
     If Err.Number = 0 Then .Fields.Add Selection.Range, 64, "path", False
    Loop Until Err.Number <> 0
    Err.Clear

    .Save
    .Close 0
   End With
   c01 = Dir
 Loop
End Sub