Closed Thread
Results 1 to 6 of 6

Change Source for multiple Linked Fields and Charts

  1. #1
    Registered User
    Join Date
    10-24-2014
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    8

    Change Source for multiple Linked Fields and Charts

    HI all,

    i've been working on this for a while now and cant seem to figure out why this is happening.

    I'm currently using the below code to update the source file for multiple links with a MS WORD document.

    all links are to a single excel workbook, however the pathnames change an i need a quick and easy way to change the link source.

    The problem i am encountering is that although the below code works - somewhat, it doesn't change all the links. The fieldscount only counts the number of fields until it hits a Chart object, and then stops, not counting any other fields in the document, and leaves the source unchanged. The fields is does identify are changed without fault.

    Same is for ChartsCount.

    VBA.PNG



    Sub changeSource()
    Dim dlgSelectFile As FileDialog 'FileDialog object
    Dim thisField As Field
    Dim selectedFile As Variant 'must be Variant to contain filepath of selected item
    Dim newFile As Variant
    Dim fieldCount As Integer
    Dim ChartCount As Integer



    'create FileDialog object as File Picker dialog box
    Set dlgSelectFile = Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)



    With dlgSelectFile
    'use Show method to display File Picker dialog box and return user's action
    If .Show = -1 Then

    'step through each string in the FileDialogSelectedItems collection
    For Each selectedFile In .SelectedItems
    newFile = selectedFile 'gets new filepath
    Next selectedFile
    Else 'user clicked cancel
    End If
    End With
    Set dlgSelectFile = Nothing



    'update fields
    fieldCount = ActiveDocument.Fields.Count
    MsgBox "Field Count is" & fieldCount
    For x = 1 To fieldCount
    ActiveDocument.Fields(x).LinkFormat.SourceFullName = newFile
    Next x

    'update charts
    ChartCount = ActiveDocument.InlineShapes.Count
    MsgBox "Chart Count is" & ChartCount
    For x = 1 To ChartCount
    ActiveDocument.InlineShapes(x).LinkFormat.SourceFullName = newFile
    Next x


    End Sub

    Thanks heaps for your help

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

    Re: Change Source for multiple Linked Fields and Charts

    See the macro attached to: http://windowssecrets.com/forums/sho...External-Files
    Although written on the assumption that the document will ordinarily be in the same folder as the link source(s), a minor code tweak could make it useful for changing links to a predefined path.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Registered User
    Join Date
    10-24-2014
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    8

    Re: Change Source for multiple Linked Fields and Charts

    thanks Macropod,

    i followed that up and imported the code.

    Seems i can't quite follow the code and come ups with Run Time error

    Debug.PNG
    Code.PNG

    and i'm lost as where to start to fix that however i suspect it has to do with inserting the correct parameters around the path name.

    code on the site is as follows - however ideally id simply like to open up the dialog box to point to the correct file to link to.

    Option Explicit
    ' Word macro to automatically update field links to other files
    ' Created by Paul Edstein (aka macropod). Posted at:
    ' http://windowssecrets.com/forums/sho...External-Files
    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 Rng As Range, Fld As Field, Shp As Shape, iShp As InlineShape, i As Long
    Dim OldPath As String, NewPath As String, Parent As String, Child As String, StrTmp 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
    NewPath = NewPath & "\"
    ' Go through all story ranges in the document.
    With ThisDocument
    For Each Rng In .StoryRanges
    ' Go through the shapes in the story range.
    For Each Shp In Rng.ShapeRange
    With Shp
    ' Skip over shapes that don't have links to external files.
    If Not .LinkFormat Is Nothing Then
    With .LinkFormat
    OldPath = Left(.SourceFullName, InStrRev(.SourceFullName, "\"))
    ' Replace the link to the external file if it differs.
    If OldPath <> NewPath Then
    .SourceFullName = Replace(.SourceFullName, OldPath, NewPath)
    On Error Resume Next
    .AutoUpdate = False
    On Error GoTo 0
    End If
    End With
    End If
    End With
    Next Shp
    ' Go through the inlineshapes in the story range.
    For Each iShp In Rng.InlineShapes
    With iShp
    ' Skip over inlineshapes that don't have links to external files.
    If Not .LinkFormat Is Nothing Then
    With .LinkFormat
    OldPath = Left(.SourceFullName, InStrRev(.SourceFullName, "\"))
    ' Replace the link to the external file if it differs.
    If OldPath <> NewPath Then
    .SourceFullName = Replace(.SourceFullName, OldPath, NewPath)
    On Error Resume Next
    .AutoUpdate = False
    On Error GoTo 0
    End If
    End With
    End If
    End With
    Next iShp
    ' Go through the fields in the story range.
    For Each Fld In Rng.Fields
    With Fld
    ' Skip over fields that don't have links to external files.
    If Not .LinkFormat Is Nothing Then
    With .LinkFormat
    OldPath = Left(.SourceFullName, InStrRev(.SourceFullName, "\"))
    ' Replace the link to the external file if it differs.
    If OldPath <> NewPath Then
    .SourceFullName = Replace(.SourceFullName, OldPath, NewPath)
    On Error Resume Next
    .AutoUpdate = False
    On Error GoTo 0
    End If
    End With
    End If
    End With
    Next Fld
    Next Rng
    .Save
    End With
    End Sub

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

    Re: Change Source for multiple Linked Fields and Charts

    Please post whatever your images are supposed to show - all I see is an X next to the image names.

    As for "inserting the correct parameters around the path name", I can't tell what you've done in that regard. Even so, if you have saved the document in the same folder as the objects to which it is linked, you should be able to run the code unchanged; otherwise, you'd need to change the link paths. To do that, simply delete everything between:
    ' Set the new path
    and
    NewPath = NewPath & "\"
    and change the line:
    NewPath = NewPath & "\"
    to refer to the required path.

    PS: When posting code, please use the code tags, indicated by the # button on the posting menu.

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

    Re: Change Source for multiple Linked Fields and Charts

    Hello macropod, thanks for all your help on these forums!

    I've installed your macro (from step 3 of "Word Fields and Relative Paths to External Files" thread in MS office forums) into my word doc with links but it is not working, generally I get the same issues as David Barker. (I'm using windows 10 with MS 365 for business, all applicable files are in same folder)

    1. Open docx and save as docm, import your macro
    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, bring you to) ".SourceFullName = Replace(.SourceFullName, OldPath, NewPath)"

    As part of my troubleshooting I've got it to sort of work.
    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.

    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, this would be so helpful if it worked a bit better!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: Change Source for multiple Linked Fields and Charts

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Can I move the source worksheet of linked charts in Powerpoint
    By kfryar in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-23-2014, 01:32 AM
  2. VBA to change source data of charts by sheetname
    By antak in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-08-2014, 06:39 AM
  3. Change PIVOT source and NOT losing calculated fields
    By kaligad in forum Excel General
    Replies: 0
    Last Post: 11-28-2013, 08:50 AM
  4. Change item source for linked excel object by VBA
    By koticphreak in forum Word Programming / VBA / Macros
    Replies: 0
    Last Post: 08-13-2013, 12:44 PM
  5. VBA to change Linked Workbook Source
    By carsto in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-19-2010, 01:58 PM
  6. Replies: 3
    Last Post: 10-13-2005, 09: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