[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3436
  • Last Modified:

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.
0
rlindstrom
Asked:
rlindstrom
  • 5
  • 4
1 Solution
 
weellioCommented:
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
 
weellioCommented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
rlindstromAuthor Commented:
the button isn't operational ?
0
 
weellioCommented:
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
 
weellioCommented:
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
 
weellioCommented:
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now