Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
Network Links
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 |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
Network Links
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 |
#3
Posted to microsoft.public.excel.links
|
|||
|
|||
Network Links
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 |
#4
Posted to microsoft.public.excel.links
|
|||
|
|||
Network Links
Try
File/Properties/Summary/Hyperlink Base = \\SomeNonExistentServer\Folder Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#5
Posted to microsoft.public.excel.links
|
|||
|
|||
Network Links
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 |
#6
Posted to microsoft.public.excel.links
|
|||
|
|||
Network Links
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 |
#7
Posted to microsoft.public.excel.links
|
|||
|
|||
Network Links
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlinks - Move file to local drive, all links break | Excel Discussion (Misc queries) | |||
options to update automatic links | Excel Worksheet Functions | |||
workbook links to .xla file on the network | Excel Discussion (Misc queries) | |||
Creating links to a worksheet on a network. | Excel Worksheet Functions | |||
Creating links to a worksheet on a network. | Excel Worksheet Functions |