Solved

vba auto close files after break in code

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

920 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

16 Experts available now in Live!

Get 1:1 Help Now