+ Reply to Thread
Results 1 to 6 of 6

Change path in Linked Fields (for word links to excel)

  1. #1
    Registered User
    Join Date
    01-25-2022
    Location
    Massachusetts
    MS-Off Ver
    365 Pro
    Posts
    7

    Change path in Linked Fields (for word links to excel)

    I'm new here and could really use a little help! I have a source xlsx with many docx linked to it. All files are in the same folder. I'd like to copy/paste this folder into a new location and have the source path to .xlsx automatically update to new location. Unfortunately, this simple idea does not have a simple fix (that I have found).

    I've installed the macro from note 3 of "Word Fields and Relative Paths to External Files" by macropod (sorry, new user, can't post link to forum yet) into my word doc but it is not working. (I'm using windows 10 with MS 365 for business, all applicable files are in same folder)

    Here are my steps:

    1. Open docx and save as docm, import the macro, save docm
    2. close all word and excel files.
    3. copy files to new location
    4. open moved docm, this prompts the following errors:

    "Sorry, Excel can't open two workbooks with the same name at the same time." (pops up twice, then)

    "Run-time error '6083':
    Objects in this document contain links to files that cannot be found.
    The linked information will not be updated."

    (click debug) goes to VBA code line: ".SourceFullName = Replace(.SourceFullName, OldPath, NewPath)"

    As part of my troubleshooting I've got it to barely work for small docs.
    If, after I move the files, I open the moved xlsx file first, then the moved docm, it prompts the same error:
    "Sorry, Excel can't open two workbooks with the same name at the same time." but this time it pops up MANY times (20+, seems dependent on # of links)
    If I can click through all the popups the macro is effective and the link paths are updated. Unfortunately, some of the bigger docs have hundreds of links and I just can't click through all the error boxes.

    It seems like it's so close, maybe I just have a word or excel option toggled differently than default? Any help is really appreciated, it would be so helpful if it worked better!
    Last edited by chriskarney; 01-26-2022 at 01:26 PM.

  2. #2
    Registered User
    Join Date
    01-25-2022
    Location
    Massachusetts
    MS-Off Ver
    365 Pro
    Posts
    7

    Re: Change path in Linked Fields (for word links to excel)

    I'm new to this site; do I need to buy points to get a response comment? I feel so close to a solution but it's just beyond my understanding.

  3. #3
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,667

    Re: Change path in Linked Fields (for word links to excel)

    It would have helped had you posted in the correct forum (https://www.excelforum.com/word-programming-vba-macros/).

    The post to which you refer is: https://www.excelforum.com/word-prog...ml#post4331973

    Before running the macro, switch off Word's 'update automatic links at open' option.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    Registered User
    Join Date
    01-25-2022
    Location
    Massachusetts
    MS-Off Ver
    365 Pro
    Posts
    7

    Re: Change path in Linked Fields (for word links to excel)

    Thank you macropod, I really appreciate your work.

    That said, I turned off 'update automatic links at open' (file->options->advanced->general) and it didn't fix the problem. After I move the files, when I try to open the word doc, word freezes before opening and pops up with "Sorry, Excel can't open two workbooks with the same name at the same time." and then "Run-time error 083': Objects in this document contain links to files that cannot be found. The linked information will not be updated." and the debugger heads to VBA code line: ".SourceFullName = Replace(.SourceFullName, OldPath, NewPath)". I then need to use task manager to force end word and excel (excel is opened as background process).

    But here's the strange thing ... If I open the newly moved excel sheet before I open then word doc I still get the "Sorry, Excel can't open two workbooks with the same name at the same time." but no run-time error. If push though all the error boxes and close and reopen the word doc, all the links have been updated correctly and it works.

    I've created an excel file with 1 entry and a word doc with 1 link (and the macro) and still no luck.

    Thanks a lot for any help.

  5. #5
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,667

    Re: Change path in Linked Fields (for word links to excel)

    You should also not have either copy of the Excel workbook open...

  6. #6
    Registered User
    Join Date
    01-25-2022
    Location
    Massachusetts
    MS-Off Ver
    365 Pro
    Posts
    7

    Re: Change path in Linked Fields (for word links to excel)

    I do not have any excel or word files open. When I attempt to open the freshly moved word doc, word clearly opens the old and new excel files in the background and struggles due to the file name. Long story short the previously linked macro does not work due to this error. I was able to find this macro which works well, if you are having an issue like this I'd reccomend this code.

    Sub UpdateWordLinks()

    Dim newFilePath As Variant
    Dim excelDocs As Variant
    Dim range As Word.range
    Dim shape As shape
    Dim section As Word.section

    excelDocs = GetFileNamesbyExt(ThisDocument.Path, ".xlsx")

    'The new file path as a string (the text to replace with)'
    newFilePath = ThisDocument.Path & Application.PathSeparator & excelDocs(1)

    Call updateFields(ThisDocument.fields, newFilePath)

    For Each section In ThisDocument.Sections

    Call updateHeaderFooterLinks(section.Headers, newFilePath)

    Call updateHeaderFooterLinks(section.Footers, newFilePath)

    Next

    'Update the links
    ThisDocument.fields.Update

    Set newFilePath = Nothing
    Set excelDocs(1) = Nothing
    Set excelDocs = Nothing
    Set range = Nothing
    Set shape = Nothing
    Set section = Nothing

    End Sub

    Function GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant

    Dim Result As Variant
    Dim i As Integer
    Dim MyFile As Object
    Dim MyFSO As Object
    Dim MyFolder As Object
    Dim MyFiles As Object
    Set MyFSO = CreateObject("Scripting.FileSystemObject")
    Set MyFolder = MyFSO.GetFolder(FolderPath)
    Set MyFiles = MyFolder.Files
    ReDim Result(1 To MyFiles.Count)
    i = 1
    For Each MyFile In MyFiles
    If InStr(1, MyFile.Name, FileExt) <> 0 Then
    Result(i) = MyFile.Name
    i = i + 1
    End If
    Next MyFile
    ReDim Preserve Result(1 To i - 1)

    GetFileNamesbyExt = Result

    Set MyFile = Nothing
    Set MyFSO = Nothing
    Set MyFolder = Nothing
    Set MyFiles = Nothing

    End Function

    Function updateHeaderFooterLinks(headersFooters As headersFooters, newFilePath As Variant)

    Dim headerFooter As Word.headerFooter

    For Each headerFooter In headersFooters

    Call updateFields(headerFooter.range.fields, newFilePath)

    Next

    Set headerFooter = Nothing

    End Function

    Function updateFields(fields As fields, newFilePath As Variant)

    Dim field As field
    Dim oldFilePath As Variant

    For Each field In fields

    If field.Type = wdFieldLink Then

    oldFilePath = field.LinkFormat.SourceFullName

    field.Code.Text = Replace(field.Code.Text, _
    Replace(oldFilePath, "\", "\\"), _
    Replace(newFilePath, "\", "\\"))

    End If

    Next

    Set field = Nothing
    Set oldFilePath = Nothing

    End Function

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 12-17-2021, 08:19 AM
  2. Path change in a linked excel files.
    By hanymos in forum Excel General
    Replies: 1
    Last Post: 03-08-2015, 02:11 AM
  3. How to copy linked Excel and Word files and retain links
    By ashleynpeters1 in forum Excel General
    Replies: 7
    Last Post: 05-30-2013, 08:27 PM
  4. Replies: 1
    Last Post: 05-14-2009, 11:01 AM
  5. [SOLVED] I need to change the path of the linked files - thousands of them.
    By Derekc in forum Excel General
    Replies: 2
    Last Post: 11-21-2005, 09:15 AM
  6. Change path links in Excel from VBA
    By Ricardo García in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-30-2005, 11:05 PM
  7. Replies: 1
    Last Post: 04-23-2005, 08:09 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1