PDA

View Full Version : Solved: open workbook



benong
10-25-2010, 01:33 AM
hi,
i want to achieve something like this through a button on a userform:
If i try to open a workbook and that workbook is currently in use by other user. Then it will try to open the workbook again at 2 sec later, make 5 attempts before it decide to pop up message box to ask user to try again later. How to put that in code? I tried but failed. Below is my code


On Error Resume Next
Do
Set wbk = Workbooks(DB_Workbook)
Application.Wait (Now + TimeValue("00:00:5"))
Loop Until Err <> 0

Bob Phillips
10-25-2010, 01:41 AM
Function IsFileOpen(FileName As String) As Boolean
Dim iFilenum As Long
Dim iErr As Long

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

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select
End Function

Sub test()
Dim fOpen As Boolean
Dim cnt As Long
Do
cnt = cnt + 1
fOpen = IsFileOpen("filename")
Application.Wait Now + TimeValue("00:00:05")
Loop Until Not fOpen Or cnt >= 5
If Not fOpen Then Set wbk = Workbooks.Open "filename"
End Sub

benong
10-25-2010, 06:22 PM
Thanks xld, it works great :)