Determine if shared workbook is opened on my machine

  • I receive an error when I use Workbooks.Open on a shared workbook that is already open on my machine. I've tried examples of code posted on this forum to determine if the file is already opened, including:


    Code
    1. [FONT=arial]Function IsWorkbookOpen(stName As String) As Boolean 'Boolean Function assumed To be False unless Set To True On Error Resume Next ' In Case it isn't Open IsWorkbookOpen = Not (Workbooks(stName) Is Nothing) On Error Goto 0 End Function [/FONT]

    but it always returns False. I think it isn't working because the file is a shared workbook. I tried the Microsoft supplied macro:

    Code
    1. Function IsFileOpen(ByVal filename As String) As Boolean Dim filenum As Integer, errnum As Integer On Error Resume Next filenum = FreeFile() Open filename For Input Lock Read As #filenum Close filenum errnum = Err On Error GoTo 0 Select Case errnum Case 0 IsFileOpen = False Case 70 IsFileOpen = True Case Else Error errnum End Select End Function

    and it will return an err 70 if someone else on the network has the file open, but 0 if open on my machine. Is there a way to tell if I have a shared workbook opened on my machine?
    Sorry, first post. Code snippets messed up. Not sure why
    Trying again: