By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,986 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,986 IT Pros & Developers. It's quick & easy.

Excel instance won't close

P: 28
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:

Expand|Select|Wrap|Line Numbers
  1. myExcelApp.ActiveWorkbook.Close
  2. 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.
Dec 19 '07 #1
Share this Question
Share on Google+
9 Replies


Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1. myExcelApp.ActiveWorkbook.Close
  2. 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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Set YourWorkSheet = Nothing
  3. Set YourWorkBook = Nothing
  4. Set YourApp = Nothing
  5.  
  6.  
Something like that, I think you need to say that:-)

Try it!
Dec 20 '07 #2

Expert 5K+
P: 8,434
Yeah, you need to close/release/whatever all of the objects you create.
Dec 20 '07 #3

P: 28
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.
Dec 20 '07 #4

Expert 5K+
P: 8,434
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.)
Dec 20 '07 #5

Dököll
Expert 100+
P: 2,364
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!
Dec 21 '07 #6

Dököll
Expert 100+
P: 2,364
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:-)

Expand|Select|Wrap|Line Numbers
  1.  
  2. With myWorkBook
  3.  
  4.           .Save
  5.  
  6.           .Close
  7. End With
  8.  
  9. myApp.Quit
  10.    Set myWorkSheet = Nothing
  11.    Set myWorkBook = Nothing
  12.    Set myApp = Nothing
  13.  
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
Dec 21 '07 #7

kadghar
Expert 100+
P: 1,295
try making the Excel.Application visible, then close it.

It's not the "right" way, but it works well.
Jan 9 '08 #8

P: 1
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
Feb 17 '08 #9

Expert 5K+
P: 8,434
TRUE FIX ...
Thanks for sharing. I'm sure it will be useful for others with similar problems.
Feb 17 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.