Update Source of Links

[depawl]

I haave a Word document with 20+ links to an excel spreadsheet. Every month staff need to create a new document with new data. The new document is saved with a new file name. I need a way to update all of the links to the new source. The only way that I can see to do it in Word is to update one link at a time, which is much too time consuming. I suspect that perhaps this could be done with a macro, which is way beyond my level of expertise.

Thanks.

[TonyJollans]

What type of Links?

[depawl]

I guess I'm not sure exactly what you mean. It is mostly data, like customer names, numbers of times they called in, etc. There is also a link to a chart with additional customer data.

[TonyJollans]

No, I meant what type of link - are they hyperlinks or linked files - how were they originally created?

[fumei]

Yes. depawl, we need to know more explicitly what you are dealing with.

It could be done very simply, as in:Dim oFld As Field
For Each oFld In ActiveDocument.Fields
oFld.Update
Next

Or it could be rather complicated. It is impossible to say until we know exactly what you have. Tell us - precisely, exactly - how these links were made.

[depawl]

OK, Sorry. The links are all from an excel spreadsheet. As an example I have a cell in the spreadsheet, say it's a client's name. I copy that cell, then in the Word Document, I do a "Paste Special", then select "Paste Link", then select Microsoft Office Excel Worksheet Object. As I mentioned, there are a few dozen links from the Excel spreadsheet to the Word document, and I would like to be able to change the source file for all the links at once, without having to update each individual link.
Thanks again.

[TonyJollans]

Thankyou depawl. I think you should be able to do this with Find & Replace and will have a look at it tomorrow.

[depawl]

Just a bit more explanation. This Word document needs to be created anew at the end of the month. So if last month's excel spreadsheet was named nov07.xls, for the current month, a new spreadsheet would be created, named dec07.xls. And all of the links in the Word document would have to have their source updated to dec07.xls, rather than nov07.xls. This can be done one at a time by using Edit | Links, then "Change Source", but I haven't found a way to do them all at once.

[TonyJollans]

You are right that Edit > Links can't do them all at once.

You can do this quite easily through the UI but, for the example you quote, this should do it:

With ActiveDocument.Content
.TextRetrievalMode.IncludeFieldCodes = True
.Find.Execute FindText:="(LINK Excel.Sheet.8*)Nov 07", _
ReplaceWith:="\1Dec 07", _
MatchWildcards:=True, _
Replace:=wdReplaceAll
End With

[depawl]

Tony:
Could you explain what you mean by being able to do this through the UI? I have played around with this for quite awhile but have not been able to do so.
Also, in the example you gave, it appears that the old and new source names would have to be entered into the macro. The staff that will be using this application are not up to that level. What I need is maybe something like a popup box where they would enter the path and file name of the new source.
Is that possible?
Thanks again.

[TonyJollans]

What I meant was the Find & Replace could be done through the UI.

It is easy enough to pop up a Form. You can make the whole process as flexible as you wish but if it is the case that all the links are to the same workbook and they all want changing to the same new workbook then it would be sensible simply to ask for the new one.

This has actually proved slightly more awkward than I expected but this should work:

Dim Fld As Field
Dim FName As String, FNameNew As String
Dim Work
For Each Fld In ActiveDocument.Fields
If Fld.Type = wdFieldLink Then
Work = Split(LTrim(Split(LTrim(Fld.Code.Text), , 2)(1)), , 2)(1)
If Left(Work, 1) = """" Then
FName = Split(Work, """")(1)
Else
FName = Split(Work)(0)
End If
Exit For
End If
Next
With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = Replace(FName, "\\", "\")
.Filters.Add "Excel Workbooks", "*.xls; *.xlsx; *.xlsm", 1
Do While .Show = 0: Loop
FNameNew = Replace(.SelectedItems(1), "\", "\\")
End With
FNameNew = """" & FNameNew & """"
With ActiveDocument.Content
.TextRetrievalMode.IncludeFieldCodes = True
.Find.Execute FindText:="""" & FName & """", _
ReplaceWith:=FNameNew, _
Replace:=wdReplaceAll
.Find.Execute FindText:=FName, _
ReplaceWith:=FNameNew, _
Replace:=wdReplaceAll
End With
ActiveDocument.Fields.Update

This assumes all the Links are to the same workbook and picks up that name from the first link it finds. The difficulty was in extracting the name precisely from the field. That is the first part of the code.

The second part prompts the user to select a new workbook, by default from the same location as the original but they are free to choose any file.

The third part replaces all instances of the file name with the new one. The two Replaces are for the filename in quotes, and not in quotes as it seems files might be either, except that they must be in quotes if they contain spaces.

Finally the fields are updated to actually link to the new file.

[depawl]

Thanks Tony, I appreciate all the effort you put into this. I'll give it a try and let you know how things work out.