Hi, I was looking to see if someone could help me with a problem with hyperlinks. I have an excel file located on my desktop that I use for church on Sunday mornings that has an alphabetical list of songs to choose from. Each title has a hyperlink to aMicrosoft Word document corresponding to its title. Those Word files are located in a folder on my desktop called Worship Songs.

One day when I went to click one of the titles, it gave me a message: cannot open the specified file. I then thought about the hyperlink path being screwed up and sure enough it had somehow changed. It changed to something like:

file:///c:\users\brett\appdata\roaming\microsoft\excel\Wor ship%20Songs\All%20I%20Need%20Is%20You.doc

It no longer had the desktop in the pathway. Can someone help me with a step by step fix to this that I could maybe fix the links all at once? There are way too many to fix manually. Your help would be most desired. Thank you!

Hi, I was looking to see if someone could help me with a problem with
hyperlinks. I have an excel file located on my desktop that I use for
church on Sunday mornings that has an alphabetical list of songs to
choose from. Each title has a hyperlink to aMicrosoft Word document
corresponding to its title. Those Word files are located in a folder
on my desktop called Worship Songs.

One day when I went to click one of the titles, it gave me a message:
cannot open the specified file. I then thought about the hyperlink
path being screwed up and sure enough it had somehow changed. It
changed to something like:

file:///c:\users\brett\appdata\roaming\microsoft\excel\Wor ship%20Songs\All%20I%20Need%20Is%20You.doc

It no longer had the desktop in the pathway. Can someone help me with
a step by step fix to this that I could maybe fix the links all at
once? There are way too many to fix manually. Your help would be most
desired. Thank you!

Why don't you just store the songs in the Excel workbook so your
hyperlinks can just point to the sheet (or range) where the songs are
located?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

How do you store them in the workbook? Is this a quick change that it could switch each song title so that it opens up its respective Word file? I've heard of people getting hyperlinks fixed in mass, just not sure how to do it for mine.

Hi,

Am Sun, 25 Sep 2016 17:43:15 +0100 schrieb PitchNinja:

How do you store them in the workbook? Is this a quick change that it
could switch each song title so that it opens up its respective Word
file? I've heard of people getting hyperlinks fixed in mass, just not
sure how to do it for mine.

you can change the path in the address with VBA. What is the expected
path? You only posted the actual hyperlink.


Regards
Claus B.
--
Windows10
Office 2016

Hi, I just want it to go back to the folder on my desktop. The folder is called "Worship Songs" and it as all of the Microsoft Word files in it that the excel sheet should be hyperlinked to. Hope that helps you.

Hi, just wondered if you by chance had had a solution regarding the macro that could be used?

Hi,

Am Fri, 30 Sep 2016 22:20:02 +0100 schrieb PitchNinja:

Hi, just wondered if you by chance had had a solution regarding the
macro that could be used?

modify the strings in the code to your expected path:

Sub ChangeHyperlink()
Dim i As Long
Dim HypAlt As String
Dim HypNeu As String

'Hier alten und neuen Pfad anpassen
HypAlt = "appdata\roaming\microsoft\excel"
HypNeu = "Desktop"

For i = 1 To ActiveSheet.Hyperlinks.Count

ActiveSheet.Hyperlinks(i).Address = _
Replace(ActiveSheet.Hyperlinks(i).Address, HypAlt, HypNeu)

Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016

'GS[_6_ Wrote:
;1625872'] Hi, I was looking to see if someone could help me with a
problem with-
hyperlinks. I have an excel file located on my desktop that I use
for church on Sunday mornings that has an alphabetical list of
songs to choose from. Each title has a hyperlink to aMicrosoft Word
document corresponding to its title. Those Word files are located
in a folder on my desktop called Worship Songs.

One day when I went to click one of the titles, it gave me a
message: cannot open the specified file. I then thought about the
hyperlink path being screwed up and sure enough it had somehow
changed. It changed to something like:

file:///c:\users\brett\appdata\roaming\microsoft\excel\Wor ship%20Songs\All%20I%20Need%20Is%20You.doc

It no longer had the desktop in the pathway. Can someone help me
with a step by step fix to this that I could maybe fix the links
all at once? There are way too many to fix manually. Your help
would be most desired. Thank you!-

Why don't you just store the songs in the Excel workbook so your
hyperlinks can just point to the sheet (or range) where the songs
are located?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
How do you store them in the workbook? Is this a quick change that it
could switch each song title so that it opens up its respective Word
file? I've heard of people getting hyperlinks fixed in mass, just not
sure how to do it for mine.

You put the songs in the workbook and point your hyperlinks to their
respective location.

Optionally, if you give the song titles a defined name you can go from
song to song via the NameBox left of the FormulaBar.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus