Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change links by a macro
I'm using the macro below to change the input file.
I have to do this for 20 spreadsheets which contains 5 similar links each. The problem is that excel is not been able to locate the new file and or path. Sheets.Select Cells.Select Selection.Replace What:= _ "H:\2004\[source.XLS]", _ Replacement:= _ "H:\2005\[source.XLS]", LookAt _ :=xlPart, SearchOrder:=xlByRows, MatchCase:=False |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
change links by a macro
Unless the What string doesn't match entries in the cells, it should work.
Does it work when you do it manually. If so, turn on the macro recorder while you do it manually and capture the code. -- Regards, Tom Ogilvy "JohnQ" wrote in message ... I'm using the macro below to change the input file. I have to do this for 20 spreadsheets which contains 5 similar links each. The problem is that excel is not been able to locate the new file and or path. Sheets.Select Cells.Select Selection.Replace What:= _ "H:\2004\[source.XLS]", _ Replacement:= _ "H:\2005\[source.XLS]", LookAt _ :=xlPart, SearchOrder:=xlByRows, MatchCase:=False |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
change links by a macro
"JohnQ" wrote in message ... I'm using the macro below to change the input file. I have to do this for 20 spreadsheets which contains 5 similar links each. The problem is that excel is not been able to locate the new file and or path. Sheets.Select Cells.Select Selection.Replace What:= _ "H:\2004\[source.XLS]", _ Replacement:= _ "H:\2005\[source.XLS]", LookAt _ :=xlPart, SearchOrder:=xlByRows, MatchCase:=False Not sure exactly what you want here, but a simple way to make changes in multiple files is as follows: this code looks through all open workbooks, writing "Hello" in the top left cell of sheet 1 For each w in Workbooks w.Sheets(1).Cells(1,1).Value = "Hello" Next If you nest your replace calls inside that For loop, it will replace on every open workbook. Iain King |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the links in Excel at one batch? | Excel Discussion (Misc queries) | |||
change multiple links at once | Excel Worksheet Functions | |||
Change named cells and keep the links | Excel Discussion (Misc queries) | |||
Change links between 2 spreadsheets | Excel Discussion (Misc queries) | |||
Writing a macro to change external links to manual updating in Excel 2000 | Excel Programming |