Excel instance won't close

[fahadqureshi]

I am running two visual basic modules in Access and keep coming across an annoying problem.
Both the vb modules transfer an excel spreadsheet to an access database but for some reason after the module has executed the excel spread sheets remain open in memory.
So if for some reason i try to go open the spreadsheets in excel i get a message saying that the spreadsheet is locked and can only be open in read-only mode.
When i press Ctrl+Alt+Delete, open task manager, and go to processes i can see two EXCEL.EXE process's running.
Once I end the process i can open the spread sheet and use it but otherwise it stays locked.

I researched online and came across the following code:

myExcelApp.ActiveWorkbook.Close
myExcelApp.Application.Quit

I tried using this code and it solved half my problem. So now what its doing is closing one of the EXCEL.EXE processes but one still remains open.

What kind of code can I use to close/exit all spreadsheets that might be open in memory.

[Dököll]

I am running two visual basic modules in Access and keep coming across an annoying problem.
Both the vb modules transfer an excel spreadsheet to an access database but for some reason after the module has executed the excel spread sheets remain open in memory.
So if for some reason i try to go open the spreadsheets in excel i get a message saying that the spreadsheet is locked and can only be open in read-only mode.
When i press Ctrl+Alt+Delete, open task manager, and go to processes i can see two EXCEL.EXE process's running.
Once I end the process i can open the spread sheet and use it but otherwise it stays locked.

I researched online and came across the following code:

myExcelApp.ActiveWorkbook.Close
myExcelApp.Application.Quit

I tried using this code and it solved half my problem. So now what its doing is closing one of the EXCEL.EXE processes but one still remains open.

What kind of code can I use to close/exit all spreadsheets that might be open in memory.

Hello Hello!

It looks like you are missing:

 
Set YourWorkSheet = Nothing
Set YourWorkBook = Nothing
Set YourApp = Nothing
 
 

Something like that, I think you need to say that:-)

Try it!

[Killer42]

Yeah, you need to close/release/whatever all of the objects you create.

[fahadqureshi]

I tried that and it's closing only one of the EXCEL.EXE 's while one remains open.

Also, is this the correct command or am I doing this incorrectly?

Set myExcelAppWorksheets = Nothing
Set myExcelAppWorkBooks = Nothing
Set myExcelApp = Nothing

Instead of YourWorkSheet and YourWorkBook I would use myExcelApp.

[Killer42]

As far as I know it looks alright. However, as a general rule aren't you normally supposed to Close each object before setting your reference to Nothing?

(I don't work a lot with objects, so don't assume I know what I'm talking about.)

[Dököll]

Great point, I dunno objects:-)

...perhaps close is also needed. I would try them both; it looks like it is working. I'll check the code to see if anything's a miss there.

Does it give your the same treatment though? I am not sure if you said that...

In a bit!

[Dököll]

Great point, I dunno objects:-)

...perhaps close is also needed. I would try them both; it looks like it is working. I'll check the code to see if anything's a miss there.

Does it give your the same treatment though? I am not sure if you said that...

In a bit!

Please fogive me if I seem standoffish with the code, I have not worked too much with excel. Try nonetheless, and I will also give it a whirl and see if anything pops up. I must admin please I am not sure what the resluts will be when you run it. Let's give it a go and see:-)

 
With myWorkBook
 
          .Save
 
          .Close
End With
 
myApp.Quit
   Set myWorkSheet = Nothing
   Set myWorkBook = Nothing
   Set myApp = Nothing
 

Thing is I messed it up so much that I do not what going on with it.
I probably made a typo up there too:-)

Dököll

[kadghar]

try making the Excel.Application visible, then close it.

It's not the "right" way, but it works well.

[silentv8]

TRUE FIX ... or rather ... THIS IS HOW IT SHOULD BE


dim appExcel as Excel.Application <- this is variable declaration only
.
. declare whatever you need
.

Set appExcel = NEW Excel.Application <--- this line declares appExcel as New Object to be run in a new instance rather than object to be run within MS ACCESS instance.
.
. set whatever you need (including worbook and worksheet)
.


Try eliminating SET statment of appExcel and run your program .. See task list
Try setting appExcel without NEW declaration and run your program ... See task list
Try setting appExcel with NEW declaration and run your program ... See task list

.
. do your stuff here
.

workbook.save
workbook.close

set worksheet=nothing
set workbook=nothing
appExcel.quit
set appExcel=nothing

It took me about 1 day to figure things out ( as I missed NEW declaration in the set statement... so I hope this would benefit someone somewhere ).

excel should close with above standard closing statements

if you run recursive function/procedure with NEW instance of appExcel ... you need to close each instance. I suggest you DECLARE and set appExcel OUTSIDE the RECURSIVE procedure/function so that you only have 1 instance of appExcel running and pass the object as a parameter (if possible-i havent tried it but i think it should work)...using standard VBA programming it will be hard to close each instance without an object identifier .

if you declare the appExcel outside any function or procedure (public declaration), you may have EXCEL lingering until you close MS ACCESS.

SilentV8

[Killer42]

TRUE FIX ...

Thanks for sharing. I'm sure it will be useful for others with similar problems.