Relative Path Reference for Excel file links (Word 2000)

Topic

New Reply

I have an Excel file with tables and charts and a Word file with linked Excel worksheet objects to display the various tables and charts. Is there a way to make the links in the Word file relative, instead of absolute? Right now, when I copy these two files to another subdirectory, I have to manually update about 40 links.

Reply | Quote

Replies

You can use macropod’s utility Update paths in INCLUDETEXT et al. (Word 2000/XP) to update all paths in one go.

Reply | Quote

Thank you. I haven’t used any VBA code with Word before. How do I get the file to work as an add-in?

Reply | Quote

Macropod’s document contains some instructions: if you copy or move it to your startup folder for Word, it’ll be loaded as an add-in automatically. Check the File Locations tab of Tools | Options… to find out what your startup path is.

Reply | Quote

I have tried to use the macro unsuccessfully and am looking for some suggestions as to what I’m doing wrong. Here’s my scenario:

I need to e-mail a Word file with embedded Excel files to another person. All the files are in the same folder. The links are fine on my PC.

I e-mailed the folder with all the files from my PC to another PC that has the macro on it. When I ran the macro I got the error “Word is unable to create a link to the object you specified. Please insert the object directly into your file without creating a link.”

Any ideas will be appreciated!

Reply | Quote

Do you really need to mail the document with linked Excel files? It would be easier if you broke all the links before mailing the document, so that the Excel files become truely embedded.

Reply | Quote

Maybe that’s my problem…I want embedded files. All the other person needs it to be able to DC and edit using Excel. I thought that’s what I did, actually. In the Word file I do see LINK fields, though. So what do I need to do???

Reply | Quote

Make a copy of the document and open the copy.
Press Alt+F9 to show field codes.
Select Edit | Replace…
Enter LINK Excel in the Find what box.
Enter EMBED Excel in the Replace with box.
Click Replace All.
Press Alt+F9 to hide field codes.
Save the document.
Mailing this document should be OK. Zip it if is has become large.

Reply | Quote

Thank you, that’s what I needed. That will work beautifully.

Reply | Quote

Hi egghead,

If you put the following code into your Word document, it will automatically update the links to point to its current folder whenever the document is opened. It even gives a progress report on the status bar, which is useful if the document has lots of links.

Option Explicit
Dim SBar As Boolean           ' Status Bar flag
Dim TrkStatus As Boolean      ' Track Changes flag

Private Sub AutoOpen()
' This routine runs whenever the document is opened. It mainly calls 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 Status Bar status, then switch on temporarily.
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
' 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()
' Clear the Status Bar
Application.StatusBar = False
' Restore original Status Bar status
Application.DisplayStatusBar = SBar
' 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.
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,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote