Improve company productivity with a Business Account.Sign Up

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

Excel not exiting properly after running VBA code

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
stmoritz
Asked:
stmoritz
  • 5
  • 3
  • 3
  • +4
5 Solutions
 
pdd1lanCommented:
one way to quite Excel entirely is using:  Shell "TASKKILL /F /IM Excel.exe", vbHide,
it will kill excel task.
0
 
SiddharthRoutCommented:
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
 
bboswellCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
patrickabCommented:
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
 
gbanikCommented:
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
 
patrickabCommented:
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
 
gbanikCommented:
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
 
patrickabCommented:
You did say that, but...

Patrick
0
 
stmoritzAuthor Commented:
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
 
stmoritzAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
 
stmoritzAuthor Commented:
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
 
stmoritzAuthor Commented:
I increased a 100 points to 400, maybe there's a solution for the erro when manually closing the workbook... thanks a lot.
0
 
gbanikCommented:
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
 
stmoritzAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 3
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now