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")

 

[nemmi69]

Insert something like

Dim PauseTime, Start

If wbMaster.ReadOnly Then
    PauseTime = 5    ' Set duration.
    Start = Timer    ' Set start time.
    Do While Timer < Start + PauseTime
        DoEvents    ' Yield to other processes.
    Loop

Else
    ' continuation
End If

 

[nemmi69]

Ignore previous

Insert something like

Sub SaveDataMasterWorkbook()
Dim wbMaster As Workbook
Dim wbLocal As Workbook
Dim masterNextRow As Long
Dim PauseTime, Start
Dim Rpeat as integer

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


Rpeat=0
do while Rpeat<3
If wbMaster.ReadOnly Then
    PauseTime = 5    ' Set duration.
    Start = Timer    ' Set start time.
    Do While Timer < Start + PauseTime
        DoEvents    ' Yield to other processes.
    Loop
    Rpeat=Rpeat+1
else
    Rpeat=4
end if
loop

 

[Tej92]

Hi @nemmi69, thanks for the reply. when the macro is called it just opens the file immediately in read-only. i've tried to increase the pause time to 5000 as well.

 

[dmt32]

Hi,
Try checking if the File is Open first & if not inform user

Have a look that this thread :Update data in excel via VBA, without opening the file

#post 3 & see if it helps you

copy the function to a standard module & to call it you just modify your code a little

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


Set wbMaster = DatabaseOpen("G:\files\data.xlsx")
if wbMaster Is Nothing Then Exit Sub

‘rest of code

Solution assumes users access the file on your corporate network & not via sharepoint.
Dave

 

[nemmi69]

A local check for file open function returns true or false

'######################################################
' Function IsFileLocalOpen checks if the file is open
' by current user on there computer aleady.
'######################################################
Function IsFileLocalOpen(ByVal iFileName As String)
Dim Wb As Workbook

IsFileLocalOpen = False

For Each Wb In Workbooks
    If Wb.Name = iFileName Then
        IsFileLocalOpen = True
        Exit For
    End If
Next
End Function

 

[Tej92]

Good morning, thanks for the reply.
The master file isn't opened manually by anyone, multiple spreadsheets are using VBA to open -> paste -> close the master file.
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?

 

[nemmi69]

This would check the file 1st if not open will open

'######################################################
' Function IsFileLocalOpen checks if the file is open
' by current user on there computer aleady.
'######################################################
Function IsFileLocalOpen(ByVal iFileName As String)
Dim Wb As Workbook

IsFileLocalOpen = False

For Each Wb In Workbooks
    If Wb.Name = iFileName Then
        IsFileLocalOpen = True
        Exit For
    End If
Next
End Function

Sub SaveDataMasterWorkbook()
Dim wbMaster As Workbook
Dim wbLocal As Workbook
Dim MsFilePath As String
Dim MsFileName As String
Dim masterNextRow As Long
Dim PauseTime, Start
Dim Rpeat As Integer

Set wbLocal = ThisWorkbook
MsFilePath = "G:\files\"
MsFileName = "data.xlsx"


Rpeat = 0
Do While Rpeat < 3
    If IsFileLocalOpen(MsFileName) = False Then
        PauseTime = 6    ' Set duration 6 sec.
        Start = Timer    ' Set start time.
        Do While Timer < Start + PauseTime
            DoEvents    ' Yield to other processes.
        Loop
        Rpeat = Rpeat + 1
    Else
        Set wbMaster = Workbooks.Open(MsFilePat & MsFileName)
        Exit Do
    End If
Loop

End Sub

 

[Tej92]

Hi @nemmi69 i've tried that and it doesn't get to open the workbook. There was a mistype of MsFilePath inside the while loop, corrected that and still nothing.
it waits for 18 seconds before giving me any error.

 

[nemmi69]

Sorry 2 errors logic was wrong and path variable misspelt

'######################################################
' Function IsFileLocalOpen checks if the file is open
' by current user on there computer aleady.
'######################################################
Function IsFileLocalOpen(ByVal iFileName As String)
Dim Wb As Workbook

IsFileLocalOpen = False

For Each Wb In Workbooks
    If Wb.Name = iFileName Then
        IsFileLocalOpen = True
        Exit For
    End If
Next
End Function

Sub SaveDataMasterWorkbook()
Dim wbMaster As Workbook
Dim wbLocal As Workbook
Dim MsFilePath As String
Dim MsFileName As String
Dim masterNextRow As Long
Dim PauseTime, Start
Dim Rpeat As Integer

Set wbLocal = ThisWorkbook
MsFilePath = "G:\files\"
MsFileName = "data.xlsx"



Rpeat = 0
Do While Rpeat < 3
    If IsFileLocalOpen(MsFileName) = True Then
        PauseTime = 6    ' Set duration.
        Start = Timer    ' Set start time.
        Do While Timer < Start + PauseTime
            DoEvents    ' Yield to other processes.
        Loop
        Rpeat = Rpeat + 1
    Else
        Set wbMaster = Workbooks.Open(MsFilePath & MsFileName)
        Exit Do
    End If
Loop
MsgBox "Count " & Rpeat
End Sub