Hyperlinks created on sheet (not VBA) work for some users but not others - Any Reason

[craigos]

Hi All,

The scenario is this:

At work we have a Server with a main folder and sub folders that has approx 2 thousand users with either Modify or Read Only access. These users work across many different locations across the South of England.

They can access an Excel Workbook on the Server and a particular worksheet within that Workbook that has Hyperlinks in it. The Hyperlinks are set to open other Word and Excel documents which contain information they need to read on various topics. There are hundreds of Hyperlinks in that worksheet.

Note the Hyperlinks cannot be hard coded as only some of us have access to the VBA for maintenance, however, at least 60 staff need to post the Topic and subsequent Hyperlink on the worksheet.

Some users are reporting that they cannot open the link getting the 'Cannot open specified file error message.

However, the link is working as others can open it. I tested this by having two users in the same office access the worksheet, select the same Hyperlink and one opened it and the other couldn't. This scenario of some successfully using the hyperlink and others getting the error message is mirrored across our users - some can, some can't. The bizarre thing is the same user who got the error message say at 10pm goes in say at 2pm and can open the Hyperlink, with others having to wait till the next day and then can get in.

The topic information is in many cases, used for real time activities staff have to conduct, so they need access to hit deadlines.

I am now getting inundated with queries over why they cannot access the required file and wondered if this is an known issue with having potentially hundreds of users in the worksheet (Modify or Read Only) and potentially hundreds all trying to open the same Hyperlink and some get the error and others dont.

My response at the moment is 'Excel gets confused' when so many potential users are in the same worksheet because I cannot give a defined / better answer or who knows, that might be the answer.

The server is within a Server farm somewhere, the Workbook is in an Office 2003 format created in XP and we are now using Win 7 and Office 2010 and no choice of trying to develop a 2010 version due to the other complexities of the Workbook and what we use it for.

Any Thoughts.

Craigos

[Kenneth Hobs]

The first thing a coder should do is to check if the file exists. You can use Dir() to do that or one of various other methods. If it does, then check to see if it is open may be needed. Some users do not have access to some network drives and folders.

The GetFileOwner routine only works for 2003 so if a problem occurs, that routine might show who has the file open.

Sub Test_GetFileOwner()  MsgBox GetFileOwner(ThisWorkbook.FullName)
End Sub


' http://www.ozgrid.com/forum/showthread.php?t=143957
Function GetFileOwner(fileName As String) As String
    Dim secUtil As Object
    Dim secDesc As Object
    Dim File_Shortname As String
    Dim fileDir As String
     
    File_Shortname = Dir(fileName)
    fileDir = Left(fileName, InStr(1, fileName, File_Shortname) - 1)
    Set secUtil = CreateObject("ADsSecurityUtility")
    Set secDesc = secUtil.GetSecurityDescriptor(fileDir & File_Shortname, 1, 1)
    GetFileOwner = secDesc.owner
End Function


Sub Test_IsWorkbookOPen()
  MsgBox IsWorkbookOpen("Personal.xls"), , "Personal.xls Open?"
  MsgBox IsWorkbookOpen("Personal.xlsb"), , "Personal.xlsb Open?"
End Sub


Function IsWorkbookOpen(stName As String) As Boolean
    Dim Wkb As Workbook
    On Error Resume Next ' In Case it isn't Open
    Set Wkb = Workbooks(stName)
    If Not Wkb Is Nothing Then IsWorkbookOpen = True
    'Boolean Function assumed To be False unless Set To True
End Function