Solved

Unload Excel from memory using Code

Posted on 2000-04-16
12
319 Views
Last Modified: 2008-01-09
Ive got an access procedure that loads Excel into memory,and stores it.
When I exit Access I want also to unload the Excel...How???
0
Comment
Question by:tbsgadi
  • 6
  • 2
  • 2
  • +2
12 Comments
 
LVL 13

Expert Comment

by:cri
ID: 2721109
This is "by (Microsoft) design"

ACC: OLE MS Excel, Word Do Not Close When Form Is Closed
http://support.microsoft.com/support/kb/articles/Q96/4/65.ASP

Perhaps a work-around can be devised. Please paste how you invoke Excel from Access.

0
 
LVL 6

Expert Comment

by:Marine
ID: 2721287
Did you use Quit Method of Excel.Application object ? I am new to this myself just started a day ago :) but read that to unload excel from memory you need to use Quit Method.
If you had declaration like this Then
Set xlApp = CreateObject("Excel.Application")

just issue xlApp.Quit
Hope this helps.
0
 
LVL 6

Expert Comment

by:Marine
ID: 2721403
I forgot to add that you might want to close the work book because it will promt you if you haven't set that method. Here it is.

xlBook.Close savechanges:=False
and then Quit
xlApp.Quit
0
 
LVL 13

Expert Comment

by:cri
ID: 2722300
Marine, please do not post as answer unless are sure you have the solution. As the calling method is not known your solution might or not apply. As most experts will not bother looking into a locked question, you diminish tbsgadi's chances to get the best answer by premature answering.
0
 
LVL 6

Expert Comment

by:Marine
ID: 2722332
I'll withdraw but i was pretty sure that it was the right answer. I never lock questions on purpose. I let the person pick the answer that best suits him.
0
 
LVL 4

Expert Comment

by:Noggy
ID: 2724235
Marine's comment is correct. However, it does not go quite far enough as I expect that you may be experiencing problems when your procedure/function is being exited prematurely:

Sub YourProc()

    Dim xlApp As Excel.Application'You may even have "...As New Excel.App..."

    On Error Goto YourProc_Error
   
    'Only use one of the following if you don't have "New Excel.Application" anywhere:
    'Set xlApp=GetObject(,"Excel.Application")
    'Set xlApp=CreateObject(,"Excel.Application")

    If ConditionToExitPrematurely = True Then
        Error 3000
    End If

YourProc_Exit:
    On Error Resume Next
    xlApp.Quit
    Set xlApp = Nothing
   
YourProc_Error:
    If Err.Number <> 3000 Then
        'Put your error reporting procedure here if you have one
    End If
    Resume YourProc_Exit
End Sub

If you use the structure similar to the above, your Exit routine will always be executed ensuring that your objects (and references to them) are safely removed. Basically, NEVER use "Exit Sub".

Always keep your app objects local to the function or sub that you're going to be using it in. Otherwise, it can get messy trying to clear up whether the app's still in use or not. So, for your case, I would already have Excel closed before the user even wants to quit Access.

There is more information on orphan objects etc. in the following article:
Advanced Class Modules - http://www.microsoft.com/officedev/articles/AdvClMod.htm (Start reading from the section "Avoiding Reference Problems")
You may also find other articles useful at the following page (they are a basic "must" for developers): http://www.microsoft.com/officedev/o-a&sa.htm#automate
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 17

Expert Comment

by:calacuccia
ID: 2854194
TbsGadi,

Please settle this question, grade it to the person who helped you best !

Calacuccia
0
 
LVL 6

Expert Comment

by:Marine
ID: 2854210
i thought i was getting my first points in this section i guess i was wrong :( . I think other person deserves points he explained more then i did. I was only learning hehe.
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 2907416
Adjusted points from 100 to 300
0
 
LVL 46

Author Comment

by:tbsgadi
ID: 2907417
Hi I'm sorry but so far none of the answers help me.Take it as given that I have Excel in memory..doesn't matter how It got there.How do I release it??
0
 
LVL 6

Accepted Solution

by:
Marine earned 300 total points
ID: 2907445
xlApp.Quit
set xlApp = Nothing

xlApp means Excel.Application
0
 
LVL 6

Expert Comment

by:Marine
ID: 2907449
xlBook.Close savechanges:=False
and then Quit
xlApp.Quit
Set xlApp = Nothing
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now