find and replace name of links
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 wrote:
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
On Jan 30, 3:14 pm, Dave Peterson wrote:
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 wrote:
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
--
Dave Peterson
|