Check to see if another workbook is open in VBA

[andrewb90]

Hello all,

I'm looking to add some code to my opening procedure that will check if another workbook is open. I found some examples online, but they seem to look for a specific filename. Is there a way to look for any .xls or .xlsm file open?

Thanks!

 

[Michael M]

Maybe something like this can be adapted..

Sub MM1()
othwb = Application.WORKBOOKS.Count
If othwb >= 1 Then
ActiveWorkbook.Close
End If
End Sub

 

[Michael M]

Also have a look here

http://www.mrexcel.com/forum/excel-...l-excel-workbooks-except-active-workbook.html

 

[Macropod]

If you're looking to see whether you have any workbooks open, all it takes is a Application.Workbooks.Count test. The result will typically be 2 or more (your personal workbook plus the one you run the test from), plus any others that are open. A loop through the Workbooks collection will also return their names. If you want to test whether anyone has any of the workbooks accessible to your code open, the only way of doing that is by testing with their individual filenames. For example:

Sub Demo()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, strList As String
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.xls")
While strFile <> ""
' Does another user have the file open?
  If IsFileLocked(strFolder & "\" & strFile) = True Then
    ' Report if true
    strList = strList & vbCr & strFile
  End If
  strFile = Dir()
Wend
If strList <> "" Then MsgBox "The following Excel workbooks are in use:" & strList, vbExclamation, "Files in use"
Application.ScreenUpdating = True
End Sub

Function IsFileLocked(strFileName As String) As Boolean
  On Error Resume Next
  Open strFileName For Binary Access Read Write Lock Read Write As #1
  Close #1
  IsFileLocked = Err.Number
  Err.Clear
End Function

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

 

[andrewb90]

Awesome! I made it work, thank you!
Is it possible to save and close the other workbooks? I'm gonna put this into a vbYesNoCancel, but I'm just wondering if my code can close all other sheets upon command.

Edit: Just saw the other posts... Looking at that right now! Thanks!

 

[Macropod]

Is it possible to save and close the other workbooks? I'm gonna put this into a vbYesNoCancel, but I'm just wondering if my code can close all other sheets upon command.

If they're the user's workbooks, then yes, you can, but forcing them to save and close, or to close without saving, might be disastrous and will lose you a lot of friends in an instant. Better to have your macro gracefully exit, advising the user that it can't run while those other workbooks are open. In any event, you can't force the closure of workbooks in use by other users.

 

[andrewb90]

OK, So I'm having at least one issue...
This is trying to close my worksheet as well. However, I have Worksheet_Beforeclose code, so maybe that's whats causing it, in any case, can I have that procedure ignored when closing the other workbooks?

Private Sub test2()Dim WB As Workbook


For Each WB In Workbooks
     If Not (WB Is ActiveWorkbook) Then WB.Close
Next


End Sub

Edit: Just saw Macropod's reply... This is what I am trying to do. I'd like to give the user the option of me saving and closing their sheets or doing nothing, and letting them deal with any side effects of my vba that runs on my sheet.

Private Sub PreOpen()othwb = Application.Workbooks.Count
If othwb > 1 Then
MsgBox "many books"


Dim msg, Style, Title
msg = "It looks like you have multiple Excel files open." & vbNewLine & _
"This scheduler works best alone. Leaving your other files open may cause undesirable results." & vbNewLine _
& vbNewLine & "Would you like me to save and close these files for you?"
Style = vbYesNo + vbExclamation     ' Define buttons.
Title = "I'm one-of-a-kind!"    ' Define title.
Response = MsgBox(msg, Style, Title, Help, Ctxt)
If Response = vbNo Then    ' User chose NO.
    MyString = "No"    ' Perform some action.
    


Else    ' User chose yes.
    MyString = "Yes"    ' Perform some action.
    Dim WB As Workbook


    For Each WB In Workbooks
        If Not (WB Is ActiveWorkbook) Then _
        WB.Save
        WB.Close
Next
    
End If

 

[Macropod]

letting them deal with any side effects of my vba that runs on my sheet.

Properly written, you code shouldn't interact with any workbook it isn't meant to. If it needs to interact with one that's already open, that should only happen under clearly defined scenarios that won't interfere with whatever else the user might be doing with those workbooks. Good luck with that in any situation where you're doing anything more than reading from or writing to a workbook the user is supposed to have open for your macro's purposes.