change links by a macro

J

JohnQ

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
 
T

Tom Ogilvy

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.
 
I

Iain King

JohnQ said:
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
Click to expand...

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
 
Top