Announcement

Collapse
No announcement yet.

Determine if shared workbook is opened on my machine

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
      
     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 
    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:
     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:

    Code:
    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
    Last edited by pike; 4 days ago. Reason: merge post reset replies to zero
Working...
X