Solved

Excel not exiting properly after running VBA code

Posted on 2010-11-24
16
913 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
[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
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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
 

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:Michael Fowler
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:
Michael Fowler 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

726 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