If Excel open then close

  • Thread starter Thread starter Markus
  • Start date Start date
M

Markus

Hallo

Is there a way to check when clicking on a button in my form that it check
if a specific Excel document is open if yes then close that document only? I
know there is way to close Excel but that closes all Excel documents open but
I just want the specific to be closed and not all documents

Thank you
Markus
 
Markus said:
Hallo

Is there a way to check when clicking on a button in my form that it check
if a specific Excel document is open if yes then close that document only?
I
know there is way to close Excel but that closes all Excel documents open
but
I just want the specific to be closed and not all documents

Thank you
Markus
Click to expand...

Something like this should do it (untested):

If IsExcelOpen = True Then
Dim oExcelApp As Excel.Application
Set oExcelApp = GetObject(, "Excel.Application")
For Each objWkb In oExcelApp.Workbooks
If objWkb.Name = "MyWorkbook.xls" Then
objWkb.Save
objWkb.Close
End If
Next objWkb
Set oExcelApp = Nothing
Else
Exit Sub
End If
End If

The function being called is this:

Function IsExcelOpen() As Boolean

Dim oExcelApp As Excel.Application

On Error Resume Next

'try to access an instance of Excel that is running
Set oExcelApp = GetObject(, "Excel.Application")

'if it didn't work then Excel isn't open
If oExcelApp Is Nothing Then
IsExcelOpen = False
Else
IsExcelOpen = True
End If

End Function

*HOWEVER* it sounds like your Excel file can be opened by others and this
may cause your code to fail if it happens whilst your code's running.

Keith.
www.keithwilby.co.uk
 
Back
Top