Hi all,
Here's hoping you can help a very new person to VB.
I've got an Excel file with links to other Excel files in hundreds of cells, but there are two parts of these links in each cell I want to change automatically with this VB below
I used the following (the file replaces the first one correctly..but
[CODE=vb]Sheets("Dry Year Final Balance").Selec t
Range("B1: AG110 ").Select
Selection.Repla ce What:="date", Replacement:=ba seyear, LookAt:=xlPart, _
SearchOrder:=xl ByRows, MatchCase:=Fals e
Sheets("Dry Year Final Balance").Selec t
Range("B1: AG110 ").Select
Selection.Repla ce What:="Dummy", Replacement:=Ne wRunNo, LookAt:=xlPart, _
SearchOrder:=xl ByRows, MatchCase:=Fals e[/CODE]
Once it gets to the end of the first replace the links in Excel try to update automatically. This halts what is happening as it tries to find a file which doesn't exist. Is there a way I can stop this happening so the 2nd replace can take place before Excel updates the link?
I tried using UpdateLink:=0 after the 1st replace, but this didn't work or I placed it incorrectly
Apologies if this is very basic, but I am tres new to VB.
Look forward to your answers.
M
Here's hoping you can help a very new person to VB.
I've got an Excel file with links to other Excel files in hundreds of cells, but there are two parts of these links in each cell I want to change automatically with this VB below
I used the following (the file replaces the first one correctly..but
[CODE=vb]Sheets("Dry Year Final Balance").Selec t
Range("B1: AG110 ").Select
Selection.Repla ce What:="date", Replacement:=ba seyear, LookAt:=xlPart, _
SearchOrder:=xl ByRows, MatchCase:=Fals e
Sheets("Dry Year Final Balance").Selec t
Range("B1: AG110 ").Select
Selection.Repla ce What:="Dummy", Replacement:=Ne wRunNo, LookAt:=xlPart, _
SearchOrder:=xl ByRows, MatchCase:=Fals e[/CODE]
Once it gets to the end of the first replace the links in Excel try to update automatically. This halts what is happening as it tries to find a file which doesn't exist. Is there a way I can stop this happening so the 2nd replace can take place before Excel updates the link?
I tried using UpdateLink:=0 after the 1st replace, but this didn't work or I placed it incorrectly
Apologies if this is very basic, but I am tres new to VB.
Look forward to your answers.
M