Check for open Excel workbook and then close it and Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use a program that imports into Excel that requires that the workbook is
closed prior to the import.

How do you check to see if an Excel workbook is open, and then close the
workbook?

How do you then look to see if Excel is still running and then close it
(Excel.exe)?

I already know how to open an instance of Excel, use it, and then close it,
but I have not figured out how to check for an already open instance of a
workbook and/or Excel.

Thanks,
 
Here is a function that you can call to identify if a file is open:

' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error will occur because there is
' some other problem accessing the file.

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum

' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False

' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True

' Another error occurred.
Case Else
Error errnum
End Select
End Function


Here is how you can test if Excel is already open:

Dim objExcel As Object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
' Excel is not open
Err.Clear
Set objExcel = CreateObject("Excel.Application")
Else
' Excel is open and you now have an object
' reference to it in objExcel
End If
 
Ken:

First, thanks for the reply. I see you helping people all the time.

The code works to identify an open file or an instance of Excel, but I
cannot seem to get the code to close the file and Excel. I have tried moving
the "Close filenum" to various places in the code, tried "Close filename",
etc. What am I missing?

--
Thanks,

Randy


Ken Snell said:
Here is a function that you can call to identify if a file is open:

' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error will occur because there is
' some other problem accessing the file.

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum

' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False

' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True

' Another error occurred.
Case Else
Error errnum
End Select
End Function


Here is how you can test if Excel is already open:

Dim objExcel As Object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
' Excel is not open
Err.Clear
Set objExcel = CreateObject("Excel.Application")
Else
' Excel is open and you now have an object
' reference to it in objExcel
End If
Click to expand...
 
Sorry... the code I posted doesn't include closing down the file or EXCEL.
Any reason why you need to close EXCEL instead of just using the instance
that is already running? If you need to shut it down, you can use the Quit
method once you set a reference to the EXCEL application:
objExcel.Quit

To close the file once you have an object pointing to EXCEL application,
just use (example)
objExcel.Workbooks("FileName").Close SaveChanges:=True


Additional info: there is code at http://www.mvps.org/access/api/api0007.htm
for a function that also will tell you if an application is running.

--

Ken Snell
<MS ACCESS MVP>


Randy Wayne said:
Ken:

First, thanks for the reply. I see you helping people all the time.

The code works to identify an open file or an instance of Excel, but I
cannot seem to get the code to close the file and Excel. I have tried
moving
the "Close filenum" to various places in the code, tried "Close filename",
etc. What am I missing?

--
Thanks,

Randy
Click to expand...
 
Ken:

Thanks!! It works perfectly.

The answer to your question of "Why not use the instance that is running" is
that one of our third party software programs opens a new instance of Excel
each time it downloads a file. Over time, the file server will end up with
several instances of Excel open, which uses up resources and sometimes locks
up the server. I want to check for this and close the files and then close
Excel.

Thanks again!

Randy
 
Ken

I have separately posted this query in case you're not online to reply!

I have a database that imports an excel worksheet as part of a vba function.

I have used your posting to get code that checks if the file is open before
I start the import but my query is slightly different, hence the new post
that I also put up.

The excel file I'm importing is on a network and my query is whether I can
retrieve the name of the person who has the file open as the company is quite
big and I'd like to be able to got to the person's PC if they've left the
file open on their PC and/or are not responding to a blanket email to a group
of people to shut the excel file. I'd prefer to go to the person's PC and
shut the file or get them to shut the file rather than close the file on them
and potentially cause their work to be lost.

If I try to open the excel file ordinarily (through Excel and not as part of
a VBA function) and the file is open, I get a message saying that the file is
use and it says the name of the person who has the file open. It gives an
option to open a copy of the file as read-only. Would it be possible to
retrieve the information (i.e name) on the user that has the file open from
this dialog pane/error message as part of the code you have written in your
post?

I presume this error message/dialog pane is the same error that you are
checking for in the code (error num 70) and maybe the code could be altered
to provide the name of the person that has the file open. Is this possible?


--
Andrew


Ken Snell said:
Here is a function that you can call to identify if a file is open:

' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error will occur because there is
' some other problem accessing the file.

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum

' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False

' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True

' Another error occurred.
Case Else
Error errnum
End Select
End Function


Here is how you can test if Excel is already open:

Dim objExcel As Object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
' Excel is not open
Err.Clear
Set objExcel = CreateObject("Excel.Application")
Else
' Excel is open and you now have an object
' reference to it in objExcel
End If
Click to expand...
 
Back
Top