• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 809
  • Last Modified:

Detect if an Excel Workbook is open

What is the best / fastest way to check if an Excel workbook is already open by a user, and if it is open, then force the user to close it ?
0
rajesh_khater
Asked:
rajesh_khater
  • 6
  • 3
  • 2
  • +1
1 Solution
 
gbzhhuCommented:
Use GetObject in a loop until you get an error and inside the loop check all books in that instance against the book you want to close.  If found then close it
0
 
Erick37Commented:
Dim oWB As Object

On Error Resume Next

'enter the path of the workbook
Set oWB = GetObject("c:\new.xls")

If Not oWB Is Nothing Then
    Debug.Print oWB.Name
    'it's open, close it
    oWB.Close
Else
    'not open
    Debug.Print "Not open"
End If
0
 
gbzhhuCommented:
GetObject specifying Excel
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
gecko_au2003Commented:
make a reference to the excel object in visual basic and use the workbook property and check to see if it is open. If that does not work then you can use the findwindow and getwindow API's to check to see if it is open and close it.

http://vbnet.mvps.org/index.html?code/system/findwindowlikesimple.htm

There is an example, There is also another example in my previous Answered questions I will post that URL / PAQ here in a while
0
 
gecko_au2003Commented:
lol , I was too late. I am sure Ericks example will do exactly what you require, his suggestions normally do :) Refresh helps
0
 
gbzhhuCommented:
I assumed we only have a book name.

Erick's would close only one if that book was open in more than 1 Excel instance (I think).  also if Excel instance was open but the book we wanted is not open, it will open then close

My methd will be a lot slower than Erick's but it maybe what rajesh wants - depends what he is trying to achieve

Plus Erick has so many points he let a poor soul like me get the points  lollllll ;-)
0
 
leonstrykerCommented:
Assuming you mean a specific workbook and not just Excel.  you can try using this function.  It was made specifically for Excel VBA, but you could easily adapt it to work from VB.

Public Function IsOpen(ByVal strWorkbook As String) As Boolean
'/ Tells if a given workbook is open
Dim wbk As Workbook, intResponse As Integer
    IsOpen = True
    For Each wbk In Workbooks
        If wbk.Name = strWorkbook Then
            intResponse = MsgBox("File " & wbk.Name & " is already opened." & Chr(13) & Chr(13) & _
            "Click 'Yes' to close the file and Save changes," & Chr(13) & _
            "Click 'No' to close the file Without saving any changes" & Chr(13) & _
            "Click 'Cancel' to quit the application", vbYesNoCancel, "Opening files")
            Select Case intResponse
                Case vbYes
                    Workbooks(wbk.Name).Close True
                Case vbNo
                    Workbooks(wbk.Name).Close False
                Case Else
                    GoTo ERROR_FUNCTION
            End Select
        End If
    Next
EXIT_FUNCTION:
    Exit Function
ERROR_FUNCTION:
    IsOpen = False
    Debug.Print Err.Description
    Err.Clear
    GoTo EXIT_FUNCTION
End Function

Leon
0
 
gbzhhuCommented:
leon, on the VB end we will have to use GetObject (as we don't want to create a new instance of Excel) which leads back to what Erick and I were suggesting
0
 
leonstrykerCommented:
Sure, if Excel is not open then neither is the workbook.  If it is then use the loop and that is the function for it.
0
 
gbzhhuCommented:
I agree with the function and it is pretty neatly coded but what if several instances of Excel are open and the book is in one of them.  
0
 
leonstrykerCommented:
>if several instances of Excel are open and the book is in one of them

If you want to test for that just try opening the file and saving it.  If it is already open, you will get an error.
0
 
Erick37Commented:
I think using GetObject as I did will actually launch Excel if the sheet is not open.  I believe the best approach would be to do as others have suggested, and loop the workbooks by attaching to an existing instance of Excel.
0
 
gbzhhuCommented:
Ok, I am with Erick the GetObject route because if I try opening the file and saving it as a means of testing if it is already open or not, when I get an error (file is already open) then how do I determine which instance of Excel the workbook is in? looks like I am gonna have to look every instance open

I think rajesh_khater has several suggestions here put no input as far as I can see as to his preference, I am going to retire until he/she makes some comment
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now