Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Globally change MS-Excel spreadsheet links in MS-Word document

I have an MS-Word document with multiple links (about 100) to an Excel spreadsheet.  I would like to create a copy of the document and have it link to a new spreadsheet.  This "new" spreadsheet would be a copy of the original, but with different values in it.  It would take far too much time to manually edit each link in the MS-Word document to point to the new spreadsheet.   I would only need to change the spreadsheet name.   Is there a way to do a global substitution/replacement of spreadsheet links within an MS-Word document?
0
msklein
Asked:
msklein
  • 3
  • 2
  • 2
  • +1
1 Solution
 
LoNeRaVeR9Commented:
msklein:

This should work for you.  Just replace the C:\OldFile.xls with your original spreadsheet path and name and C:\NewFile.xls with the new path and spreadsheet name.  This is a Word Macro and should be run from Word.

Sub ChangeHyperlinks()
    Dim hLink As Hyperlink
    For Each hLink In ActiveDocument.Hyperlinks
        If hLink.Address = "C:\OldFile.xls" Then _
            hLink.Address = "C:\NewFile.xls"
    Next hLink
End Sub

Please let me know if you have any questions.

Kindest Regards,

Jaes
0
 
jeveristCommented:
Hi msklein,

Or, if you have regular Excel links you can try this macro from Excel:

Public Sub ChangeWordLinks()
Dim DocFile As String, OldLinkFile As String, NewLinkFile As String, i As Long
Dim oWordApp As Object, oWordDoc As Object, oWordField As Object, oWordHyperlink As Object

DocFile = "C:\Document1.doc"
OldLinkFile = "Old Workbook.xls"
NewLinkFile = "New Workbook.xls"

On Error Resume Next
Set oWordApp = GetObject(, "Word.Application")
If oWordApp Is Nothing Then
    Set oWordApp = CreateObject("Word.Application")
End If
On Error GoTo 0

oWordApp.Visible = True
Set oWordDoc = oWordApp.Documents.Open(DocFile)

'Change links
For i = 1 To oWordDoc.Fields.Count
    Set oWordField = oWordDoc.Fields(i)
    If Not oWordField.LinkFormat Is Nothing Then
        oWordField.LinkFormat.SourceFullName = Replace(oWordField.LinkFormat.SourceFullName, OldLinkFile, NewLinkFile)
    End If
Next i

'Change Hyperlinks
For i = 1 To oWordDoc.Hyperlinks.Count
    Set oWordHyperlink = oWordDoc.Hyperlinks(i)
    oWordHyperlink.Address = Replace(oWordHyperlink.Address, OldLinkFile, NewLinkFile)
Next i

oWordDoc.SaveAs Left(DocFile, Len(DocFile) - 4) & " - New.doc"
oWordDoc.Close SaveChanges:=False
oWordApp.Quit

Set oWordApp = Nothing

End Sub

Jim
0
 
mskleinAuthor Commented:
Jaes,
I created the Word Macro using VB Editor with Copy/Paste of your code.  I saved the Word document and ran the Macro, but no luck.  The document is still linking to the "old" spreadsheet.  Perhaps the path length is too long?  My path to the old spreadsheet is" L:\Documents and Settings\Administrator.ATHLON-113\My Documents\Arnisoft\BeerBrands\The BeerBrands system packages Schedule A Exp Jan 31 2007.xls". Do you have any other ideas?
Thanks.
Michael
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LoNeRaVeR9Commented:
Michael:

I'm not sure, but you might need %20 instead of spaces in your link.k

L:\Documents%20and%20Settings\Administrator.ATHLON-113\My%20Documents\Arnisoft\BeerBrands\The%20BeerBrands%20system%20packages%20Schedule%20A%20Exp%20Jan%2031%202007.xls

Put your link in notepad and do a find and replace on a space and replace with %20.  That's just a guess.

Jaes
0
 
mskleinAuthor Commented:
Jim,
When I try to run your macro, I receive a

Run-time error '6083'

When I click the Debug pushbutton, it stops on the following line:

oWordField.LinkFormat.SourceFullName = Replace(oWordField.LinkFormat.SourceFullName, OldLinkFile, NewLinkFile)

Any ideas?
Thanks.
Michael
0
 
mskleinAuthor Commented:
Jaes,
Using the %20 didn't help.  Still no change in the links.  Any other ideas?
Thanks.
Michael
0
 
jeveristCommented:
Michael,

You will get this error if you are trying to update a regular link (not a hyperlink) to a file that does not exist.  Make sure that the file that you are changing the link to is at the location you are specifying.

Run-time error '6083':

Objects in this document contain links to files that cannot be found.
The linked information will not be updated.

Jim
0
 
Eric FletcherCommented:
You could just use Find and Replace to change the name of the link within the field codes in the Word document. Make the field codes visible with Alt-F9, then copy the Excel filename within one of the field codes. Open the Find & Replace dialog (Ctrl-H) and paste it into the Find what box. Type the replacement file name in the Replace with box and click Replace All. Toggle the field codes back again (Alt-F9), select everything (Ctrl-A), and update the links (F9).

That should do it for you.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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