To Tom Ogilvy

[Ramadan]

Hi Tom
first off all I want to thank you very much for your
response but unfortunately until now no success in my excel
sheet which is very important to my company but any way let
me inform you with the all of aspects existed in my
problem.
we have on our server wich has a name "server1" and it
stores a 10GB of pdf files which are linked with an excel
sheet created by excel2002 and this sheet has nearly 6000
rows and in a certain columns there are a disply names to
pdf files via hyperlinks .to some reason that we don't
know why? we open the excel sheet and click one of
hyperlink but we discover that the address was changed by
doublicating folder name in the path and as I said we
don't know why.
but the strainge thing is that this change was ocured on
all of the addresses which are nearly 6000 records
exactly the previous(correct) local addresses
are"H:\history\folder1\file1.pdf and the wrong now are
H:\history\history\folder1\file1.pdf what we want now to
make a change of all the new hyperlinks to be at
H:\history\folder1\file1.pdf
Please write a cleare code with step by step procedure for
how to register it in excel workbook and how to run it in
order to successfuly change the hyperlink addresses in all
of the records in excel workbook
and thank you very much for your help.

 

[Harald Staff]

Hi

Open the VB editor (Alt F11 or the Tools > Macro menu).
Go menu Insert > Module.
Paste this into the module:

Sub ChangeLinks()
Dim HL As Hyperlink
Dim sOld As String, sNew As String
sOld = InputBox("Replace what:")
If sOld = "" Then Exit Sub
sNew = InputBox("Replace " & sOld & " with:")
If sNew = "" Then Exit Sub
For Each HL In ActiveSheet.Hyperlinks
HL.Address = Replace(HL.Address, sOld, sNew)
HL.TextToDisplay = Replace(HL.TextToDisplay, sOld, sNew)
Next
End Sub

Return to Excel. Open the troublesome file/worksheet sheet if not already
open. Run the macro with menu Tools > Macro > Run.
On prompt "Replace what:" enter H:\history\history
On prompt "Replace H:\history\history with:" enter H:\history

Now test some corrected links BEFORE saving to make sure it's right. If not,
close without save, reopen and repeat until it's right.

 

[GB]

Are they all changed in the same way, ie you have \history\history where you
should have \history?

In that case use Edit... Replace to change them all.

Geoff

 

[Ramadan]

Yes they have changed in the same way
but what you have to know that this change was in Hyperlink
address not to the display names of the hyperlinks and
this mean that you can't find it with the find replace
but any way do you know any way to disply the hyperlink
addresses if so I think that the problem is easy to solve
by find and replace but if not it is then has to do a
correction code like the Tom macro but til now does not
successful and now I am thinking a bout the changing the
location of the file to the parent folder to make overall
change but I am afraid that problem can get back in any
time and this solution is not parctical any way I am
wating Tom's solution I hope to be workable and practical
best regards

 

[GB]

Hi, find and replace works fine with my hyperlinks. Perhaps you could try it
out on a spare copy of your spreadsheet?

My hyperlink formulae look like this:
=HYPERLINK("c:\History\History\Rubbish.xls","Rubbish")

Geoff

 

[Ramadan]

thank you for your help but unfortunately I made the
procedure you made three times but know changing
what I get is the disply of Hyperlink Addresses

 

[Harald Staff]

thank you for your help but unfortunately I made the
procedure you made three times but know changing
what I get is the disply of Hyperlink Addresses

What to say... Too bad. Works fine here.

Best wishes Harald
Followup to newsgroup only please.