Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

Unload Excel from memory using Code

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
tbsgadi
Asked:
tbsgadi
  • 6
  • 2
  • 2
  • +2
1 Solution
 
criCommented:
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
 
MarineCommented:
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
 
MarineCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
criCommented:
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
 
MarineCommented:
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
 
NoggyCommented:
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
 
calacucciaCommented:
TbsGadi,

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

Calacuccia
0
 
MarineCommented:
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
 
tbsgadiAuthor Commented:
Adjusted points from 100 to 300
0
 
tbsgadiAuthor Commented:
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
 
MarineCommented:
xlApp.Quit
set xlApp = Nothing

xlApp means Excel.Application
0
 
MarineCommented:
xlBook.Close savechanges:=False
and then Quit
xlApp.Quit
Set xlApp = Nothing
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now