Hi all,
Hope any of you can help me with this. I have a workbook that needs to open another workbook which is protected by password. However, before opening this second workbook, I would like to check if the workbook is already open by someone else. Now, I thought of the options to do so: the Workbook.Open statement and the Open statement. However, the former gives me a pop-up box when I try to open it (e.i: "The file is locked for editing by John Doe. Do you want to save a local copy or open in read-only?"), the latter doesn't seem to work on protected workbooks.
Can anyone please help me out to make one of these lines of code work?
Do Until file_open = True
On Error Resume Next
Set wb2 = Workbooks.Open(filename:="https://XYZ.com/ABC.xlsb", ReadOnly:=False, Password:="PW1234")
errnum = Err
Select Case errnum
Case 70, 1004
If a = 0 Then MsgBox ("Masterfile is opened by another user, the sytem will keep trying." & vbLf & vbLf & "Thank you for your patience")
wb2.Close (False)
Application.Wait (Now() + TimeValue("00:00:05"))
a = a + 1
Case Else
file_open = True
End Select
On Error GoTo 0
Loop
This first code gives me the pop-up window I was talking about, could someone tell me how to disable this pop-up? I tried Application.Displayalerts=False, no use.
Function IsFileOpen(filename As String)
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
This second piece of code doesn't seem to work with password protected workbooks. Does anyone know how to make it work?
Bookmarks