Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and replace name of links
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and replace name of links
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and replace name of links
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and replace name of links
On Jan 30, 7:41 pm, Dave Peterson wrote:
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 Thanks Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find/Replace within links in cells | Excel Discussion (Misc queries) | |||
Find & Replace Cell Links for Check Boxes | Excel Discussion (Misc queries) | |||
find, replace, update links | Excel Discussion (Misc queries) | |||
Find and Replace Formula Links | Excel Worksheet Functions | |||
find and replace links in Excel 2003 | Excel Worksheet Functions |