find and replace name of links

[Gerry]

I have several workbooks linked with names similar to this
H:\Budgets\Allcan\AllCan Budget 2007.xls
All I would like to change is 2007 to 2008

Is there a quick and easy way to do this?

TIA

Gerry

 

[Dave Peterson]

Edit|links|change source

might be the easiest way.

Depending on where those links are, maybe a bunch of edit|replaces would work,
too.

 

[Gerry]

I came up with a macro that worked. Is there a way that I can
accomplish this without copying the info to a worksheet?

Sub ChangeLinkNamesbyYear()
Dim v
For Each v In ActiveWorkbook.LinkSources
Worksheets("Sheet1").Select
Range("A1").Select
ActiveCell.Value = v
Range("A1").Copy
Range("a2").PasteSpecial
Range("A2").Select

ActiveCell.Replace What:="2007", Replacement:="2008",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
ActiveWorkbook.ChangeLink
Name:=Worksheets("sheet1").Range("a1").Value, _
NewName:=Worksheets("sheet1").Range("a2").Value,
Type:=xlExcelLinks
Next
End Sub

 

[Dave Peterson]

One way:

Option Explicit
Sub ChangeLinkNamesbyYear()
Dim myLinks As Variant
Dim iCtr As Long

Dim OldKey As String
Dim NewKey As String

OldKey = "2007"
NewKey = "2008"

myLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If IsEmpty(myLinks) Then
'do nothing
Else
For iCtr = 1 To UBound(myLinks)
If InStr(1, myLinks(iCtr), OldKey, vbTextCompare) > 0 Then
ActiveWorkbook.ChangeLink Name:=myLinks(iCtr), _
newname:=Replace(myLinks(iCtr), OldKey, NewKey), _
Type:=xlLinkTypeExcelLinks
End If
Next iCtr
End If

End Sub

Replace was added in xl2k. If you're using xl97, you could use
application.substitute, instead.

 

[Gerry]

One way:

Option Explicit
Sub ChangeLinkNamesbyYear()
Dim myLinks As Variant
Dim iCtr As Long

Dim OldKey As String
Dim NewKey As String

OldKey = "2007"
NewKey = "2008"

myLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If IsEmpty(myLinks) Then
'do nothing
Else
For iCtr = 1 To UBound(myLinks)
If InStr(1, myLinks(iCtr), OldKey, vbTextCompare) > 0 Then
ActiveWorkbook.ChangeLink Name:=myLinks(iCtr), _
newname:=Replace(myLinks(iCtr), OldKey, NewKey), _
Type:=xlLinkTypeExcelLinks
End If
Next iCtr
End If

End Sub

Replace was added in xl2k. If you're using xl97, you could use
application.substitute, instead.

Thanks Dave