VBA to open another file and if already opened to wait and reattempt

[Tej92]

Hello everyone, I'm using the following line that is part of a sub to open a file and paste some data to it, a lot of people will be opening it through VBA for the same reason.
is it possible to have excel to attempt opening and if it's already opened wait 10 seconds and retry?

Sub SaveDataMasterWorkbook()
Dim wbMaster As Workbook
Dim wbLocal As Workbook
Dim masterNextRow As Long
Set wbLocal = ThisWorkbook


Set wbMaster = Workbooks.Open("G:\files\data.xlsx")

 

[Tej92]

getting message Count 3 but it doesn't open file.

 

[dmt32]

I'd like the code to try and open and if it's already opened wait 5/10 seconds and retry.
VBA takes 5 seconds max to open, paste and close so it isn't necessary to have the operators wait and click again.
is something like this possible?

Hi,
NOT TESTED but see if this updated version to the function does what you want

Function DatabaseOpen(ByVal FileName As String, Optional ByVal UpdateLinks As Variant, Optional ByVal ReadOnly As Boolean, Optional ByVal Password As String) As Workbook
    Dim Response    As VbMsgBoxResult
    Dim FileInUse     As Boolean
    Dim i                   As Long
    
    'dmt32 April 2023
    If Not Dir(FileName, vbDirectory) = vbNullString Then
        If Not ReadOnly Then
            'check if file already open read/write
            Do
                On Error Resume Next
                Open FileName For Binary Access Read Lock Read As #1
                Close #1
                FileInUse = CBool(Err.Number > 0)
                On Error GoTo 0
                If FileInUse Then
                    i = i + 1
                    If i > 3 Then
                        'after 3 attempts, inform user read / write file in use
                        Response = MsgBox("File Is Open For Editing By Another User." & Chr(10) & _
                        "Do You Want To Try Again?", 37, "File In Use")
                        If Response = vbCancel Then Set DatabaseOpen = Nothing: Exit Function
                        i = 0
                    End If
                    'pause before re-trying
                    If i > 0 Then Application.Wait (Now + TimeValue("0:00:05"))
                End If
            Loop Until Not FileInUse
        End If
        
        Set DatabaseOpen = Workbooks.Open(FileName, UpdateLinks:=UpdateLinks, ReadOnly:=ReadOnly, Password:=Password)
        
    Else
        MsgBox FileName & Chr(10) & "File / Folder Not Found", 16, "Not Found"
        Set DatabaseOpen = Nothing
    End If
End Function

Function checks directory path & if Read/ write file is open (you should be able to have read-only open) - and if so, should pause 5 seconds before re-trying. After 3 attempts user is given option to re-try or cancel.

Dave

 

[Tej92]

where do i need to add this function and where do i add the file name and path? @dmt32

 

[dmt32]

where do i need to add this function and where do i add the file name and path? @dmt32

as explained in #post 5

Dave

 

[Tej92]

That is just perfect!!! and does more then i asked for.
Really appreciate the help, thank you!

 

[dmt32]

That is just perfect!!! and does more then i asked for.
Really appreciate the help, thank you!

As stated in my post June 2022, function was part of a timesheet application I developed for 250 users & seemed to perform without any issues.

Glad we managed to resolve your issue & appreciate your feedback

Dave