Hi All

Following a previous thread regarding mapped drives and hyperlinks, I have discovered that the content of the hyperlinks in a spreadsheet appear to have changed.  When clicking these, the user is simply presented with "Cannot open the specified file".

The hyperlinks open external documents such as PDF files.

Looking at the hyperlink paths they are typically ../../SPARES/PT/PT2000-PT2099/PT2009/PO%2012496.PDF.  However, the correct path has spaces either side of the hyphen symbol.  If I edit one of the hyperlinks manually and add back the spaces, the link works fine.  So it appears that, for some reason, Excel has dropped the spaces in all the hyperlinks.

Is there any code way I can globally replace "-P" with " - P" to add back the spaces?

The other option is to rename all the folders that are the targets for the hyperlinks, but there are a lot and this will have backup implications.

Thanks

Phil

Hi TheHC,   You should be able to do that using my flexfind add-in: www.jkp-ads.com/officemarketplaceff-en.asp    

Hello Jan

Many thanks - your utility has modified hyperlinks as required.

Can you tell me whether the loss of the spaces in the hyperlink path to the files is likely to have been caused by Excel?  I have asked users and no one is admitting to changing the folder names - I really can't see why they would want to.

On a matter of personal coincidence, both myself and my wife live in the UK and all our relatives except one are/have been English.  The one exception is my wife's aunt who married a Dutch man called Jan Pieterse!  Is this a common name?

Phil

Hi Phil,   I don't know whether Excel decides to wreck the links itself or that some user has done so. I have heard about links being altered more than once though.   If you want them to stay put, I suggest to use the HYPERLINK function pointing to a separate cell that holds the hyperlink address as text. That way, Excel cannot change them as the link is created on-the-fly by the hYPERLINK function.   Pieterse isn't really a very common name in NL, they seem to originate from the province of "Zuid Holland" of The Netherlands mostly: http://nl.geneanet.org/familienamen/PIETERSE    

Thanks Jan

So I have cell A1 with formula =Hyperlink(C1) and in cell C1 I have the path to the file in text form.

This works fine, but how do I display some text in cell A1 for the user to select?

Phil

Hi Phil,   Have a look at the second argument of the HYPERLINK function...    

Many thanks Jan - just needed pointing in the direction.

Can't you tell I'm a very occasional user!!

Phil