View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Terry Pinnell[_2_] Terry Pinnell[_2_] is offline
external usenet poster
 
Posts: 50
Default 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