Solved

vba auto close files after break in code

Posted on 2010-09-08
6
498 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

770 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