Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Relative Path Reference for Excel file links (Word 2000)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relative Path Reference for Excel file links (Word 2000)

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

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Newfoundland
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Relative Path Reference for Excel file links (Word 2000)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relative Path Reference for Excel file links (Word 2000)

    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.

  5. #5
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relative Path Reference for Excel file links (Word 2000)

    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!
    egghead

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relative Path Reference for Excel file links (Word 2000)

    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.

  7. #7
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relative Path Reference for Excel file links (Word 2000)

    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???
    egghead

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relative Path Reference for Excel file links (Word 2000)

    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.

  9. #9
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Relative Path Reference for Excel file links (Word 2000)

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

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Relative Path Reference for Excel file links (Word 2000)

    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.

    <pre>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
    </pre>

    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •