Need help with many changed Hyperlinks
 

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!

Need help with many changed Hyperlinks
 

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.

Need help with many changed Hyperlinks
 

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.

Need help with many changed Hyperlinks
 

'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

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

Need help with many changed Hyperlinks
 

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

I'm sorry but I can not get this to work. I really don't know what I am doing either. Could you give me step by step instructions on what to do? Also I need to know if I even gave you the correct hyperlink paths. I am not even sure if the way I found them was they way to do it. Maybe you could tell me? OR If it would be easier, I can give you my email address and maybe we could correspond back and forth via there and maybe I attach the excel file? I am sorry to be a pain, I just pretty much need my hand held all the way through this as I have absolutely zero understanding of this stuff.

Need help with many changed Hyperlinks
 

Claus Busch;1626147 Wrote:
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
I'm sorry but I can not get this to work. I really don't know what I
am doing either. Could you give me step by step instructions on what
to do? Also I need to know if I even gave you the correct hyperlink
paths. I am not even sure if the way I found them was they way to do
it. Maybe you could tell me? OR If it would be easier, I can give you
my email address and maybe we could correspond back and forth via
there and maybe I attach the excel file? I am sorry to be a pain, I
just pretty much need my hand held all the way through this as I have
absolutely zero understanding of this stuff.

Would you like to see how this will be much easier if you put the songs
in the Excel file?

--
Garry

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

If you can give me a step by step guide on how to do it, I am willing to try it

Need help with many changed Hyperlinks
 

Would you like to see how this will be much easier if you put the
songs

in the Excel file?

--
Garry

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

If you can give me a step by step guide on how to do it, I am willing
to
try it

I did this for a bunch of special diet recipes, so I know it works! I
turned off gridlines and headings so my worksheets look like Word
pages. (This also works in LibreCalc)

Just copy your songs into a worksheet; -this can be 1 sheet for a group
of songs of a given 'type', a separate sheet for each song 'type'. (ie:
praise, inspiration, joyous, ...)

Change your current hyperlinks to ref the location of the cell
containing its song title. (Place in This Document)

This will work best if you give each song title a local scope defined
name. Also give A1 on your hyperlinks sheet (Index page) a workbook
level defined name so you can jump back to it from any song sheet.
(Your hyperlink text should be the song title)

--
Garry

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