Macro on server based workbook runs fine for me & colleague, but run-time errors for another colleague

[PerseveringHarold]

I've written a macro for an Excel-based data tool at work, stored on a shared drive. The macro runs successfully for me and another colleague, but two other colleague's that have tested it encounter a 'Runtime Error 53 - File not found'. We're all using different machines accessing the shared drive remotely.

When the macro runs, it checks to see if another file is open first, then copies data from that file into the macro's workbook. But the check is only for an open workbook using a specific filename, no file path is included. Debugging the error highlights one of the Err lines in the Function code for checking whether the other file is open (can't remember right now if it highlights

Err No = Err

or

Case Else: Error ErrNo

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

I've been through one of the colleague's macro & VBA security settings, they appear to match mine. We're running the same version of VBA. They have the same Microsoft Office Object Library ticked in the References.

I'm stuck and rather hoping for help. Thanks.

 

[RoryA]

If you aren't including a file path, you are relying on whatever the current directory happens to be in the user's Excel, and that will not necessarily be where your workbook is (e.g. if they have opened another file in the meantime). Is there a reason you aren't including a path when calling that code?

 

[PerseveringHarold]

If you aren't including a file path, you are relying on whatever the current directory happens to be in the user's Excel, and that will not necessarily be where your workbook is (e.g. if they have opened another file in the meantime). Is there a reason you aren't including a path when calling that code?

Near the top of the Sub there are the lines

Dim Ret

Ret = IsWorkBookOpen("MyFileNameHere.xlsx")

The two files sit in the same network folder, but I wanted to avoid using file paths due to some network migration work that's happening around now.

I should probably disclose the fact that I didn't understand the Function code when I used it - I found it online after searching for a solution to my problem of wanting to check the other file was open first, but evidently it works for me and another colleague.

 

[RoryA]

And is there a reason you aren't including the file path?

 

[PerseveringHarold]

And is there a reason you aren't including the file path?

Well, partly for the reason I mention above - a network migration project has created uncertainty about how well certain network references will be preserved, so I hoped just using a file name and a check to see if a matching file was open would avoid any entanglement with those issues.

And I guess also partly motivated out of flexibility. I keep a test version of the files on my local drive and wanted to be able to run the macro's easily without having two separate versions (local and network) of the macro.

 

[RoryA]

That doesn't really make sense. That code is designed to see if someone else has a specific workbook open already, so you need to refer to that specific file. You must presumably know the path at runtime.

Also, that code is really designed for xls workbooks and doesn't work properly with xlsx files in my experience.

 

[PerseveringHarold]

That doesn't really make sense. That code is designed to see if someone else has a specific workbook open already, so you need to refer to that specific file. You must presumably know the path at runtime.

Also, that code is really designed for xls workbooks and doesn't work properly with xlsx files in my experience.

I appreciate you saying that. As noted above, it's not code I authored. I found it and to my naïve eyes it seemed to perform the task I wanted it to i.e. check whether any file with a matching file name was open. But my lack of in-depth understanding of how Excel and VBA works is obviously exposing me to misunderstandings etc.

 

[PerseveringHarold]

After experimenting a bit more with alternative approaches that I couldn't get running how I wanted, I resorted to amending the macro by adding the full network path for the file in the Sub. That has done the trick of getting it working for all my colleagues so for now I will take that as the solution and deal with any network issues as they arise.

But before I'd made that amendment, the colleague who it worked for yesterday got the run-time error today, so I suspect the issue was related to inconsistencies in which folder Excel thought was the active folder when the macro was run... IDK. Unfortunately I don't really have the resource to explore it fully right now, I just need a working solution in the short-term, which it now seems I have.

Thank you to @RoryA and anyone else who took the time to consider my problem.