Word 2010 vba to change an Excel Link

Topic

New Reply

I have a vba code (thanks in part to this site!) that updates Excel links to the current folder. However, this command fails when the link references an excel table (range). I have included the command, and my link (.code.text)

.Code.Text = Replace(.Code.Text, OldPath, NewPath)

LINK Excel.Sheet.12 “\\svr-mydata\data\users\bsmith\Desktop\Writer 2011.01\Companion Workbook.xlsx” Taxes!R3C3:R24C7 p

What am I doing wrong?

Reply | Quote

Replies

Hi yackbo,

I’d suggest using the latest version of the code (see http://lounge.windowssecrets.com/index.php?showtopic=670027). Office 2007 broke the old one.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I tried the new one again (link provided), but I get an error when trying to extract the .zip file. My code works on any Excel link unless it has the p switch. Any idea why that would keep the path from being changed by the code.text = replace(.code.text,oldpath,newpath)?

Reply | Quote

There seems to be a problem with downloading lounge attachments. Here’s the latest code:

Code:

Option Explicit
' Word macro to automatically update field links to other files
' Created by macropod. Posted at:
' [url]http://lounge.windowssecrets.com/index.php?showtopic=670027[/url]
Dim TrkStatus As Boolean      ' Track Changes flag
Dim Pwd As String ' String variable to hold passwords for protected documents
Dim pState As Boolean ' Document protection state flag
Sub AutoOpen()
' This routine runs whenever the document is opened.
' It calls on the others to do the real work.
' Prepare the environment.
With ActiveDocument
  ' Insert your document's password between the double quotes on the next line
  Pwd = ""
  ' Initialise the protection state
  pState = False
  ' If the document is protected, unprotect it
  If .ProtectionType  wdNoProtection Then
    ' Update the protection state
    pState = True
    ' Unprotect the document
    .Unprotect Pwd
  End If
  Call MacroEntry
  ' Most of the work is done by this routine.
  Call UpdateFields
  ' Go to the start of the document
  Selection.HomeKey Unit:=wdStory
  ' Clean up and exit.
  Call MacroExit
  ' If the document was protected, reprotect it, preserving any formfield contents
  If pState = True Then .Protect wdAllowOnlyFormFields, Noreset:=True, Password:=Pwd
  ' 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.
  .Saved = True
End With
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 oRng As Range, oFld As Field, i As Integer
Dim OldPath As String, NewPath As String, Parent As String, Child As String
' Set the new path.
' If your files are always in a folder whose path bracnhes off, one or more levels above the current
' folder, replace the second '0' on the next line with the number of levels above the current folder.
For i = 0 To UBound(Split(ActiveDocument.Path, "")) - 0
  Parent = Parent & Split(ActiveDocument.Path, "")(i) & ""
Next i
' If your files are in a Child folder below the (new) parent folder, add the Child folder's
' path from the parent (minus the leading & trailing "" path separators) on the next line.
Child = ""
NewPath = Parent & Child
' Strip off any trailing path separators.
While Right(NewPath, 1) = ""
  NewPath = Left(NewPath, Len(NewPath) - 1)
Wend
' Go through all story ranges in the document, including shapes, headers & footers.
With ThisDocument
  For Each oRng In .StoryRanges
    ' Go through the fields in the story range.
    For Each oFld In oRng.Fields
        With oFld
          ' Skip over fields that don't have links to external files.
          ' A '.Linkformat' Property test should work, but is unreliable.
          If .Type = wdFieldHyperlink Or .Type = wdFieldImport _
            Or .Type = wdFieldInclude Or .Type = wdFieldIncludePicture _
            Or .Type = wdFieldIncludeText Or .Type = wdFieldLink Or .Type = wdFieldRefDoc Then
            ' Get the old path. OldPath = .LinkFormat.SourcePath should work,
            ' but is unreliable as it sometimes returns part of the filename.
            OldPath = GetPath(.LinkFormat.SourceFullName)
            ' Replace the link to the external file if they differ.
            If OldPath  NewPath Then .LinkFormat.SourceFullName = _
              Replace(.LinkFormat.SourceFullName, OldPath, NewPath)
          End If
        End With
    Next oFld
  Next oRng
End With
End Sub
Function GetPath(StrPath As String)
' Strip off anything past the final path separator.
While Right(StrPath, 1)  ""
  StrPath = Left(StrPath, Len(StrPath) - 1)
Wend
' Strip off the final path separator.
StrPath = Left(StrPath, Len(StrPath) - 1)
GetPath = StrPath
End Function

As for the problems with the old code, I don’t profess to understand why Word 2007 broke it …

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks for the help.

Still having problems though. I reduced my document down to a single Excel link and copied the suggested code into the vba project verbatim. I get a run-time error ‘6083’. Debugging points to Line 87 which starts out “if OldPath NewPath Then .LinkFormat.SourceFullName…

Basically, it gives me the error message which says “objects contain links to files which cannot be found”

However, we are not asking it to “find” any files- that comes later. All I want it to do is update the path to the current folder.

Suggestions?

Reply | Quote

Basically, it gives me the error message which says “objects contain links to files which cannot be found”

Suggestions?

I am also having the same issue with the latest macro and Word 2007
Any help would be much appreciated

Reply | Quote

The problem is most likely to be due to a change in the filename or to the file not being in the folder concerned. The macro only updates the path to the current folder (or to a parent/child folder defined in the code), not the filename.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

The problem is most likely to be due to a change in the filename or to the file not being in the folder concerned. The macro only updates the path to the current folder (or to a parent/child folder defined in the code), not the filename.

In MS Word 2010 I am getting an error stating that I cannot have 2 links to the same filename in the document even if the file is located in 2 different places.
I get the error when assigning the new value to the .LinkFormat.SourceFullName. My code is slightly different than that above but in this case both the old and new files exist in the paths.

If OldSourceFullName NewSourceFullName Then .LinkFormat.SourceFullName = NewSourceFullName

Is there something I need to do to the link while I am changing this value?

Anyone have any ideas?

PFN

Reply | Quote

I get the error when assigning the new value to the .LinkFormat.SourceFullName. My code is slightly different than that above but in this case both the old and new files exist in the paths.

If OldSourceFullName NewSourceFullName Then .LinkFormat.SourceFullName = NewSourceFullName

For whatever reason. Word has problems changing the SourceFullName. I suggest you base whatever you’re doing on the current version of the code: http://windowssecrets.com/forums/showthread//154379-Word-Fields-and-Relative-Paths-to-External-Files

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Dear macropod,

I found this thread. I tried to find the latest macro on the site you suggest above but I can not open this page. It always puts me to the new landing page, and when I click on the proceed to the real landing page, it does not open the link I wanted.

Can you please advise where can I find the latest version of the macro that I could use in Word 2013?

Thank you!

Kind regards,

Balazs

Reply | Quote

Hi Balazs, the link to that thread was an old Windows Secrets Lounge one and that forum no longer exists. The posts were all copied to AskWoody, but most links weren’t changed (a rather long and tedious exercise). However, we think we’ve found the thread in question and it’s here. Which particular post in the thread is something you’ll have to determine I’m afraid.

Eliminate spare time: start programming PowerShell

Reply | Quote

The same content as the old WS post is available at: https://www.msofficeforums.com/word/38722-word-fields-relative-paths-external-files.html

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

1 user thanked author for this post.

access-mdb

Reply | Quote