Hyperlink replacement vba (1 Viewer)

abusaif · Nov 16, 2018

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 · Nov 16, 2018

Use the Replace function

abusaif · Nov 16, 2018

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 · Nov 16, 2018

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:

Code:

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

Gasman · Nov 16, 2018

Colin was correct.

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

Code:

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

Tested and works.

HTH

Hyperlink replacement vba (1 Viewer)

abusaif

Registered User.

isladogs

MVP / VIP

abusaif

Registered User.

isladogs

MVP / VIP

Gasman

Enthusiastic Amateur

Similar threads

Users who are viewing this thread