Automating Excel to perform overnight calculations

[insomniac_ut]

Hello,

I have 3-4 workbooks that I calculate every morning and I'd like to automate these so that they run overnight as part of a scheduled task.

I have written some code to open the workbooks, perform the calculations, save the workbooks and close Excel.

Of these 4 steps, the code performs the first two without a problem. However, the last steps are not performed and the Excel session remains open after finishing the calculations.

The code runs as follows:
Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
If Environ("Automatic") = "TRUE" Then
Call FirstButton
ThisWorkbook.Save
ThisWorkbook.Close
Application.DisplayAlerts = False
Environ("Automatic") = "FALSE"
Application.Quit
End If
End Sub

With the FirstButton method:
Public Sub FirstButton()
Worksheets("Current Points").UsedRange.Calculate
Worksheets("Floors").UsedRange.Calculate
Worksheets("Rolldown").UsedRange.Calculate
Worksheets("Summary").UsedRange.Calculate
Worksheets("Summary").Activate
End Sub

I was hoping that I could get some pointers as to what the problem might be?

Thanks in advance.

 

[Smitty]

Welcome to the Board!

What happens if you step through the code?

What's environ("Automatic") supposed to do? As far as I know that's not one of Environ's named arguments.

 

[insomniac_ut]

Thanks, Smitty. I'm not sure how to trigger the debugger when I launch Excel from a batch script.

Basically what I'd like to do is kick off this calculation when this workbook is launched from a batch script, but not when I open it manually. I'm not familiar with the Environ settings myself, but I believe this is exactly what this little piece of code does (given to me by a colleague).

 

[ZVI]

The key is in the batch file. It sets environment variable "Automatic" to "TRUE" and then run Excel to load workbook.

The code in Workbook_Open() checks the "Automatic" environment variable and if it’s equal to "TRUE" then runs the code, because it means that workbook was loaded automatically via batch file.

In case you loads workbook as usual then the "Automatic" environment variable is not set at all, and the code skips the main calculation.

You BAT file can be as follows:

set Automatic=TRUE
start Excel C:\Temp\MyWorkbook1.xls
exit

 

[Smitty]

Ahhh, gotcha. My ignorance for not working with batch files.

 

[insomniac_ut]

Thanks, Zvi. I finally got this cracked. Turns out the mistake was a real sitter. I was calling ActiveWorkbook.Close followed by Application.Quit.

It took me a while to figure out that once the workbook's closed, we're no longer in the Workbook_Open() method, so there's no way for the application to quit.

 

[ZVI]

Yeah, these lines should be excluded from the code:
ThisWorkbook.Close
Environ("Automatic") = "FALSE"

Glad you worked it out!