News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Word 2010 vba to change an Excel Link

    Posted on WSyackbo Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function MS Word and word processing help Word 2010 vba to change an Excel Link

    This topic contains 11 replies, has 6 voices, and was last updated by  macropod 4 days, 4 hours ago.

    • Author
      Posts
    • #474608 Reply

      WSyackbo
      AskWoody Lounger

      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?

    • #1265956 Reply

      macropod
      AskWoody_MVP

      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
      [MS MVP - Word]

    • #1265990 Reply

      WSyackbo
      AskWoody Lounger

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

      • #1265993 Reply

        macropod
        AskWoody_MVP

        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
        [MS MVP - Word]

    • #1266007 Reply

      WSyackbo
      AskWoody Lounger

      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?

      • #1491240 Reply

        WSalekseyn
        AskWoody Lounger

        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

    • #1491255 Reply

      macropod
      AskWoody_MVP

      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
      [MS MVP - Word]

      • #1554194 Reply

        WSpedalfasternow
        AskWoody Lounger

        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

        • #1554218 Reply

          macropod
          AskWoody_MVP

          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.php/154379-Word-Fields-and-Relative-Paths-to-External-Files

          Cheers,
          Paul Edstein
          [MS MVP - Word]

    • #1908035 Reply

      anonymous

      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

      • #1908684 Reply

        access-mdb
        AskWoody MVP

        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.

      • #1911586 Reply

        macropod
        AskWoody_MVP

        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
        [MS MVP - Word]

        1 user thanked author for this post.

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Word 2010 vba to change an Excel Link

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.