Detect if an Excel Workbook is open

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

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

GetObject specifying Excel

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

lol , I was too late. I am sure Ericks example will do exactly what you require, his suggestions normally do :) Refresh helps

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

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

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.

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.  

>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.

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.

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