
Signature
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org
merci beaucoups... c'est tous de ma francaise...
> pdberger was telling us:
> pdberger nous racontait que :
[quoted text clipped - 28 lines]
> By default, when opening the Word document, and as long as the Excel
> Workbook is kept in the same folder, all links will automatically update.
> From what I have seen before, you do not need code for this.
Actually, you do. If you don't change the links, Word will still try to find the source data according to the original path. That'll
only work if the users copy the files to the exact same folder path/location on their own systems.
Anyway, here's the code you need:
Option Explicit
Dim TrkStatus As Boolean ' Track Changes flag
Private Sub AutoOpen()
' This routine runs whenever the document is opened.
' It calls on the others to do the real work.
' Prepare the environment.
Call MacroEntry
' Most of the work is done by this routine.
Call UpdateFields
' Set the saved status of the document to true, so that changes via
' this code are ignored. Since the same changes will be made the
' next time the document is opened, saving them doesn't matter.
ActiveDocument.Saved = True
' Go to the start of the document
Selection.HomeKey Unit:=wdStory
' Clean up and exit.
Call MacroExit
End Sub
Private Sub MacroEntry()
' Store current Track Changes status, then switch off temporarily.
With ActiveDocument
TrkStatus = .TrackRevisions
.TrackRevisions = False
End With
' Turn Off Screen Updating temporarily.
Application.ScreenUpdating = False
End Sub
Private Sub MacroExit()
' Restore original Track Changes status
ActiveDocument.TrackRevisions = TrkStatus
' Restore Screen Updating
Application.ScreenUpdating = True
End Sub
Private Sub UpdateFields()
' This routine sets the new path for external links, pointing them to the current folder.
Dim oRange As Word.Range
Dim oField As Word.Field
Dim OldPath As String
Dim NewPath As String
' Set the new path
NewPath = Replace$(ActiveDocument.Path, "\", "\\")
' Go through all story ranges in the document, including shapes,
' headers & footers.
For Each oRange In ActiveDocument.StoryRanges
' Go through the fields in the story range.
For Each oField In oRange.Fields
With oField
' Skip over fields that don't have links to external files
If Not .LinkFormat Is Nothing Then
' Get the old path
OldPath = Replace(.LinkFormat.SourcePath, "\", "\\")
' Replace the link to the external file
.Code.Text = Replace(.Code.Text, OldPath, NewPath)
End If
End With
Next oField
Next oRange
End Sub
Cheers

Signature
macropod
[MVP - Microsoft Word]
-------------------------
Jean-Guy Marcil - 28 Aug 2007 23:37 GMT
macropod was telling us:
macropod nous racontait que :
>> From what I have seen before, you do not need code for this.
>
> Actually, you do. If you don't change the links, Word will still try
> to find the source data according to the original path. That'll only
> work if the users copy the files to the exact same folder
> path/location on their own systems.
This is true only if the source (the Excel workbook in this case) is in a
folder that is totally unrelated to the Word document folder. If you move
them around, Word will not find the source.
If you place the source in a children folder of the Word document folder,
and always keep the same children path, Word will find it if you move them
around, although it can lose the connection at times.
But if the Excel and Word files are always kept in the same folder, Word
will always find the Excel workbook and rebuild the path (Create the files,
link them, do ALT-F9 in Word to see the paths. Close both files, move them
to a totally unrelated folder, open the Word document and do ALT-F9 and see
the paths have been changed to match the new location.
I have seen this, I think, from at least Word 2000, or it could be 2002, I
am not sure at this point.

Signature
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org
macropod - 29 Aug 2007 00:29 GMT
> But if the Excel and Word files are always kept in the same folder, Word
> will always find the Excel workbook and rebuild the path
Not in my experience. That's why I wrote the macro.
Cheers

Signature
macropod
[MVP - Microsoft Word]
-------------------------