Hyperlink replacement vba

[abusaif]

Hi,

I have an Excel sheet with hyperlink to a folder now each folder has it's subfolders also and they are also hyperlinked from same worksheet. I.E. "c:\desktop\myfolder\300\subfolder1" I want a vba code to change only "300" to "301", rest will remain same.

Any idea.

 

[isladogs]

Use the Replace function

 

[abusaif]

I think I didn't cleared my point. The sheet I have hyperlinked contains 30+ hyperlinks to a folder with name "300" and the same contains subfolders "invoices, lpos, grns, etc". 300 is an account ref. So now if I create a folder 301 with all the subfolder, I will duplicate the current sheet for the same accout, and want to change the hyperlink's only part which contains 300 to 301.

For the above only replace funtion will not work

 

[isladogs]

Sorry but your reply doesn't help clarify what you want to do
Its also not clear from your answer whether you want to do this in Access or Excel.

However Replace will do what you seem to be asking whether its a text field or a hyperlink

For example in Access for a URL field in table tblTest:

CurrentDb.Execute "UPDATE tblTest SET tbltest.URL = Replace([URL],300,301);
"

 

[Gasman]

Colin was correct.

However you need to apply the replace to the hyperlink address as well as the cell value.

range("A4").Hyperlinks(1).address = replace(range("A4").Hyperlinks(1).address,"300","301")

Tested and works.

HTH