Hidden copy of Excel

[GrannyM]

I have a word macro that opens an xml file in Excel, then saves it as a xls
file that the word macro pulls information from. Everything works great
unless for some reason the user aborts the macro before it has closed the xls
file. Then it leaves a hidden copy of excel with this xls file open. So the
user cannot rerun the macro until they either reboot, or ctr+alt+delete and
end the hidden copy of excel (which I really do not want them to do!). I'm
trying to write an error trap so that if this happens, the word macro can
close down any existing hidden copies of excel, but so far I haven't been
able to figure out how.

My code for opening the file is:

Set myExcel = GetObject(, "Excel.application")

'Check to see if File1.xls is already open
For Each w In myExcel.Workbooks
If LCase(myExcel.ActiveWorkbook.FullName) = "i:\file1.xls" Then
myExcel.ActiveWorkbook.Close
Exit For
End If
Next w

'Open Excel File
OpenExcel:
myExcel.Application.DisplayAlerts = False
myExcel.Workbooks.OpenXML FileName:=FileXML$,
LoadOption:=xlXmlLoadImportToList
myExcel.ActiveSheet.ListObjects("List1").Unlist
myExcel.ActiveWorkbook.SaveAs FileName:="i:\File1.XLS"
Set myXML = myExcel.ActiveWorkbook
myExcel.Application.DisplayAlerts = True


My Error code is below - the "ElseIf Err.Number = 1004 Then" piece is the
piece that does not work. Instead of finding the hidden copy of excel, it
seems to open a new hidden copy without any worksheets. (I inserted
"myExcel.visible = True" to see if I could figure out what was happening)

ErrHandle:
If Err.Number = errExcelNotRunning Then
Set myExcel = CreateObject("Excel.application")
CloseXL = True
Resume Next
ElseIf Err.Number = 1004 Then '(File1.xls is open in hidden Excel)
Set myExcel = GetObject(, "Excel.application")
If myExcel.Visible = False Then
myExcel.Quit
GoTo OpenExcel
End If

Resume
Else
MsgBox Err.Number & ": " & Err.Description
Abort = True
End If

Any ideas on how I can find this hidden copy of Excel that has been left
open and close it?

Thanks

 

[Helmut Weber]

Hi GrannyM,

maybe setting the Excel-object
in question to nothing might help.

myExcel.Quit
set MyExcel = nothing
--

Greetings from Bavaria, Germany

Helmut Weber

Vista Small Business, Office XP

 

[GrannyM]

Sorry, that didn't work. It still doesn't always find the correct incident
of Excel. I can get by it by changing my filename to a variable and
incrementing the variable if the first one is not accessible. It will clear
out when the user does their nightly reboot. But I just don't like leaving
something hanging like that.

 

[PhilGSmith]

Hi,


Have you had any luck yet with the resolution of this problem?

We have a similar issue with one user. She can sometimes open spreadsheets
she has been using. Other times, she clicks on the file and Excel opens, but
it does not display anything! The Excel window shows the Excel menu with a
transparent area below where the spreadsheet should be.

I have found that by using Task Manager, there is a hidden Excel copy (not
showing in applications, just in processes) and by ending this Excel process,
normailty returns and she can carry on working.

We have re-installed Office on this workstation. Other users who have
copies of this same template can use it without a problem.

 

[GrannyM]

No, sorry, I was not able to solve this issue. I still think there should be
some way of spinning through the instances of Excel that are open (hidden or
not) to look for the file and close it, but everything I've tried through
code will only find the most recent instance of Excel.

I ended up using a variable for the filename and if the filename exists and
is locked, it increments the variable name (File1.doc, File2.doc) and
continues on. If the macro completes, it deletes this temporary file at the
end of the macro, so the only time this is an issue is when the user has
aborted the macro with the temp file still open. And next time they run it
after a reboot, the hidden version of Excel has been closed, so the first
file isn't locked, so it will overwrite it and then delete it when done.
That's the only thing I could come up with that would not require the user to
reboot after they've aborted the macro, or build up several temp files. (I
don't want our users using Task Manager to end processes!)

If anyone has a better solution, please let me know.

 

[PhilGSmith]

Hi, I don't know if it will help your situation, but a similare problem I
found was resolved by going into <Tools> <Option> <General> and checking
"ignore opther applications". Seems a bit random, but worth a try.

Phil

 

[PhilGSmith]

Tried this with my user. Whilst it seemed to stop the original problem it has
prevented more than one session in Excel being run. SO no win there!