Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Nov 2010
    Location
    Gainesville, Georgia, USA
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Word 2010 vba to change an Excel Link

    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?

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

    I'd suggest using the latest version of the code (see http://lounge.windowssecrets.com/ind...owtopic=670027). Office 2007 broke the old one.
    Last edited by macropod; 2011-02-05 at 00:11.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Nov 2010
    Location
    Gainesville, Georgia, USA
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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)?

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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:
    ' http://lounge.windowssecrets.com/ind...owtopic=670027
    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]

  5. The Following User Says Thank You to macropod For This Useful Post:

    yackbo (2011-02-03)

  6. #5
    New Lounger
    Join Date
    Nov 2010
    Location
    Gainesville, Georgia, USA
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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?

  7. #6
    New Lounger
    Join Date
    Feb 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by yackbo View Post

    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

  8. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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]

  9. #8
    New Lounger
    Join Date
    Feb 2016
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS Word 2010 - changing path to linked file.

    Quote Originally Posted by macropod View Post
    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

  10. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by pedalfasternow View Post
    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/sho...External-Files
    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
  •