How do I change all of the hyperlinks at the same time in Excel?

[Jared]

I am currently working on a project to hyperlink many bookmarked word
documents to a spreadsheet on Excel 2003. It will have probably a
hundred links. However, All of the word documents are saved on my
computer but eventually they will be saved in a different location but
with the same name. I don't want to do all of the hyperlinks until I
am sure that I will be able to change all of them without doing them
one by one again. I hyperlinked a few and then tried to use this code
to see if the hyperlinks would change anything on it:
Sub FixHiperlinks()
Dim OldStr As String, NewStr As String
OldStr = "D:\Documents and Settings"
NewStr = "Jared is the man"
Dim hyp As Hyperlink
For Each hyp In ActiveSheet.Hyperlinks
hyp.Adress = Replace(hyp.Address, OldStr, NewStr)
Next hyp
End Sub

I know that I don't actually want to change them yet and of course I
would never change them to "Jared is the man" but I just wanted to see
if the hyperlinks would change, however, when I tried to run this code
as a macro I got a message that says,
"Object doesn't support this property or method"

I know I'm probably doing something stupid, but can anyone help me know
what I'm doing wrong?

 

[Chip Pearson]

Jared.

hyp.Adress = Replace(hyp.Address, OldStr, NewStr)
should be
hyp.Address = Replace(hyp.Address, OldStr, NewStr)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Jared]

Thanks for responding so quickly, I appreciate it, but I changed my
spelling error and now it runs the macro but when I do so nothing
happens. The hyperlinks are still in the same location as they were
before and I really can't tell what the macro did. How can I get the
hyperlinks to change to a new location without manually changing all of
them one by one? Thanks in advance for the help.

 

[Dave Peterson]

replace is case sensitive if you don't specify:

hyp.Address = Replace(hyp.Address, OldStr, NewStr)

could become:
hyp.Address = Replace(expression:=hyp.address, _
Find:=oldstr,, _
Replace:=newstr, _
compare:=vbTextCompare)

 

[Jim Cone]

You may want to try the free Excel add-in "List Files".
It will list all files of a specified type from a specified folder.
Download from http://www.realezsites.com/bus/primitivesoftware
--
Jim Cone
San Francisco, USA


"Jared" <[email protected]>
wrote in message
I am currently working on a project to hyperlink many bookmarked word
documents to a spreadsheet on Excel 2003. It will have probably a
hundred links. However, All of the word documents are saved on my
computer but eventually they will be saved in a different location but
with the same name. I don't want to do all of the hyperlinks until I
am sure that I will be able to change all of them without doing them
one by one again. I hyperlinked a few and then tried to use this code
to see if the hyperlinks would change anything on it:
Sub FixHiperlinks()
Dim OldStr As String, NewStr As String
OldStr = "D:\Documents and Settings"
NewStr = "Jared is the man"
Dim hyp As Hyperlink
For Each hyp In ActiveSheet.Hyperlinks
hyp.Adress = Replace(hyp.Address, OldStr, NewStr)
Next hyp
End Sub
I know that I don't actually want to change them yet and of course I
would never change them to "Jared is the man" but I just wanted to see
if the hyperlinks would change, however, when I tried to run this code
as a macro I got a message that says,
"Object doesn't support this property or method"
I know I'm probably doing something stupid, but can anyone help me know
what I'm doing wrong?

 

[Jared]

I get an error message when I replace the line that you gave me with
the new code. Any additonal help would be appreciated.
Thanks,
Jared

 

[Dave Peterson]

Sorry, I had a typo. Please change the double commas after oldstr to just one.

hyp.Address = Replace(expression:=hyp.address, _
Find:=oldstr, _
Replace:=newstr, _
compare:=vbTextCompare)

 

[Jared]

Hmm, I'm not getting the error message anymore, but it still doesn't
seem to be doing anything, let me see if I'm doing this right. I go to
tools, click on macro, click on Visual Basic Editor, and I click on
Sheet 1 because that's the sheet that has all the hyperlinks that I
will eventually want to change. I put in the following code:
Sub FixHiperlinks()
Dim OldStr As String, NewStr As String
OldStr = "D:\Documents and Settings"
NewStr = "Jared is the man"
Dim hyp As Hyperlink
For Each hyp In ActiveSheet.Hyperlinks
hyp.Address = Replace(expression:=hyp.Address, _
Find:=OldStr, _
Replace:=NewStr, _
compare:=vbTextCompare)
Next hyp
End Sub
And then I go to tools, macro, macros... and then double click on the
macro that was created when I put the code on sheet 1. After that,
nothing really happened that I can see, no error message. When I put
the cursor over text that is hyperlinked, it still says "D:\Documents
and Settings...." I thought that it should have changed to the new
string "Jared is the man". Is the macro doing something that I'm
unaware of or did I do something wrong? What should happen when I run
the macro? Thanks for all of your help.

 

[Dave Peterson]

I would put it in a general module--not behind the worksheet.

And I would also try changing the link to something that actually exists.

 

[aaron.kempf]

excel is crap.

the fact that you have to loop through and change formulas in 100
different places?

that is proof that your piece of crap software is obsolete.

if you loved your data you would keep it in a database; and then it
would be easy to update.