Changing hyperlink on Excel 2003

Topic

New Reply

Hi

Maybe you can help me here, I want to change the Path for all the hyperlink in a workbook. What is happening is I have and excel file on the external hard drive (F: drive) and i want the hyperlinks in that worksheet to run from the external hard drive. At the moment the links are directed to the C: drive. The thing is there are more than 1000 cells with hyperlinks, liked to different files. I need to change all of them.

Regards
S

Reply | Quote

Replies

You could use something like this:

Code:

Sub ChangeHyperlink()
	Dim wkb As Workbook
	Dim wks As Worksheet
	Dim hlink As Hyperlink
	Dim sOld As String
	Dim sNew As String
    
	Set wkb = ActiveWorkbook
	Set wks = ActiveSheet

	sOld = "C:"
	sNew = "F:"
	For Each wks in wkb.Worksheets
    	For Each hlink In wks.Hyperlinks
        	hlink.Address = Application.WorksheetFunction. _
            	Substitute(hlink.Address, sOld, sNew)
    	Next hlink
	Next wkb
End Sub

It is essentially a find/replace in the hyperlink address. Add the path names if desired if the path as well as the drive is changing…

Steve

Reply | Quote

Hi

The code gives me an error “it cannot open specified file”.
This is what i have

Sub changelink()
Dim wkb As Workbook
Dim wks As Worksheet
Dim hlink As Hyperlink
Dim sOld As String
Dim sNew As String

Set wkb = ActiveWorkbook
Set wks = ActiveSheet

sOld = “C:Documents and SettingssvokozelaMy DocumentsPDF Workstuff”
sNew = “F:PDF Workstuff”
For Each wks In wkb.Worksheets
For Each hlink In wks.Hyperlinks
hlink.Address = Application.WorksheetFunction. _
Substitute(hlink.Address, sOld, sNew)
Next hlink
Next wkb
End Sub
End Sub

Reply | Quote

I think you need to add the “” to the end of the sOld and sNew lines

From
sOld = “C:Documents and SettingssvokozelaMy DocumentsPDF Workstuff”
sNew = “F:PDF Workstuff”

to

sOld = “C:Documents and SettingssvokozelaMy DocumentsPDF Workstuff”
sNew = “F:PDF Workstuff”

Reply | Quote