Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlinks changed by Excel again!
I am so frustrated! The following problem has occurred before and took me
ages to fix. Now it's happened again out of the blue. I have a large spreadsheet in Excel 2000 which includes a couple of hundred hyperlinks. I entered these (over a period of years) in 'absolute' form, like this example: C:\Docs\My Videos\PROJECTS\Dovedale 2010 Project\DVD cover possibles\Front-1.jpg But today I found that opening one displays the accurate but singularly unhelpful message 'An unexpected error has occurred'. And on examining the hyperlinks, I found that they are all now in 'relative' form like this: ...\..\..\My Videos\PROJECTS\Dovedale 2010 Project\DVD cover possibles\Front-1.jpg How can that arise? What is triggering Excel to make that unsolicited change? It wouldn't matter so much if I never moved stuff around, but that's precisely why I used absolute locations in the first place. Dave Paterson gave me some advice on this on that previous occasion and suggested entering a permanent Hyperlink base, such as C:\ I tried it without success and I see that box is now empty. I've just tried entering C:\Docs and then C:\Docs\ but now on trying to open a hyperlink I get 'The address of this site is not valid. Check the address and try again.' Hardly surprising, as the hyperlink itself hasn't changed and I don't really know how this 'hyperlink base' is getting used. If it's simply a prefix to all my entries then that would now result in links like C:\Docs..\..\..\My Videos\PROJECTS\Dovedale 2010 Project\DVD cover possibles\Front-1.jpg which obviously aren't valid. The central question is, with no base specified and absolute filenames entered, why does Excel suddenly revert to these relative addresses? Dave or anyone have any advice on this exasperating problem please? -- Terry, East Grinstead, UK |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlinks changed by Excel again!
Terry Pinnell explained on 5/16/2012 :
I am so frustrated! The following problem has occurred before and took me ages to fix. Now it's happened again out of the blue. I have a large spreadsheet in Excel 2000 which includes a couple of hundred hyperlinks. I entered these (over a period of years) in 'absolute' form, like this example: C:\Docs\My Videos\PROJECTS\Dovedale 2010 Project\DVD cover possibles\Front-1.jpg But today I found that opening one displays the accurate but singularly unhelpful message 'An unexpected error has occurred'. And on examining the hyperlinks, I found that they are all now in 'relative' form like this: ..\..\..\My Videos\PROJECTS\Dovedale 2010 Project\DVD cover possibles\Front-1.jpg How can that arise? What is triggering Excel to make that unsolicited change? It wouldn't matter so much if I never moved stuff around, but that's precisely why I used absolute locations in the first place. Dave Paterson gave me some advice on this on that previous occasion and suggested entering a permanent Hyperlink base, such as C:\ I tried it without success and I see that box is now empty. I've just tried entering C:\Docs and then C:\Docs\ but now on trying to open a hyperlink I get 'The address of this site is not valid. Check the address and try again.' Hardly surprising, as the hyperlink itself hasn't changed and I don't really know how this 'hyperlink base' is getting used. If it's simply a prefix to all my entries then that would now result in links like C:\Docs..\..\..\My Videos\PROJECTS\Dovedale 2010 Project\DVD cover possibles\Front-1.jpg which obviously aren't valid. The central question is, with no base specified and absolute filenames entered, why does Excel suddenly revert to these relative addresses? Dave or anyone have any advice on this exasperating problem please? AFAIK, all 'file' links set in Excel are (locally) relative to where the workbook is stored on the machine the workbook is running on. When you move the file to another machine or location the link refs change from (locally) relative to 'remote refs'. I'm not sure but I'd expect using the HYPERLINK() function would preserve the link 'as entered' since it's stored as an 'address string' rather than a physical hyperlink. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlinks changed by Excel again!
GS wrote:
Terry Pinnell explained on 5/16/2012 : I am so frustrated! The following problem has occurred before and took me ages to fix. Now it's happened again out of the blue. I have a large spreadsheet in Excel 2000 which includes a couple of hundred hyperlinks. I entered these (over a period of years) in 'absolute' form, like this example: C:\Docs\My Videos\PROJECTS\Dovedale 2010 Project\DVD cover possibles\Front-1.jpg But today I found that opening one displays the accurate but singularly unhelpful message 'An unexpected error has occurred'. And on examining the hyperlinks, I found that they are all now in 'relative' form like this: ..\..\..\My Videos\PROJECTS\Dovedale 2010 Project\DVD cover possibles\Front-1.jpg How can that arise? What is triggering Excel to make that unsolicited change? It wouldn't matter so much if I never moved stuff around, but that's precisely why I used absolute locations in the first place. Dave Paterson gave me some advice on this on that previous occasion and suggested entering a permanent Hyperlink base, such as C:\ I tried it without success and I see that box is now empty. I've just tried entering C:\Docs and then C:\Docs\ but now on trying to open a hyperlink I get 'The address of this site is not valid. Check the address and try again.' Hardly surprising, as the hyperlink itself hasn't changed and I don't really know how this 'hyperlink base' is getting used. If it's simply a prefix to all my entries then that would now result in links like C:\Docs..\..\..\My Videos\PROJECTS\Dovedale 2010 Project\DVD cover possibles\Front-1.jpg which obviously aren't valid. The central question is, with no base specified and absolute filenames entered, why does Excel suddenly revert to these relative addresses? Dave or anyone have any advice on this exasperating problem please? AFAIK, all 'file' links set in Excel are (locally) relative to where the workbook is stored on the machine the workbook is running on. When you move the file to another machine or location the link refs change from (locally) relative to 'remote refs'. I'm not sure but I'd expect using the HYPERLINK() function would preserve the link 'as entered' since it's stored as an 'address string' rather than a physical hyperlink. Thanks Garry. So, if from now on I keep the workbook in the same permanent location, and re-enter absolute locations for my hyperlinks yet again, can I then rely on that to work permanently? Note that the files I am linking are in several different folders, across a couple of drives, if that matters. (It certainly makes it hard to define a useful 'base'.) -- Terry, East Grinstead, UK |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlinks changed by Excel again!
Terry Pinnell explained :
Thanks Garry. So, if from now on I keep the workbook in the same permanent location, and re-enter absolute locations for my hyperlinks yet again, can I then rely on that to work permanently? Nope! Won't work too well if you move the files elsewhere. (Using absolute locations only works when you don't move anything around<g!) Note that the files I am linking are in several different folders, across a couple of drives, if that matters. (It certainly makes it hard to define a useful 'base'.) I suggest you use a different method of tracking. Perhaps a file listing utility that also stores the folder path somewhere on the sheet the files are listed on. Concludes using 1 sheet per folder path. The utility can include some right-click menus that let you process selected files in the list however you want. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlinks changed by Excel again!
GS wrote:
Terry Pinnell explained : Thanks Garry. So, if from now on I keep the workbook in the same permanent location, and re-enter absolute locations for my hyperlinks yet again, can I then rely on that to work permanently? Nope! Won't work too well if you move the files elsewhere. (Using absolute locations only works when you don't move anything around<g!) Yes, I'm assuming the linked files remain in their current locations. I'm sure that has also been the case since the *last* time I entered their absolute addresses in the spreadsheet. So the only cause I can think of for the loss of those is that I must have moved the workbook itself. One other possibility is that it's something to do with the fact that this workbook is one of those regularly synced automatically between my PC and my smartphone (a Windows Mobile based HTC Touch Pro 2). Note that the files I am linking are in several different folders, across a couple of drives, if that matters. (It certainly makes it hard to define a useful 'base'.) I suggest you use a different method of tracking. Perhaps a file listing utility that also stores the folder path somewhere on the sheet the files are listed on. Concludes using 1 sheet per folder path. The utility can include some right-click menus that let you process selected files in the list however you want. I'll persist with hyperlinks for the moment, as it's so simple - when it works! The workbook is a record of the DVDs I've made, so the hyperlinks let me immediately display - the video - the case covers - the DVD menu etc. Last August Dave Peterson also suggested the following. It looked a bit heavy going for my rusty skills but I may get stuck in and attempt it if the links revert again. --- Start paste --- I like to use the =hyperlink() worksheet function for this. I've never had one of these worksheet functions change. I'll put the path in a hidden cell (A1 in my example). (Include the trailing backslash, too!) Then put the filenames in A2:Axxx Then use the =hyperlink() formula in B2:Bxx. =hyperlink("File:////"&$a$1&a2,"Click me") and drag down Then when I need to change the folder, I can change it one location (A1). I could embed the path directly in the formula: =hyperlink("File:////c:\my documents\excel\"& a2,"Click me") and use Edit|Replace, but that seems like more work to me. --- End paste --- Meanwhile I've just re-entered 3 columns of absolute hyperlinks again, some 600 entries altogether, after first developing 3 Macro Express macros to do the grunt work. -- Terry, East Grinstead, UK |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlinks changed by Excel again!
Terri,
I agree with Dave's approach si nce it makes for easier ongoing maintenance. My reason for suggesting using the HYPERLINK() function is on par with Dave's assessment! Otherwise, I like the file listing utility approach because I have both an Excel addin and a VB6.exe designed for this. Both allow me to filter for filetypes and display other file metadata I choose to display. It also opens the files for viewing/editing in my specified viewer/editor app. While both these apps are specially designed to explicitly manage CNC machine program files, they could be easily configured to work with any filetypes specified in the OptionsFileTypes list. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlinks being changed | Excel Discussion (Misc queries) | |||
Excel 2007 Hyperlinks randomly changed on there own?? Please help. | Excel Discussion (Misc queries) | |||
hyperlinks changed automatically in excel. | Excel Discussion (Misc queries) | |||
Changed Hyperlinks !! | Excel Worksheet Functions | |||
Hyperlinks getting changed on save | Excel Programming |