Solved

vba auto close files after break in code

Posted on 2010-09-08
6
513 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

623 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