How do mass change excel hyperlinks

Need to do mass changes to excel 2003 documents.  These current hyperlinks point to \\server\dir1\dir2\dir3\filename.???

need to change the portion "\\server\dir1\" to "N:\"

saw many macros and vb code that have worked (apparently) but i've had no such luck.  Even found one thread that prompted the user for the before and after while keeping track of how many it found and changed.  This one counted 187 hyperlinks but fixed none.
rlindstromAsked:
Who is Participating?
 
William ElliottSr Tech GuruCommented:
0
 
William ElliottSr Tech GuruCommented:
can you uploasd a sample workbook with a before and after sheet,..

i just want to see how you have the links setup, then i can write something to change them for you
0
 
rlindstromAuthor Commented:
Need to change the hyperlinks from:

\\servername\dir1\dir2\dir3\filename

to

N:\dir2\dir3\filename

or put another way replace \\servername\dir1\  with N:\

thx.
FixturesAll.xls
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
William ElliottSr Tech GuruCommented:
this should work for you

you can export this to a seprate excel file and create an addin (file-saveas-addin) if you want this available for multiple spreadsheets.

i didn't put too much error correcting logic into it, but i made it useful

FixturesAll.xls
0
 
rlindstromAuthor Commented:
the button isn't operational ?
0
 
William ElliottSr Tech GuruCommented:
what does it do for you?
it works fine for my excel 2003/2007
0
 
rlindstromAuthor Commented:
clicking on the macro button has no effect at all
0
 
William ElliottSr Tech GuruCommented:
tools
macro
visual basic editor

double click on "sheet 1"

on the right side, verify that this is there


Private Sub CommandButton1_Click()
hl_replace.Show
End Sub



if it is there, then make sure that your maco security allos you to run macros.
0
 
rlindstromAuthor Commented:
macro security issue

now it replaces 0 hypelinks ?  tried multiple formats and hyperlinks

??
hyperlink-change-error.doc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.