Network Links

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Keith - 06 Jul 2006 20:13 GMT

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

Reply to this Message

Bill Manville - 08 Jul 2006 00:08 GMT

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

Reply to this Message

Steve Franks - 15 Aug 2006 15:16 GMT

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?

Signature

Thanks in advance,
Steve

> 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

Reply to this Message

Bill Manville - 16 Aug 2006 00:49 GMT

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

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

Reply to this Message

Steve Franks - 16 Aug 2006 10:07 GMT

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.

Signature

Steve Franks

> Try
> File/Properties/Summary/Hyperlink Base =
[quoted text clipped - 3 lines]
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup

Reply to this Message

Steve Franks - 16 Aug 2006 11:18 GMT

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.

Signature

Steve Franks

Reply to this Message

Steve Franks - 16 Aug 2006 15:03 GMT

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

Signature

Steve Franks
Deal, Kent UK

> I think I've sussed it:
> The description of FollowHyperlink says that it opens the file from the
[quoted text clipped - 7 lines]
>
> Thanks for your help.

Reply to this Message