Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 6
Default 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!

Last edited by PitchNinja : September 24th 16 at 02:14 AM
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default 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

  #3   Report Post  
Junior Member
 
Posts: 6
Default

Quote:
Originally Posted by GS[_6_] View Post
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.
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #5   Report Post  
Junior Member
 
Posts: 6
Default

Quote:
Originally Posted by Claus Busch View Post
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.


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default 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

  #7   Report Post  
Junior Member
 
Posts: 6
Default

Quote:
Originally Posted by Claus Busch View Post
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, just wondered if you by chance had had a solution regarding the macro that could be used?
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #9   Report Post  
Junior Member
 
Posts: 6
Default

Quote:
Originally Posted by Claus Busch View Post
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.

Last edited by PitchNinja : October 2nd 16 at 02:31 AM
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default 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


  #11   Report Post  
Junior Member
 
Posts: 6
Default

Quote:
Originally Posted by GS[_6_] View Post
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
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hyperlinks changed by Excel again! Terry Pinnell[_2_] Excel Discussion (Misc queries) 5 May 17th 12 05:26 PM
All Hyperlinks have changed the path bevchapman Excel Discussion (Misc queries) 9 November 19th 09 04:58 AM
Hyperlinks being changed Gary1972 Excel Discussion (Misc queries) 0 September 10th 09 05:17 PM
Changed Hyperlinks !! Ron@Buy Excel Worksheet Functions 1 February 7th 08 04:29 PM
Hyperlinks getting changed on save Dennis Benjamin Excel Programming 1 February 10th 06 04:45 AM


All times are GMT +1. The time now is 08:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"