Add check if files are open and alert user

[ItalianPlatinum]

Hello - I have VBA code what will currently check if a file exists and if it does my code will proceed if not it will give a dialog box.

Looking to add 2 more similar checks and looking to see if possible if I already have the above, the three extra are:

1) check if file A is open (one above as well) - dialog box to warn file is open
2) check if file B is open - dialog box to warn file is open

Sub Run()
    Dim sPath As String, sPartial As String, sFName As String
    Dim rws As Long
   
Application.ScreenUpdating = False
 
'open file using Partial file naming
    sPath = "....\"      ' <<<<< change accordingly
 
    sPartial = "_US_D_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.csv"
    sFName = Dir(sPath & sPartial)
    If Len(sFName) > 0 Then
        Workbooks.OpenText sPath & sFName
   
MY CODE


Workbooks(sFName).Close SaveChanges:=False


    Else
        MsgBox "File not found.", vbExclamation
    End If

End Sub

 

[JLGWhiz]

For Each wb In Application.Workbooks
    MsgBox "Workbook " & wb.Name & " Is open."
Next

 

[ItalianPlatinum]

For Each wb In Application.Workbooks
    MsgBox "Workbook " & wb.Name & " Is open."
Next

Does it matter where I place that and/or if i need to define WB anywhere else in the code?

 

[JLGWhiz]

Sub Run()
    Dim sPath As String, sPartial As String, sFName As String
    Dim rws As Long, wb As Workbook  
        For Each wb In Application.Workbooks
            If wb.name <> ThisWorkbook.Name Then
                MsgBox "Workbook " & wb.Name & " Is open."
            End If
       Next
Application.ScreenUpdating = False
'open file using Partial file naming
    sPath = "....\"      ' <<<<< change accordingly
    sPartial = "_US_D_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.csv"
    sFName = Dir(sPath & sPartial)
    If Len(sFName) > 0 Then
        Workbooks.OpenText sPath & sFName  
MY CODE
Workbooks(sFName).Close SaveChanges:=False
    Else
        MsgBox "File not found.", vbExclamation
    End If
End Sub

 

[ItalianPlatinum]

Sub Run()
    Dim sPath As String, sPartial As String, sFName As String
    Dim rws As Long, wb As Workbook 
        For Each wb In Application.Workbooks
            If wb.name <> ThisWorkbook.Name Then
                MsgBox "Workbook " & wb.Name & " Is open."
            End If
       Next
Application.ScreenUpdating = False
'open file using Partial file naming
    sPath = "....\"      ' <<<<< change accordingly
    sPartial = "_US_D_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.csv"
    sFName = Dir(sPath & sPartial)
    If Len(sFName) > 0 Then
        Workbooks.OpenText sPath & sFName 
MY CODE
Workbooks(sFName).Close SaveChanges:=False
    Else
        MsgBox "File not found.", vbExclamation
    End If
End Sub

But this will tell me all open workbooks i have open. not if someone else is in a file that the vba code needs in the process. for example i have a file open not related to my code and it is saying i have it open

 

[JLGWhiz]

I misunderstood what you were asking. You were apparently referring to shared files being open. See if this link helps.

VBA code to find out if a file is already open - Excel Off The Grid

If you work in a team, there are times when more than one person tries to use a file. Maybe your line manager is checking a piece of information in a

exceloffthegrid.com

 

[ItalianPlatinum]

I misunderstood what you were asking. You were apparently referring to shared files being open. See if this link helps.

VBA code to find out if a file is already open - Excel Off The Grid

If you work in a team, there are times when more than one person tries to use a file. Maybe your line manager is checking a piece of information in a

exceloffthegrid.com

so not a way to do it multiple times but nevertheless a way. I will just create two subs per the attached and call on them in my code

 

[JLGWhiz]

Sounds like a winner.
regards, JLG