Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 534
  • Last Modified:

vba auto close files after break in code

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
lcha
Asked:
lcha
  • 2
  • 2
2 Solutions
 
Dave BrettCommented:
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
 
lchaAuthor Commented:
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
 
lchaAuthor Commented:


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
 
Dave BrettCommented:
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
 
Rory ArchibaldCommented:
Use:
Application.EnableCancelKey = xlErrorHandler


so that the Esc key raises a rte.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now