Excel as background task dies on Application.Quit

[Tony51203]

I'm running a test excel workbook from task scheduler on a W2K Pro server under an admin account.

The workbook installs an add-in used to read data from an external data historian.

Next it creates a new blank workbook, activates the 1st sheet, dynamically builds cell formulas to call call one of the add-in's functions thus retrieving the data, does a SaveAs to save the new workbook after using Kill <...> to be sure the book doesn't alreayd exist.

Finally it turns off DisplayAlerts and does an Application.Quit

in between each step it opens a text file in append mode and writes a line of text indicating it's progress then closes the text file.

When I try letting task Scheduler execute this without a user logged into the server then log in and look at the text file I see it makes it all the way up to and including the point where it writes a line to the text file indicating it will close Excel. Very next line does the application.quit, yet excel never closes (it shows up in task manager w/zero cpu)

Code (sloppy at this point) is:

Private Sub Workbook_Open()
Const sAddInFullName As String = "D:\Program Files\PIPC\Excel\pipc32.xll"
Const sAddInName As String = "PI-DataLink"
Dim sAdiPath As String
Dim sLibPath As String
Dim i, outname As String

outname = "d:\TEST" & Format(Date, "yymmdd") & _
Format(Time, "hhmmss") & ".txt"
Open outname For Append As #1
Print #1, "test " & Format(Date, "dd-mmm-yyyy") & " " & Format(Time, "hh:mm:ss")
Close 1

sAdiPath = sAddInFullName
sLibPath = Application.LibraryPath & Application.PathSeparator & "pipc32.xll"

On Error Resume Next
FileCopy Source:=sAdiPath, Destination:=sLibPath

With AddIns(sAddInName)
If Not .Installed Then
AddIns.Add Filename:=sLibPath
.Installed = True
Open outname For Append As #1
Print #1, sAddInName & " loaded from " & sLibPath
Close 1
End If
End With
On Error GoTo 0

Dim wkb As Workbook
Dim wks As Worksheet
Dim mycell As Range
Set wkb = Workbooks.Add
Open outname For Append As #1
Print #1, "new workbook created"
Close 1
wkb.Activate
Open outname For Append As #1
Print #1, "new workbook activated"
Close 1

Set wks = ActiveWorkbook.Worksheets(1)

mycell.Formula = "=PICurrVal(" & Chr(34) & "0001_228_ft" & Chr(34) & _
", 1," & Chr(34) & "phspi001" & Chr(34) & ")"

mycell.NumberFormat = "dd-mmm-yy hh:mm:ss"

Set mycell = wks.Cells(12, 2)

mycell.Formula = "=PICurrVal(" & Chr(34) & "0001_228_ft" & Chr(34) & _
", 0," & Chr(34) & "phspi001" & Chr(34) & ")"
Open outname For Append As #1
Print #1, "formulas written"
Close 1

wks.Calculate
Open outname For Append As #1
Print #1, "recalced"
Close 1

On Error Resume Next
Kill "d:\testsave.xls"
Open outname For Append As #1
Print #1, "old xls file deleted"
Close 1
ActiveWorkbook.SaveAs Filename:="d:\testsave.xls", CreateBackup:=True
Open outname For Append As #1
Print #1, "new xls file saved"
Close 1

Application.DisplayAlerts = False
Open outname For Append As #1
Print #1, "closing excel"
Close 1
Application.Quit
End Sub




Does anyone have a clue what I'm doing wrong?

 

[BrianB]

After

Application.Quit

try

ThisWorkBook.Close savechanges:=false

ThisWorkBook is the one containing the code.
It seems that we cannot close the workbook (or Excel) with its code running. You may have to adapt earlier code. All other workbooks have to be closed, of course.

 

[George J]

I was going to suggest

Thisworkbook.saved

 

[Tony51203]

Thanx.

Closing the new workbook was the trick. I had to close it before quitting excel but after turning alerts off.