Solved

Excel not exiting properly after running VBA code

Posted on 2010-11-24
16
874 Views
Last Modified: 2012-08-13
I have an excel sheet that is opened every day at 7am by the Windows task scheduler.
The sheet has the following code (in the "ThisWorkbook"Code) that calls the VBA code after waiting 1 minute (this time is needed until all links are updated):

Private Sub Workbook_BeforeClose(Cancel As Boolean)

     Application.OnTime dTime, "RunVbaOnTime", , False

End Sub


Private Sub Workbook_Open()

  Application.OnTime Now + TimeValue("00:01:00"), "RunVbaOnTime"

End Sub

This here is in addition added to a Module:

Public dTime As Date
Sub RunVbaOnTime()
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "RunVbaOnTime"


Call SendDailyFxByEmailToServer


End Sub

which then calls after 1 minute the code below SendDailyFxByEmailToServer

However, somehow the application does not properly close/quit. While the sheet is closed, the code continues to send the email every 1 minute until excel is manually closed. Any help to change this code in order it works, i.e. quits excel entirely?

Sub SendDailyFxByEmailToServer()



    Application.ScreenUpdating = False

    

   

   ActiveWorkbook.Save

   

   ' Select the range of cells on the active worksheet and copy paste values

   Sheets("email").Select

   Range("FxData").Select

   Selection.Copy

   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

   

   ' Show the envelope on the ActiveWorkbook.

   ActiveWorkbook.EnvelopeVisible = True

   

   ' Set the To and Subject lines and send out msg

   With ActiveSheet.MailEnvelope

      .Item.To = "xxx@xxx.om"

      .Item.Cc = "xx@xxx.com"

      .Item.Subject = " Daily Fx Feed"

      .Item.Send

   End With

   



' closes the active workbook without saving any changes, exit application

    ActiveWorkbook.Close False

    Application.ScreenUpdating = True

    Application.Quit

    

End Sub

Open in new window

0
Comment
Question by:stmoritz
  • 5
  • 3
  • 3
  • +4
16 Comments
 
LVL 8

Expert Comment

by:pdd1lan
ID: 34204239
one way to quite Excel entirely is using:  Shell "TASKKILL /F /IM Excel.exe", vbHide,
it will kill excel task.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34204285
If you just replace your old code with this then what happens?

Please close All Excel Applications. Also before running the code, press Ctrl+Alt+Del to check if Excel is running in the background.

Private Sub Workbook_Open()
  dTime = Now + TimeValue("00:01:00")
  Application.OnTime dTime, "SendDailyFxByEmailToServer"
End Sub

'~~> In the module module

Public dTime As Date

Public Sub SendDailyFxByEmailToServer()
    Application.OnTime dTime, "RunVbaOnTime"
    
    Application.ScreenUpdating = False
    
    '~~> Select the range of cells on the active worksheet and copy paste values
    Sheets("email").Range("FxData").Copy
    
    Sheets("email").Range("FxData").PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   
    '~~> Show the envelope on the ActiveWorkbook.
    ActiveWorkbook.EnvelopeVisible = True
   
    '~~> Set the To and Subject lines and send out msg
    With ActiveSheet.MailEnvelope
      .Item.To = "xxx@xxx.om"
      .Item.Cc = "xx@xxx.com"
      .Item.Subject = " Daily Fx Feed"
      .Item.Send
    End With
   

    '~~> closes the active workbook without saving any changes, exit application
    ActiveWorkbook.EnvelopeVisible = False
    ActiveWorkbook.Close savechanges:=False
    Application.ScreenUpdating = True
    
    Application.OnTime dTime, "RunVbaOnTime", , False
    
    Application.Quit
End Sub

Open in new window

0
 
LVL 5

Assisted Solution

by:bboswell
bboswell earned 150 total points
ID: 34204288
From the code you have you are closing the Workbook before it can read the code to close the application.

Replace the code as show in code window.

'Replace the code 

' closes the active workbook without saving any changes, exit application

    ActiveWorkbook.Close False

    Application.ScreenUpdating = True

    Application.Quit

'With the below code.

     Application.DisplayAlerts = False

     Application.Quit

Open in new window

0
 
LVL 45

Expert Comment

by:patrickab
ID: 34204479
stmoritz,

This routine:

Public dTime As Date
Sub RunVbaOnTime()
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "RunVbaOnTime"
Call SendDailyFxByEmailToServer
End Sub

keeps on calling itself before it runs SendDailyFxByEmailToServer. Perhaps changing it to this might solve the problem:

Public dTime As Date
Sub RunVbaOnTime()
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "SendDailyFxByEmailToServer"
End Sub

Patrick
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34204600
Here is the solution for you
http://www.mrexcel.com/forum/showthread.php?t=46586

Do understand that "Application.OnTime" has the last parameter as "Schedule". In order to exit cleanly, that parameter has to be set to "false". Also do remember to use the SAME TIME to deactivate the OnTime (see discussion above).
0
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 50 total points
ID: 34204654
Perhaps gbanik's comment changes the sub to:

Public dTime As Date
Sub RunVbaOnTime()
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "SendDailyFxByEmailToServer"
Application.OnTime EarliestTime:=Now+ TimeValue("00:0:05"), Procedure:="SendDailyFxByEmailToServer",Schedule:=False
End Sub
0
 
LVL 13

Assisted Solution

by:gbanik
gbanik earned 150 total points
ID: 34204875
Sorry @patrickab... your code involving NOW is not going to work. We have to cancel the "OnTime" using the SAME time that is used to set it.

ie. if we use
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "SendDailyFxByEmailToServer"
then on Workbook_BeforeClose event
Application.OnTime dTime, "SendDailyFxByEmailToServer", Schedule:=False

dTime being a global variable
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34207083
You did say that, but...

Patrick
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:stmoritz
ID: 34209984
seems i'm getting closer and moving forward thanks to your help. unfortunately, i tried the suggestions that did not work first... at least the solution gbanik/patrickab avoids that the vba code is run every minute without stopping... now i'm checking the suggestions to close excel entirely...



0
 

Author Comment

by:stmoritz
ID: 34210012
everything works fine now and closes properly.
last issue:

if workbook closed manually before vba runs and closes it, the run time error 1004 "Method 'OnTime' of object'_Application' failed...

any way to fix this? (although it's only minor, just nice to have, no need to have...)
0
 
LVL 23

Expert Comment

by:Michael74
ID: 34210287
stmoritz

You just need to add some error handling.

eg

On error goto EarlyExit

then have a section at the end of your code to handle an early exit

eg

EarlyExit:
   Application.OnTime dTime, "SendDailyFxByEmailToServer", Schedule:=False

Just remember that the early exit code will run if the code reaches this point so you need to use exit sub before this code if you do not want it to run
Have a look at this link for info on VBA error handling
http://www.cpearson.com/excel/ErrorHandling.htm

0
 

Author Comment

by:stmoritz
ID: 34233417
Hi Michael. The error handler is great and fine, however it contains the same statement that causes the error when exiting, so somehow something is wrong with

Application.OnTime dTime, "SendDailyFxByEmailToServer", Schedule:=False

as this seems to cause the error when manually closing the workbook...
0
 

Author Comment

by:stmoritz
ID: 34279084
I increased a 100 points to 400, maybe there's a solution for the erro when manually closing the workbook... thanks a lot.
0
 
LVL 13

Assisted Solution

by:gbanik
gbanik earned 150 total points
ID: 34284358
Just add "On Error resume next' before the statement and ignore it... sometimes thats the way to do it.... so

On Error Resume Next
Application.OnTime dTime, "SendDailyFxByEmailToServer", Schedule:=False

Just confirm whether the Excel Object disappears from the Task Bar everytime (try couple of times). If yes... u r good to go!
0
 

Author Comment

by:stmoritz
ID: 34326971
Sorry... seems I am not rally getting it... so this is currently the code in "ThisWorkbook" (not Module). I need to change it here, right?

so instead of "On Error GoTo EarlyExit" I change it to "on Error Resume Next"??? but then I don't need early exit right? thanks for the help!
Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error GoTo EarlyExit

    Application.OnTime dTime, "SendDailyFxByEmailToServer", Schedule:=False

Exit Sub

EarlyExit:

   Application.OnTime dTime, "SendDailyFxByEmailToServer", Schedule:=False

End Sub



Private Sub Workbook_Open()

On Error GoTo EarlyExit

    Application.OnTime Now + TimeValue("00:01:00"), "SendDailyFxByEmailToServer"

Exit Sub

EarlyExit:

   Application.OnTime dTime, "SendDailyFxByEmailToServer", Schedule:=False

End Sub

Open in new window

0
 
LVL 23

Accepted Solution

by:
Michael74 earned 50 total points
ID: 34328046
St Moritz

Yes you are correct. When I posted my comment I assumed it was part of a larger process. For the code you have posted gbanik's solution is best, that is you tell excel just to ignore the error and continue, which in this case is to just exit the application.
Michael




Private Sub Workbook_BeforeClose(Cancel As Boolean)

    On Error resume next

    Application.OnTime dTime, "SendDailyFxByEmailToServer", Schedule:=False

End Sub



Private Sub Workbook_Open()

    On Error Resume Next

    Application.OnTime Now + TimeValue("00:01:00"), "SendDailyFxByEmailToServer"

End Sub

Open in new window

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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

21 Experts available now in Live!

Get 1:1 Help Now