Solved

Unload Excel from memory using Code

Posted on 2000-04-16
12
322 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 
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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
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.

831 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