My hyperlinks address has chenged in my workbook

  • Thread starter Thread starter Eqbal
  • Start date Start date
E

Eqbal

Hi,

I have created a file manager in excel where i could manage my files by
linking their names via hyperlinks to their comments and .... It is very
handy and i love it. But since it was recovered after a power failure by
excel recovery, my hyperlink addresses to the files have changed to the
recovery folder. Thus i can not open them none. I read in Excel help that
the addresses are relative to the folder where my original excel file is
or could be specified in the File>Property but i couldn't fix the problem
using the abpve.

I am desprate, please tell me what i should do.

Tnx,
eqbal
 
I've never had any trouble when I used the =hyperlink() worksheet function. As
a hyperlink, it works just like the Insert|Hyperlink version.

But I find =hyperlink() easier to work with and less susceptible to
problems--like you're having.

If you want to convert the Insert|Hyperlink versions to =hyperlink()...

Let's say your hyperlinks are all in Column A. I'd insert a new column A and
put a userdefinedfunction in a helper cell in that column.

This is the UDF:

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in B1 (after inserting a new column A), you could put
=getURL(B1) in that helper cell. (and drag down)

Now convert those formulas to values.
Select column A, then Edit|Copy followed by Edit|Paste Special|values

Now select column A and
Edit|replace
what: (recovery path)
with: (real path)
replace all

Now insert another new column A so that you see the same thing as you have in
column C (after inserting 2 new columns).

Put this formula in A1:

=hyperlink(b1,c1)
and drag down.

(almost done)

Now remove the hyperlinks in column C.
Select the column C and run this macro:

sub deletelinks()
selection.hyperlinks.delete
end sub

I'd hide columns B and C (don't delete them, column A needs them).

After you've converted the =geturl() formulas to values and removed the
hyperlinks from column C, you can remove the macros.

Use the instructions at Debra Dalgleish to make sure you won't be prompted for
macros when you open the workbook:
http://www.contextures.com/xlfaqMac.html#NoMacros

Then I don't think you'll have this problem ever again.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

========================
An alternative, if you want to keep the links created by Insert|Hyperlink, but
want to fix the location would be to use a macro by David McRitchie:

Look at this page:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
Look for: Fix Hyperlinks (#FixHyperlinks)
(a little more than half way down)
 
Back
Top