|
Application.Quit Does not Close the Application
I'm in the process of automating a workbook for an engineer at our
site. He has a macro written to run some calculations in Excel using solver.xla and send these values to another server. As it stands now the macro is accessed by a button on one of the worksheets in the workbook. What I have been asked to do is setup a scheduled task that runs every three hours. This task will open the workbook, launch the macro that writes data to the other server, and then close the workbook and quit Excel. I have setup the following code on the Workbook_Open sub: Private Sub Workbook_Open() 'Call Module5.Run_solver Application.DisplayAlerts = False ActiveWorkbook.Close Application.Quit End Sub This code works as stated except that the application does not close. Excel stays open but the workbook closes. I have tried to reverse the order of the ActiveWorkbook.Close and Application.Quit but the result is the same. Module5.Run_solver is the macro that was designed by the engineer. Below is the code for the macro: Sub Run_solver() ' ' Run_solver Macro ' Macro recorded 5/22/2008 by ****** ******* ' Application.Run "Solver.xla!Auto_Open" SolverReset 'SolverOk SetCell:="$O$7", MaxMinVal:=3, ValueOf:="0", ByChange:="$M$9" SolverOptions Precision:=0.01, Convergence:=0.1 SolverOk SetCell:="$O$7", MaxMinVal:=3, ValueOf:="0", ByChange:="$M $9" SolverSolve True SolverReset 'SolverOk SetCell:="$O$8", MaxMinVal:=3, ValueOf:="0", ByChange:="$L$9" SolverOptions Precision:=0.01, Convergence:=0.1 SolverOk SetCell:="$O$8", MaxMinVal:=3, ValueOf:="0", ByChange:="$L $9" SolverSolve True SolverReset 'SolverOk SetCell:="$O$16", MaxMinVal:=3, ValueOf:="0", ByChange:="$M$18" SolverOptions Precision:=0.01, Convergence:=0.1 SolverOk SetCell:="$O$16", MaxMinVal:=3, ValueOf:="0", ByChange:="$M$18" SolverSolve True SolverReset 'SolverOk SetCell:="$O$17", MaxMinVal:=3, ValueOf:="0", ByChange:="$L$18" SolverOptions Precision:=0.01, Convergence:=0.1 SolverOk SetCell:="$O$17", MaxMinVal:=3, ValueOf:="0", ByChange:="$L$18" SolverSolve True macroResult = Application.Run("PIPutValx", _ Application.ActiveSheet.Range("M37").Text, _ Application.ActiveSheet.Range("S12"), _ Application.ActiveSheet.Range("H2").Value, , _ Application.ActiveSheet.Range("N37")) macroResult = Application.Run("PIPutValx", _ Application.ActiveSheet.Range("M38").Text, _ Application.ActiveSheet.Range("R12"), _ Application.ActiveSheet.Range("H2").Value, , _ Application.ActiveSheet.Range("N38")) macroResult = Application.Run("PIPutValx", _ Application.ActiveSheet.Range("M39").Text, _ Application.ActiveSheet.Range("L14"), _ Application.ActiveSheet.Range("H2").Value, , _ Application.ActiveSheet.Range("N39")) macroResult = Application.Run("PIPutValx", _ Application.ActiveSheet.Range("M40").Text, _ Application.ActiveSheet.Range("L23"), _ Application.ActiveSheet.Range("H2").Value, , _ Application.ActiveSheet.Range("N40")) macroResult = Application.Run("PIPutValx", _ Application.ActiveSheet.Range("M41").Text, _ Application.ActiveSheet.Range("M14"), _ Application.ActiveSheet.Range("H2").Value, , _ Application.ActiveSheet.Range("N41")) macroResult = Application.Run("PIPutValx", _ Application.ActiveSheet.Range("M42").Text, _ Application.ActiveSheet.Range("M23"), _ Application.ActiveSheet.Range("H2").Value, , _ Application.ActiveSheet.Range("N42")) End Sub I've seached many resources online and I've tried many different things but I cannot seem to figure out why Excel will not respond to the Application.Quit command. Is it possible that I need to close solver.xla before Excel can be completely closed out? Also, I am running Excel 2003 with SP3 and all Microsoft security updates and patches. Any help on this at all would be greatly appreciated. Thanks J A |
Application.Quit Does not Close the Application
It doesn't work because you are closing the workbook before it can get to
Application.Quit. You could try to deleting the ActiveWorkbook.Close line and see if that helps you. Hope this helps! If so, let me know or just click "Yes" below. -- Cheers, Ryan " wrote: I'm in the process of automating a workbook for an engineer at our site. He has a macro written to run some calculations in Excel using solver.xla and send these values to another server. As it stands now the macro is accessed by a button on one of the worksheets in the workbook. What I have been asked to do is setup a scheduled task that runs every three hours. This task will open the workbook, launch the macro that writes data to the other server, and then close the workbook and quit Excel. I have setup the following code on the Workbook_Open sub: Private Sub Workbook_Open() 'Call Module5.Run_solver Application.DisplayAlerts = False ActiveWorkbook.Close Application.Quit End Sub This code works as stated except that the application does not close. Excel stays open but the workbook closes. I have tried to reverse the order of the ActiveWorkbook.Close and Application.Quit but the result is the same. Module5.Run_solver is the macro that was designed by the engineer. Below is the code for the macro: Sub Run_solver() ' ' Run_solver Macro ' Macro recorded 5/22/2008 by ****** ******* ' Application.Run "Solver.xla!Auto_Open" SolverReset 'SolverOk SetCell:="$O$7", MaxMinVal:=3, ValueOf:="0", ByChange:="$M$9" SolverOptions Precision:=0.01, Convergence:=0.1 SolverOk SetCell:="$O$7", MaxMinVal:=3, ValueOf:="0", ByChange:="$M $9" SolverSolve True SolverReset 'SolverOk SetCell:="$O$8", MaxMinVal:=3, ValueOf:="0", ByChange:="$L$9" SolverOptions Precision:=0.01, Convergence:=0.1 SolverOk SetCell:="$O$8", MaxMinVal:=3, ValueOf:="0", ByChange:="$L $9" SolverSolve True SolverReset 'SolverOk SetCell:="$O$16", MaxMinVal:=3, ValueOf:="0", ByChange:="$M$18" SolverOptions Precision:=0.01, Convergence:=0.1 SolverOk SetCell:="$O$16", MaxMinVal:=3, ValueOf:="0", ByChange:="$M$18" SolverSolve True SolverReset 'SolverOk SetCell:="$O$17", MaxMinVal:=3, ValueOf:="0", ByChange:="$L$18" SolverOptions Precision:=0.01, Convergence:=0.1 SolverOk SetCell:="$O$17", MaxMinVal:=3, ValueOf:="0", ByChange:="$L$18" SolverSolve True macroResult = Application.Run("PIPutValx", _ Application.ActiveSheet.Range("M37").Text, _ Application.ActiveSheet.Range("S12"), _ Application.ActiveSheet.Range("H2").Value, , _ Application.ActiveSheet.Range("N37")) macroResult = Application.Run("PIPutValx", _ Application.ActiveSheet.Range("M38").Text, _ Application.ActiveSheet.Range("R12"), _ Application.ActiveSheet.Range("H2").Value, , _ Application.ActiveSheet.Range("N38")) macroResult = Application.Run("PIPutValx", _ Application.ActiveSheet.Range("M39").Text, _ Application.ActiveSheet.Range("L14"), _ Application.ActiveSheet.Range("H2").Value, , _ Application.ActiveSheet.Range("N39")) macroResult = Application.Run("PIPutValx", _ Application.ActiveSheet.Range("M40").Text, _ Application.ActiveSheet.Range("L23"), _ Application.ActiveSheet.Range("H2").Value, , _ Application.ActiveSheet.Range("N40")) macroResult = Application.Run("PIPutValx", _ Application.ActiveSheet.Range("M41").Text, _ Application.ActiveSheet.Range("M14"), _ Application.ActiveSheet.Range("H2").Value, , _ Application.ActiveSheet.Range("N41")) macroResult = Application.Run("PIPutValx", _ Application.ActiveSheet.Range("M42").Text, _ Application.ActiveSheet.Range("M23"), _ Application.ActiveSheet.Range("H2").Value, , _ Application.ActiveSheet.Range("N42")) End Sub I've seached many resources online and I've tried many different things but I cannot seem to figure out why Excel will not respond to the Application.Quit command. Is it possible that I need to close solver.xla before Excel can be completely closed out? Also, I am running Excel 2003 with SP3 and all Microsoft security updates and patches. Any help on this at all would be greatly appreciated. Thanks J A |
Application.Quit Does not Close the Application
|
Application.Quit Does not Close the Application
Thanks for the reply. I tried both suggestions and Excel still will
not close. If I don't use ActiveWorkbook.Close then the everything stays open after the Macro is run. Does anyone have any other reccomendations? Could it be that the solver.xla object needs to be closed before Excel can be closed? Forgive me but I'm very new to VBA and I'm still trying to learn it. Also, I have tried this in Excel 2000 and I receive the same results (Excel does not close). Any help at all would be appreciated. I've spent hours trying to find a resolution and I'm getting rather frustrated. Thanks J A |
application.quit in Excel 2003 doesn't work after call to Solver
Were you ever able to solve this problem, if so how did you do it?
justin.arnold wrote: Application.Quit Does not Close the Application 18-Sep-08 Thanks for the reply. I tried both suggestions and Excel still will not close. If I don't use ActiveWorkbook.Close then the everything stays open after the Macro is run. Does anyone have any other reccomendations? Could it be that the solver.xla object needs to be closed before Excel can be closed? Forgive me but I'm very new to VBA and I'm still trying to learn it. Also, I have tried this in Excel 2000 and I receive the same results (Excel does not close). Any help at all would be appreciated. I've spent hours trying to find a resolution and I'm getting rather frustrated. Thanks J A EggHeadCafe - Software Developer Portal of Choice Microsoft Webservice HTC Behavior Revisited http://www.eggheadcafe.com/tutorials...rvice-htc.aspx |
application.quit in Excel 2003 doesn't work after call to Solver
I don't know if the OP solved the problem, but for the Application to close
using Application.Quit, everything that was opened during that instance of excel must be closed prior to executing the command. If VBA finds anything at all open in that instance, it will not close the Application. <Ann Leland wrote in message ... Were you ever able to solve this problem, if so how did you do it? justin.arnold wrote: Application.Quit Does not Close the Application 18-Sep-08 Thanks for the reply. I tried both suggestions and Excel still will not close. If I don't use ActiveWorkbook.Close then the everything stays open after the Macro is run. Does anyone have any other reccomendations? Could it be that the solver.xla object needs to be closed before Excel can be closed? Forgive me but I'm very new to VBA and I'm still trying to learn it. Also, I have tried this in Excel 2000 and I receive the same results (Excel does not close). Any help at all would be appreciated. I've spent hours trying to find a resolution and I'm getting rather frustrated. Thanks J A EggHeadCafe - Software Developer Portal of Choice Microsoft Webservice HTC Behavior Revisited http://www.eggheadcafe.com/tutorials...rvice-htc.aspx |
application.quit in Excel 2003 doesn't work after call to Solver
Adding to that, If there are any objects or object variables that still have
values, then the application will not close, so all object variables should be Set to Nothing before using Application.Quit. <Ann Leland wrote in message ... Were you ever able to solve this problem, if so how did you do it? justin.arnold wrote: Application.Quit Does not Close the Application 18-Sep-08 Thanks for the reply. I tried both suggestions and Excel still will not close. If I don't use ActiveWorkbook.Close then the everything stays open after the Macro is run. Does anyone have any other reccomendations? Could it be that the solver.xla object needs to be closed before Excel can be closed? Forgive me but I'm very new to VBA and I'm still trying to learn it. Also, I have tried this in Excel 2000 and I receive the same results (Excel does not close). Any help at all would be appreciated. I've spent hours trying to find a resolution and I'm getting rather frustrated. Thanks J A EggHeadCafe - Software Developer Portal of Choice Microsoft Webservice HTC Behavior Revisited http://www.eggheadcafe.com/tutorials...rvice-htc.aspx |
| All times are GMT +1. The time now is 06:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com