?
Solved

Detect if an Excel Workbook is open

Posted on 2005-03-30
13
Medium Priority
?
804 Views
Last Modified: 2012-05-05
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
Comment
Question by:rajesh_khater
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13662850
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
 
LVL 32

Expert Comment

by:Erick37
ID: 13662857
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
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13662866
GetObject specifying Excel
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13663246
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
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13663255
lol , I was too late. I am sure Ericks example will do exactly what you require, his suggestions normally do :) Refresh helps
0
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13663435
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
 
LVL 29

Accepted Solution

by:
leonstryker earned 750 total points
ID: 13663445
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
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13663507
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 13663544
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
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13663603
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 13665799
>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
 
LVL 32

Expert Comment

by:Erick37
ID: 13666522
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
 
LVL 12

Expert Comment

by:gbzhhu
ID: 13669799
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question