Solved

vba auto close files after break in code

Posted on 2010-09-08
6
502 Views
Last Modified: 2013-11-25
Hello experts,

I have a vba macro that opens 3 files but if I intervene and break/stop the code in middle of processing, the files appear to be  still be in memory in the task manager.  

I need a code sample of on how automatically close the open files if there's an error in the code or if I break the code in the middle of running a macro.

Thanks in advance for your help!
0
Comment
Question by:lcha
  • 2
  • 2
6 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33633260
Normally you would use a combined
  • Errorhandler
  • User intervention check for this
ie, if either the user halts the code - say by pressing a stop button on a form - or there is an error goto a closing routine

something like below, see the errhandler code that closes the automated workbook and excel object

Cheers

Dave

Sub SampleStuff()
Dim objExcel
Dim objWB
Set objExcel = CreateObject("excel.application")
Set objWB = objExcel.Workbooks.Open("C:\test1.xls")
   
On Error GoTo errhandler
Err.Raise 12
    
    ' do stuff
      objWB.Close False
    objExcel.Quit
    Set objExcel = Nothing
    
    Exit Sub

errhandler:
    objWB.Close False
    objExcel.Quit
    Set objExcel = Nothing
End Sub

Open in new window

0
 

Author Comment

by:lcha
ID: 33633494
thanks!   I  added this code substituting the object names you provided with the ones in my code but it doesn't seem to be working properly.

Here's some additional info.

I start out by opening to excel files.   One is an excel file and another that is a macro enabled excel file.
I run the code from the macro enabled file by clicking f8 and run the code in the macro enabled file.

As the macro runs, it opens 2 other files creates and opens 3rd output.xls file.

When I hit the "esc" key to break the code in the middle of processing, I get a popup that says the "code execution has been interrupted.    Then I click the "end" button.

After that is all done, all my files are open still. Next, I manually close all the open excel files and check the task manager.   There is still one excel process running.




Dim oeXcel as New Excel.Application (I'm not sure if declaration in my code is even needed.... it's not used anywhere else)

Err.Raise 12
    
    ' do stuff
    wb.Close False
    WB_input2.Close
    WB_input3.Close
    outputWB.Close
    oExcel.Quit
    
    Set oExcel = Nothing
    
    Exit Sub

errhandler:
    wb.Close False
    WB_input2.Close
    WB_input3.Close
    outputWB.Close
    
    oExcel.Quit
    Set oExcel = Nothing

Open in new window

0
 

Author Comment

by:lcha
ID: 33633499


Here's some additional info (See CAPs for my correction)

I start out by opening TWO excel files.   One is an excel file and another that is a macro enabled excel file.
I run the code from the macro enabled file by clicking f8 and run the code in the macro enabled file.
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 250 total points
ID: 33633536
Hi,
A couple of comements

1) My Err.Raise was to simulate an error to force the code to run the error routine. You should remove it from your code
2) >  When I hit the "esc" key to break the code in the middle of processing, I  get a popup that says the "code execution has been interrupted.    Then  I click the "end" button.
If you want to halt the code part way through you need a way to control the exit - say a VBYesNo choice in the code, if you simply crash it as above then there is no way to process an early exit.

Cheers

Dave
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 250 total points
ID: 33634400
Use:
Application.EnableCancelKey = xlErrorHandler


so that the Esc key raises a rte.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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