# Solved: Excel 2003: Possible to Globally Change Hyperlinks?



## Pausebreak (Nov 1, 2006)

Hi, all.

I've searched here and Googled. The problem (but no solution) has been posted elsewhere and I'm hoping someone here can help.

Problem: Nearly 1,000 hyperlinks in an Excel 2003 workbook now point to the wrong directory/drive and need to be repointed. For example:

Old hyperlink: ///dsnetapp\\100\125\data\[doc name].pdf
New hyperlink: I:\100\125\data\[doc name].pdf

All of the documents to which the hyperlinks point have been moved to a new drive and no longer work.

What I've tried: Search and replace (doesn't work), manually editing a single hyperlink (doesn't work). Everything reverts back t the ///dsnetapp\\ address. The workbook, worksheet, and cells are not protected. The workbook is not read-only.

The only thing that seems to work is re-doing each hyperlink (go into Edit Hypelink, navigate to new location, select pdf, and save). I would like to find some fix that's less time-consuming and would appreciate any assistance.

Thank you.

Brent


----------



## Jimmy the Hand (Jul 28, 2006)

Something like this might do. It replaces "///dsnetapp\" by "I:" in each hyperlink on the active sheet both in the address and the displayed text.


```
Sub test()
    For Each hl In ActiveSheet.Hyperlinks
        hl.Address = Replace(hl.Address, "///dsnetapp\", "I:")
        hl.TextToDisplay = Replace(hl.TextToDisplay, "///dsnetapp\", "I:")
    Next
End Sub
```
I tested this procedure with C: and D: drive of my home PC, and had a little problem with excel using sometimes "\" instead of "/", but if your hyperlinks are consistent then it should run OK.

Edit
I strongly advise to make a backup copy of your workbook before running this macro.


----------



## Zack Barresse (Jul 25, 2004)

Don't forget to declare your variables.


----------



## Pausebreak (Nov 1, 2006)

Thank you for the macro. I will try this out this afternoon.

However, I do not want to change the display text (that has to remain the same and is different from the hyperlink text). So, if I leave out the TextToDisplay line in the macro, will it still run OK?

For example, the display text is a number and the underlying hyperlink is the full path name to the document. It's part of a legal index, so we need the doc numbers to display. They are not totally sequential (some end up being 200, 200A, 200B, 200B1, 201 . . . ).

Thanks very much for your help.

Brent


----------



## Pausebreak (Nov 1, 2006)

firefytr said:


> Don't forget to declare your variables.


Could you provide more info? VBA/macros in Excel is pretty new to me - only a basic understanding.

I don't know what variables are involved since I only want to change one portion of the hyperlink string (from ///dsnetapp\\ to I:\) in each of the hyperlinked cells (a single column on each page of the workbook).

Thanks.

Brent


----------



## Jimmy the Hand (Jul 28, 2006)

firefytr said:


> Don't forget to declare your variables.


Firefytr,
you seem to be someone with :up: knowledge, please explain me this. 
I know variant type needs more memory than regular types, which can be a disadvantage in some cases,
and that declaring variables can help prevent confusion in more complicated macros. 
But is it really necessary to declare variables in case of such a simple code? What's the point?

Er... no, I'm not kidding. I really would like to know, and sorry if my question is stupid. 

Pausebreak,
yes, just leave out that line, it won't affect the other one.
And sorry for being off-topic in my question above.


----------



## bomb #21 (Jul 1, 2005)

Jimmy -- an Excel expert writes:

"Strictly speaking, declaring variables is optional -- if you don't declare a variable, the compiler will automatically create that variable the first time it is used in code. However, it is VERY BAD practice not to declare your variable and there is NO excuse not to do so. None."

http://www.cpearson.com/excel/DeclaringVariables.htm


----------



## Jimmy the Hand (Jul 28, 2006)

Pausebreak said:


> Could you provide more info? [...] I don't know what variables are involved [...]


As firefytr seems to be no more online, I'll try to answer this. I think he referred to an other thread, where it was* really *necessary to declare a public variable, othervise the code wouldn't run. I think it's _taught_ to always declare the variables you use, and in our case it would mean this small change in the macro:

```
Sub test()
    Dim hl As Hyperlink
    For Each hl In ActiveSheet.Hyperlinks
        hl.Address = Replace(hl.Address, "///dsnetapp\", "I:")
        hl.TextToDisplay = Replace(hl.TextToDisplay, "///dsnetapp\", "I:")
    Next
End Sub
```


----------



## Pausebreak (Nov 1, 2006)

Thank you Jimmy for the info and pointing out what the variable would be.

I'll run the above version instead. I'm pretty good with basic macros in Word and know that VBA can be very powerful, especially in Excel. I'm slowly dipping my toe into that pool. So appreciate having things stated pretty explicitly.

Brent


----------



## Zack Barresse (Jul 25, 2004)

Hi Jimmy,

Sorry, had many phone calls to make. Apologies for being non-contiguous here.

Declaring variables basically comes down to best practices. It doesn't _need_ to be done, but there are many advantages to doing so. First and foremost (IMO) is basically the syntax upkeep. Say you declare all your variables with hungarian notation. If you happen to mis-spell a variable, you'll notice it right away. For example...


```
Dim rngFilter as Range

set rngfiltered = range("A1:E10")
```
The second line was intentionally typed (except what is in quotes) as lower case. When you hit enter after that line, you will see the capitalization take effect, namely with the Set and Range. However, you will _not_ see it with the variable because it is wrong. If you typed the actual variable name in lower case, then hit enter, you'd see it show up as you declared it, thus double checking yourself.

The other big issue is the performance, which you have already touched on. Either not declaring the variables, or not specifically dimensioning the type (i.e. Dim myVariable) will be compiled as a Variant type, which is performed right at the start of run-time. Dimensioning your variables correctly will not over use your memory and - generally with larger sets of code - you may see an improvement in your code efficiency.

As referenced by Andy at Chip Pearson's site (although, I disagree that it is VERY BAD, I just think it is BAD  ) you can use Option Explicit at the beginning of every module. For me, this goes back to the first reason I stated: typing accuracy. I make as many typos - or more - than the next person. This catches them for me and ensures that my code will be more cohesive and smooth.

With VBA knowing what variable type you are declaring, it is all setup to receive/output information with that variable and won't need to switch in mid-run, which can lead to inefficiency and even crashing sometimes (although the latter is extreme).

Also, as mentioned by Chip, is the fact that you have the ability to use Intellisense with a declared variable (of a specific type, not including Variant or Object types, etc). The same goes for using Late vs Early Binding. MS MVP (Excel) Ken Puls has a great article on the differences of these here: http://www.excelguru.ca/node/13, and MS MVP (Excel) Bob Phillips has another good one here: http://www.xldynamic.com/source/xld.EarlyLate.html.

Also note that when declaring variables, this declares only one variable as Long (the last one), the rest are Variant types...


```
Dim i, j, k as Long
```
This is shown on Chip's site here: http://www.cpearson.com/excel/variables.htm

Hope some of this helps. Btw, there is a good discussion of best practices over at PODA (Professional Office Developers Association).


----------



## bomb #21 (Jul 1, 2005)

firefytr said:


> (edited) As referenced by Andy at Chip Pearson's site (although, I disagree that it is VERY BAD, *I just think it is BAD*  )


LOL!  I am *SO* glad you said that ; the guilt was becoming unbearable.


----------



## Pausebreak (Nov 1, 2006)

Well, I'm not sure if this problem can be marked as solved.

None of the suggested macros worked. It appears the option to edit links (except manually) is locked down in Excel for our organization. 

Additionally, "I:\" appears to be a pointer only, not a physical drive. Maybe a virtual folder?. After manually changing a link to I:\ . . . ., the path name updates from ///dsnetapp\ to ///clerrnetapp\ (the new drive's actual location). And it works - the documents open properly.

But 1,000+ links manually edited is painful and slow.

My sincere thanks to everyone's suggestions and the info I picked up on variables.

Brent


----------



## bomb #21 (Jul 1, 2005)

To be honest, I had my doubts about a macro fix because ... if you can't *edit* it manually, why should a macro fare any better?

Maybe a macro that replicates the *exact* actions you make when doing it the way it *does* work (delete hlink -- create new hlink from amended version of what it was *before* deletion) might _work_.


----------



## Pausebreak (Nov 1, 2006)

Bomb - Thanks for the suggestion.

I did try that and it didn't work - the macro seems cell-specific. Here's what I did:

Start Macro.
Right-click, edit hyperlink.
Delete characters ///dsnetapp\\ (in the hyperlink address line in the popup window).
Type i:\
Press "enter" to accept change and close window.
Arrow down to next cell.
Stop Macro.

Here's how it came out in VBA:

Sub Edit()
'
' Edit Macro
' Macro recorded 11/27/2006 by Brent H
'
' Keyboard Shortcut: Ctrl+q
'
Selection.Hyperlinks(1).Address = _
"I:\Data\19FILES\19233\Discovery Motions\2DM.pdf"
End Sub

I don't know why it picked up everything after I:\. I didn't type it, highlight it, copy it, etc.

If it rings any bells, I'd be happy for feedback.

Brent


----------



## bomb #21 (Jul 1, 2005)

But Brent, looking at that, it seems to be just editing the existing rather than doing any physical deleting -- which I think is the crucial bit that makes it work (when you do it manually).

I'm thinking something like:

Sub Test()
old_link = ActiveCell.Text
Selection.Hyperlinks.Delete
new_link = Replace(old_link, "///dsnetapp\\", "I:\")
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
new_link, TextToDisplay:=new_link
End Sub

Of course you'd need to navigate manually to a link before running it -- but the important thing is to get a working method first, _then_ figure how to "globalise" it.


----------



## Pausebreak (Nov 1, 2006)

Bomb - Thanks. I'll give that a try.

Can I leave out the TextToDisplay:=new_link? I don't want the link to display - only the text in the cell. So the cell will show something like "193", but when you click the link it opens the linked PDF.

Heck, if I could just run a macro, hit the down arrow, and run it again, that would be miles ahead of where I am now and would go pretty quickly.

Brent


----------



## bomb #21 (Jul 1, 2005)

But if the cell text *isn't* the old link, then *old_link = ActiveCell.Text* won't be able to pick up the old link (for amending).


----------



## Pausebreak (Nov 1, 2006)

I see what you mean and that may be the root of the problem. Here's how each link gets set up:

Ctrl+K to hyperlink.
Navigate to document to be hyperlinked and hit OK.
Change "Text to Display" from default (which would be the path/link) to the number to display and hit OK.

So the cell is hyperlinked, but the display text is not the link, only a number typed in.

If I wanted to devote the time to it, a better way might be to create a column that does display the whole path, hide it, and hyperlink the cell in the first column to the hidden column. If I had to do this again, I could unhide and do a search/replace on that column only. Oh my!

Thanks any way. The mysterious powers of Excel - often rewarding, at times frustrating.  

Brent


----------



## bomb #21 (Jul 1, 2005)

Try this with one of your links selected, where cell text <> link:

Sub display()
MsgBox ActiveCell.Hyperlinks(1).Address
End Sub

(found at David McRitchie's site)


----------



## bomb #21 (Jul 1, 2005)

Using your set-up method, the link automatically converts to:

/.///dsnetapp\\100\125\data\[doc name].pdf

i.e. "/." added to the front.

But running this:

Sub Test()
old_link = ActiveCell.Hyperlinks(1).Address
Selection.Hyperlinks.Delete
new_link = Replace(old_link, "/.///dsnetapp\\", "I:\")
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
new_link
End Sub

_does_ give me I:\100\125\data\[doc name].pdf *and* leaves the cell text "as is".


----------



## Pausebreak (Nov 1, 2006)

Bomb - This is so close it's unbelievable! Thanks.

This does exactly what I need. Unfortunately, after I run it on one cell, it doesn't work again. ????

Is there something with the ActiveCell.Hyperlinks(1)?? The number in parens seems like it's a limiter.

It does correct the one cell and leaves the display as is (it does change the position of the text in the cell, though). 

Brent


----------



## Zack Barresse (Jul 25, 2004)

What is the previous position of the text in the cell? Or better yet, what would you _like_ the position of the text to be in the cells?

Also, what is your sheet name and exact range?


----------



## bomb #21 (Jul 1, 2005)

The text alignment is no big deal, in the grand scheme of things.

I can't comment on the significance of the (1), I just stumbled on it at David's site -- http://www.mvps.org/dmcritchie/excel/buildtoc.htm, or thereabouts. Maybe you can get to the bottom of it there.


----------



## bomb #21 (Jul 1, 2005)

firefytr said:


> What is the previous position of the text in the cell? Or better yet, what would you _like_ the position of the text to be in the cells?
> 
> Also, what is your sheet name and exact range?


Oops, didn't see the _codemeister_ there.


----------



## Pausebreak (Nov 1, 2006)

firefytr - Thanks for helping. I don't care about the position of the text in the cell. I can fix that very easily. Just mentioned it in case it helped in figuring out a solution.

I need this to run on several sheets in the same workbook, all with different names. The ranges are different, too. Most of them are listed in Column A. One worksheet is Discovery Motions, range A1:A517, but some rows are hidden and do not need to be updated.

Another worksheet is Key Docs, range A2:A39. There's no real uniformity from one worksheet to the next, other than most of the cells that need the hyperlink updated are in column A. Some worksheets have the links in different columns though.

So a simple macro that could be run on each worksheet (or even just one cell at a time) would be fine. 

Currently, I'm right clicking, editing, deleting the old address and pasting in the new address (thank goodness cut-and-paste works in almost every field in Office).

Brent


----------



## Pausebreak (Nov 1, 2006)

And to continue the quotes from Chip Pearson's site (which I like - when I understand it):



> Embedded hyperlinks that you create by Right-clicking on a cell and inserting a hyperlink create an object.
> 
> HYPERLINK Worksheet Formula is the other. You can see both in my build.htm page, but mainly of the hyperlink object variety and programmed.
> 
> Since Chip Pearson has indicated that embedded hyperlinks can cause problems they add to the number of objects in your workbook, and that the formula hyperlinks do not cause problems, would suggest sticking to the Worsheet Functions variety, which you can include as a formula in programming, and double any internal quotes.


And, of course, I'm using Embedded Hyperlinks. I will remember this for the future and will look into how to create formula hyperlinks for future databases. I will say, at this point, I inherited this. Not that I wouldn't have done it differently, but I inherited it. 

Brent


----------



## Pausebreak (Nov 1, 2006)

I'm marking this solved only because I've discovered that the embedded hyperlinks (per the message above) are truly, awesomely difficult to work with - unless the hyperlinked location will never, ever change. Embedded hyperlinks are the ones created by using Ctrl+K or right-clicking a cell and choosing "create hyperlink". In other words, the easiest ways to create them are the most difficult to work with later.

Best think to do is the "hyperlink worksheet function". Very good instructions in Excel's help on this. Location of docs change? No problem - simple search and replace. Ditto for documents changing name, etc. You can also hyperlink to specific locations in other spreadsheets or in Word docs if you like.

I'll be converting all of my embedded links to hyperlink functions. Slowly and over time.

Thanks one and all for the education and help. Great group of folks!

Brent


----------

