[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1573
  • Last Modified:

Is it possible to write a macro to change links in Word?

I have a word document (final test report) that grabs graph data from a large excel spreadsheet. The spreadsheet contains the tabulated results of around 4000 tests and the data is presented graphically. I have a Word report that grabs this graphical data and drops it into Word's own excel graphs. However, I cannot use template files as I have, on occasions, more than one device I am testing. Hence, each device has its own results folder and hence unique link paths. There are at least a hundred graphs in my word doc and I, so far, have manually edited the links (Which is DUMB!). How could I automate this task with VBA?
0
standinwave
Asked:
standinwave
  • 9
  • 6
  • 3
  • +1
1 Solution
 
pony10usCommented:
Here is some basic code to work with:

Selection.Find.ClearFormatting
 Selection.Find.Replacement.ClearFormatting
 With Selection.Find   
 .Text = "\<start\>*\<end\>"   
 .Replacement.Text = ""    
 .Forward = True    
 .Format = False   
 .MatchCase = False   
 .MatchWholeWord = False   
 .MatchWildcards = True   
 .MatchSoundsLike = False   
 .MatchAllWordForms = False 
End With 
Selection.Find.Execute Replace:=wdReplaceAll 

Open in new window

0
 
GrahamSkanCommented:
You could try something like this:
Sub ChangeLinks()
    Dim fld As Field
    Dim strOldPath As String
    Dim strNewPath As String
    
    strOldPath = "C:\MyFolder\Book3.xlsx"
    strNewPath = "C:\MyFolder\Book4.xlsx"
    
    For Each fld In ActiveDocument.Fields
        If fld.Type = wdFieldLink Then
            If fld.LinkFormat.SourceFullName = strOldPath Then
                fld.LinkFormat.SourceFullName = strNewPath
                fld.Update
            End If
        End If
    Next fld
End Sub

Open in new window

0
 
Eric FletcherCommented:
Or you could skip VBA and just use the Find and Replace dialog...

1. Make all field codes visible (Alt-F9)
2. Copy the old path from a field
3. In the F&R box, paste the old path into the Find what box, then put the new one into the Replace with box
4. Click Replace All
5. Toggle the field code visibility back and update the fields (Alt-F9, Ctrl-A, F9)

If you only need to change certain types of field code, you may need to be more specific, and include the field code name in the F&R boxes (i.e. ^d HYPERLINK "http would operate on just the hyperlink field codes starting with "http").
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
standinwaveAuthor Commented:
EricFletcher: Thanks for the comment, the graph itself didn't have a field code - its figure number did, but the graph was just the graph
GrahamSkan: I added this to my Word document, but it crashed. Will supply the exact message when I get home this evening.
pony10us: this looks like a search and replace routine, but I will try it!

Everyone: I tried creating a macro whilst manually editing a link - my usual lazy way to get the software to write the code - but it was empty as though it could not understand. Tried it more than once, so I am puzzled!
0
 
Eric FletcherCommented:
If the Word document is getting the data from Excel, there must be a link field. Have you examined the file with view field codes toggled on (Alt-F9)?

The link field code typically looks something like this:
{ LINK Excel.Sheet.12 "C:\\My Documents\\Telephone list.xlsx" "Telephone list!workTels" \a \f 5 \h }

In the above, I could use F&R to change C:\\My Documents\\ to D:\\Data\\Common\\ if I wanted to retain the link after moving the "Telephone list.xlsx" file to that new location on drive D.

In this example, my Word document displays the current values of the region named "workTels" in the named spreadsheet. The "\a \f 5 \h" switches automatically update the link, match the formatting of the destination document, and insert the object as HTML format text.
0
 
standinwaveAuthor Commented:
Hi Eric, I created a dummy excel and word doc on Office 2010. I pasted into Word as a special paste, "keep source formatting and link data". When I press ALT F9, nothing appears in Word at all. I have enabled "Edit Links to Files" and added it to the ribbon, but still useless! Is the TYPE of paste the problem, if so what would be better?
0
 
pony10usCommented:
You are correct, what I offered is a search/replace function.
0
 
Eric FletcherCommented:
I use Copy in Excel, then right-click in Word to select the "Link & Use Destination Styles" flyout option from Paste. When I press Alt-F9, the view toggles between the result and the LINK field code.
Preview of Word's Paste option to Link to an Excel sheet
I'm using Word 2010 but I think this ability has been available for some time.

(BTW, the preview shows the result with a grey background because I have Word | Options, Advanced "Field shading:" set to "Always" so I can tell when content comes from a field code.)
0
 
standinwaveAuthor Commented:
Still cannot display any codes when docs justhave the one graph!
0
 
GrahamSkanCommented:
Could you consider posting an example here (document and source) , in case we have missed something essential?
0
 
standinwaveAuthor Commented:
I will do Tuesday UK time.
0
 
Eric FletcherCommented:
Also, let us know what versions of Word & Excel you are using.
0
 
standinwaveAuthor Commented:
Here are the source files
Doc1.docx
Book1.xlsx
0
 
standinwaveAuthor Commented:
Both Office 2010 versions
0
 
Eric FletcherCommented:
Looks like Microsoft changed how the link function worked... You used to be able to toggle the field codes to see and edit the LINK field details, but it appears that both Word 2010 and the preview version of Word 2013 do not permit this. The MS web site appears to be completely mum about any explanation of this.

Instead, the "new" way to manage this is via the fairly obscure Links dialog. It is only available after a Word document has been saved. To find it, look in File | Info, and down near the bottom right under properties you'll see the "Edit Links to Files" link. When clicked, the Link dialog opens.

This dialog allows you to edit the source links and some other options. Unfortunately, it only does one link at a time, and the objects in Word are not at all easy to relate to the listed source file. If you do select a whole group of items, the "Open Source" option disappears, but the "Change Source" button stays available. You'd expect that this would let you change them all at once, but no: instead, it tediously presents you with an Open dialog for each of the selected links!

IMHO, this is a terrible step backwards, and I cannot imagine why they would make such a change. It certainly cripples one of the features I really liked about Office.

For your question though, when I copied the chart from Excel, Word's Paste flyout offers several options. Only the L (Use Destination Theme & Link Data) and F (Keep Source Formatting & Link Data) options insert a linked copy of the chart. To test the above, I saved the Excel sheet, then moved it to a different location and changed its name. Back in Word, the Links dialog did allow me to change the path to restore the links.

The Links dialog (from File | Info, and then click the link under Properties)
0
 
GrahamSkanCommented:
It has always been a bit of a confusing situation, but as well as having the possibility of using link fields, objects can be placed in Shapes and InlineShapes. In the example document, they are in InlineShapes. Here is some code for that:
Sub ChangeInlineShapeLinks()
    Dim ilsh As InlineShape
    Dim strOldPath As String
    Dim strNewPath As String
    
    strOldPath = "C:\MyFolder\Book3.xlsx"
    strNewPath = "C:\MyFolder\Book4.xlsx"
    
    For Each ilsh In ActiveDocument.InlineShapes
        If ilsh.Type = wdInlineShapeChart Then
            If ilsh.LinkFormat.SourceFullName = strOldPath Then
                ilsh.LinkFormat.SourceFullName = strNewPath
                ilsh.LinkFormat.Update
            End If
        End If
    Next ilsh
End Sub

Open in new window

0
 
standinwaveAuthor Commented:
Eric, The real problem is EXACTLY how you describe! When there are around a hundred graphs to 're-path' it is VERY tedious!
Graham, I shall try this, I have never heard of Inline Shapes!
0
 
standinwaveAuthor Commented:
Hi Graham - It worked! Thanks Howard
0
 
standinwaveAuthor Commented:
Thanks for making life more difficult MS! Thanks to EE for making it easier!
0
 
Eric FletcherCommented:
And thanks to Graham for the very useful code snippet; this will definitely be added to my toolset!

I'm glad you posed the question standinwave. A number of legacy documents in my archives make extensive use of links, and while none have needed to be reworked yet, at least now I won't be faced with a crisis if a client suddenly wants to republish.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now