I have a number of Excel files on a network server which it is mapped on my
PC as the 'H' drive, however for some users this drive is mapped as their
'K' drive. I have created links to various Access databases for which they
auto update when I open the Excel spreadsheets. However when users (who see
the drive as 'K') try to open this file, they get an error stating that the
file cannot be found.

I have the same problem to some degree with macros which need to open or
update other spreadsheets. Is there a way to specify the server name and
full path rather than H:\Sales\spreadsheet.xls etc?

Thanks for your help

Sure, you can use a UNC pathname
\\SomeServer\SomeDir\SomeSubDir\MyDB.mdb


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

I have the same problem and a related one.
When I specify the UNC path to files, as you suggested, Excel (and Word)
helpfully replace them with a drive letter if one exists. i.e. If in your
example \\SomeServer\SomeDir is mapped to Y:, the hyperlink changes to Y:\...
so it works for me but other people have \\SomeServer\SomeDir mapped to Z:\
so it fails form them.

The second issue is:
I have exported a list from Sharepoint to Excel. Excel correctly shows the
hyperlinks, but sometimes clicking on a link fails saying that the file
cannot be found. The link that's displayed stars with C:\Documents and
Settings\...

Using VBA to follow these links always fails with this error.
So back to the original question - how can I force Excel to keep hyperlinks
exactly as they were entered?
--
Thanks in advance,
Steve


"Bill Manville" wrote:

Sure, you can use a UNC pathname
\\SomeServer\SomeDir\SomeSubDir\MyDB.mdb


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Try
File/Properties/Summary/Hyperlink Base =
\\SomeNonExistentServer\Folder

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

I tried that, but it made no difference.
What happens when I run my macro is:
Excel displays the correct path with the usual warning about ...files may
contain viruses...
I click on OK.
Excel then displays an error with the path changed to:
C:\Documents and Settings\MyName\Local Settings\Temporary Internet
Files\Content.IE5\ZEK7Z9S1\...
This obviously fails, because the file isn't there.
--
Steve Franks


"Bill Manville" wrote:

Try
File/Properties/Summary/Hyperlink Base =
\\SomeNonExistentServer\Folder

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

I think I've sussed it:
The description of FollowHyperlink says that it opens the file from the
cache, or if it isn't in the cache it downloads it to the cache.
The download works, because the filename is less than 250 characters.
Adding the extra characters for C:\Documents and Settings\MyName\Local
Settings\Temporary Internet
Files\Content.IE5\ZEK7Z9S1 makes the file path too long for Windows.
Why Windows allows a file to be saved with a path that is too long for it to
be retrieved is beyond me.

Thanks for your help.
--
Steve Franks

The solution is to use the Hyperlink Address as the name and open the workbook:
Sub SkinCat2()
Dim strAddr As String
Range("C2").Select
strAddr = Selection.Hyperlinks(1).Address
Workbooks.Open Filename:=strAddr, ReadOnly:=True
end sub
--
Steve Franks
Deal, Kent UK


"Steve Franks" wrote:

I think I've sussed it:
The description of FollowHyperlink says that it opens the file from the
cache, or if it isn't in the cache it downloads it to the cache.
The download works, because the filename is less than 250 characters.
Adding the extra characters for C:\Documents and Settings\MyName\Local
Settings\Temporary Internet
Files\Content.IE5\ZEK7Z9S1 makes the file path too long for Windows.
Why Windows allows a file to be saved with a path that is too long for it to
be retrieved is beyond me.

Thanks for your help.
--
Steve Franks