Windows Server 2008 R2 64 bit with Office 2010 Professional Plus 32 bit with SP2

I have a VB script that queries AD for inactive accounts, creates an html email and an Excel spreadsheet, attaches the spreadsheet to the email and sends the report.  Everything runs perfect when running it as the service account from a command line.

But when running it as a Schduled Task (as the same service account), it hangs at saving the Excel file.  I've tried many different forms of the SaveAs method but they all hang.  Here is the part where I save it:

	If iLL > 4 Then Addlog oLogFile, "Saving Excel file: " & sExcelFile 
	'oWorkbook.SaveAs(sExcelFile)  <-- commented out, trying different methods
	oExcel.ActiveWorkbook.SaveAs sExcelFile, 51
	
	If Err.Number <> 0 Then
        	sTemp = "Error saving Excel workbook: " & sExcelFile & " - " & Err.Description
	        Err.Clear
        	Addlog oLogFile, sTemp
	        sError = sError & TimeStamp(sTemp) & "<br />" & vbCRLF 
	End If
    
	If iLL > 4 Then Addlog oLogFile, "Closing Excel workbook..."
	oExcel.ActiveWorkbook.Close
	If iLL > 4 Then Addlog oLogFile, "Quitting Excel..."
	oExcel.Application.Quit

And the log file entry, it never hits the Error block and never hits the "Closing Excel workbook..." part.  This is the last 4 lines of the log:

9/5/2013 11:21:36 AM, Setting header row to bold...
9/5/2013 11:21:36 AM, Setting columns to Autofit...
9/5/2013 11:21:36 AM, Sorting by Displayname...
9/5/2013 11:21:36 AM, Saving Excel file: C:\Scripts\InactiveAccounts\data\InactiveAccountReport-20130905.xlsx

And the EXCEL process remains running under the service account.

But when running it from command line under the same account, it runs fine.  This is the log after running from the command line:

9/5/2013 11:25:48 AM, Setting header row to bold...
9/5/2013 11:25:48 AM, Setting columns to Autofit...
9/5/2013 11:25:48 AM, Sorting by Displayname...
9/5/2013 11:25:48 AM, Saving Excel file: C:\Scripts\InactiveAccounts\data\InactiveAccountReport-20130905.xlsx
9/5/2013 11:25:48 AM, Closing Excel workbook...
9/5/2013 11:25:48 AM, Quitting Excel...
9/5/2013 11:25:58 AM, Report Complete. Sending report to: stricklandgk@blahblah - Subj: Inactive AD Account Report
9/5/2013 11:25:58 AM, Trying SMTP Server relay.blahblah on port 25.
9/5/2013 11:25:59 AM, Succesfully sent email to: stricklandgk@blahblah

What research have you done so far, and with what results?

Bill

This line should be:

'oWorkbook.SaveAs(sExcelFile)  <-- commented out, trying different methods

Like this:

oWorkbook.SaveAs sExcelFile,  51

No parens and use type.

You should also se the flag to avoid prompts and use the full path.

oWorkbook.DisplayAlerts = False

I have been researching all of the different ways to SaveAs and they all work from the command line but hang when run from a Scheduled Task. I am thinking this is more of a problem with Excel itself than with vbscript.

I do have the DisplayAlerts = False:

oExcel.DisplayAlerts=False

And all of these works from the cmd line but hang under the Task:

'oWorkbook.SaveAs(sExcelFile)'<-thisworksfromcommandline
'oWorkbook.SaveAssExcelFile'<-thisworksfromcommandline
'oExcel.ActiveWorkbook.SaveAssExcelFile'<-thisworksfromcommandline
oExcel.ActiveWorkbook.SaveAssExcelFile,51'<-thisworksfromcommandline

Here is all of my code regarding the Excel portion:

'Create Excel attachment to send
	If iLL > 0 Then Addlog oLogFile, "Creating Excel spreadsheet..."
	
	'Excel column headers and variables
	Const xlCN = 1
	Const xlSAMACCOUNTNAME = 2
	Const xlDISPLAYNAME = 3
	Const xlLASTLOGONTIMESTAMP = 4
	Const xlWHENCREATED = 5
	Const xlDISABLED = 6		
	Const xlLOCKED = 7		
	Const xlPWDNEVEREXPIRES = 8
	Const xlACCOUNTEXPIRES = 9
	Const xlPWDLASTSET = 10
	Const xlMAIL = 11
	Const xlAscending = 1
	Const xlYes = 1
	Const xlWorkbookDefault = 51
	
	Dim sExcelFile, oExcel, oWorkbook, oRange, oRange2, oSheet
	
	If Mid(sExcelPath,2,1) = ":" Or Left(sExcelPath,2) = "\\" Then
		'The Excel path is a local/mapped drive or network share
	Else	
		'The Excel path is a subfolder of the script path
		sExcelPath = sPath & sExcelPath
	End If
	If Right(sExcelPath,1) <> "\" Then sExcelPath = sExcelPath & "\"
	If Not oFSO.FolderExists(sExcelPath) Then
	    oFSO.CreateFolder(sExcelPath)
	End If
	
	sExcelFile = sExcelPath & "InactiveAccountReport-" & YYYYMMDD(Now()) & ".xlsx"
	
	If iLL > 4 Then Addlog oLogFile, "Excel spreadsheet filename: " & sExcelFile
	
	If oFSO.FileExists(sExcelFile) Then
	    oFSO.DeleteFile sExcelFile, True
	End If
	
	Set oExcel = CreateObject("Excel.Application")
	oExcel.Visible = False
	oExcel.DisplayAlerts = False
	Set oWorkbook = oExcel.Workbooks.Add()
	oExcel.Cells(1, xlCN ).Value = "cn"
	oExcel.Cells(1, xlSAMACCOUNTNAME ).Value = "samAccountname"
	oExcel.Cells(1, xlDISPLAYNAME).Value = "displayName"
	oExcel.Cells(1, xlLASTLOGONTIMESTAMP).Value = "lastLogonTimeStamp"
	oExcel.Cells(1, xlWHENCREATED).Value = "whenCreated"
	oExcel.Cells(1, xlDISABLED).Value = "Disabled"
	oExcel.Cells(1, xlLOCKED).Value = "Locked"
	oExcel.Cells(1, xlPWDNEVEREXPIRES).Value = "pwdNeverExpires"
	oExcel.Cells(1, xlACCOUNTEXPIRES).Value = "accountExpires"
	oExcel.Cells(1, xlPWDLASTSET).Value = "pwdLastSet"
	oExcel.Cells(1, xlMAIL).Value = "mail"
	
	Dim y
	y = 2
	For Each sKey in dicUsers.Keys
	
	    Set oDicUser = dicUsers.Item(sKey)
	    
	    If iLL > 4 Then Addlog oLogFile, "Adding "& oDicUser.displayName &" to Excel spreadsheet..."
	
		oExcel.Cells(y, xlCN ).Value = oDicUser.cn
		oExcel.Cells(y, xlSAMACCOUNTNAME ).Value = oDicUser.samAccountname
		oExcel.Cells(y, xlDISPLAYNAME).Value = oDicUser.displayName
		oExcel.Cells(y, xlLASTLOGONTIMESTAMP).Value = oDicUser.lastLogonTimeStamp
		If oExcel.Cells(y, xlLASTLOGONTIMESTAMP).Value = "Never" Then oExcel.Cells(y, xlLASTLOGONTIMESTAMP).Interior.ColorIndex = 6
		oExcel.Cells(y, xlWHENCREATED).Value = oDicUser.whenCreated
		oExcel.Cells(y, xlDISABLED).Value = oDicUser.Disabled
		oExcel.Cells(y, xlLOCKED).Value = oDicUser.Locked
		oExcel.Cells(y, xlPWDNEVEREXPIRES).Value = oDicUser.pwdNeverExpires
		oExcel.Cells(y, xlACCOUNTEXPIRES).Value = oDicUser.accountExpires
		oExcel.Cells(y, xlPWDLASTSET).Value = oDicUser.pwdLastSet
		oExcel.Cells(y, xlMAIL).Value = oDicUser.mail
	
	    y = y + 1
	    
	Next
	
	If iLL > 4 Then Addlog oLogFile, "Setting header row to bold..."
	Set oRange = oExcel.Range("1:1")
	oRange.Font.Bold = True
	
	If iLL > 4 Then Addlog oLogFile, "Setting columns to Autofit..."
	Set oSheet = oExcel.ActiveSheet
	For y = 1 To 11
		oSheet.Columns(y).AutoFit
	Next
	
	If iLL > 4 Then Addlog oLogFile, "Sorting by Displayname..."
	Set oRange = oSheet.UsedRange
	Set oRange2 = oExcel.Cells(1,xlDISPLAYNAME)
	oRange.Sort oRange2, xlAscending, , , , , , xlYes
	
	If iLL > 4 Then Addlog oLogFile, "Saving Excel file: " & sExcelFile 
	'oWorkbook.SaveAs(sExcelFile)   				'<- this works from command line
	'oWorkbook.SaveAs sExcelFile					'<- this works from command line
	'oExcel.ActiveWorkbook.SaveAs sExcelFile		'<- this works from command line
	oExcel.ActiveWorkbook.SaveAs sExcelFile, 51		'<- this works from command line
	
	If Err.Number <> 0 Then
        sTemp = "Error saving Excel workbook: " & sExcelFile & " - " & Err.Description
        Err.Clear
        Addlog oLogFile, sTemp
        sError = sError & TimeStamp(sTemp) & "<br />" & vbCRLF 
    End If
    
    If iLL > 4 Then Addlog oLogFile, "Closing Excel workbook..."
	oExcel.ActiveWorkbook.Close
	If iLL > 4 Then Addlog oLogFile, "Quitting Excel..."
	oExcel.Application.Quit

According to the MS documentation you cannot run Excel without a UI without issues.

What account are you trying to run it under?

It appears that you are not handling errors correctly and that you are possibly hanging due to a message box trying to display.

Shorten the code to only that portion that is necessary to test the issues.

I also see that you have an "On Error Resume Next" somewhere very early in the code and you are never handling the errors.  Yu cannot do this ever and have a reliable outcome.  An error will happen and the code will continue on sending messages but you will never know what caused you failure.

The following is an example of how to correctly handle an error possibility:

    On Error Resume Next
    oWorkbook.SaveAs sExcelFile, 51		'<- this works from command li
    If Err.Number <> 0 Then
        sTemp = "Error saving Excel workbook: " & sExcelFile & " - " & Err.Description
        Addlog oLogFile, sTemp
        sError = sError & TimeStamp(sTemp) & "<br />" & vbCrLf
    End If
    On Error GoTo 0

We turnoff error handling. execute a line and test the error and then immediately turn handling back on.

I like to place things in functions because they retain the state of the error handling even though you do notexplicitly set it on.

Example:

Function SaveWorkbook( oWorkbook, sExcelFile )
    On Error Resume Next
    oWorkbook.SaveAs sExcelFile, 51		'<- this works from command li
    If Err.Number <> 0 Then
        sTemp = "Error saving Excel workbook: " & sExcelFile & " - " & Err.Description
        Addlog oLogFile, sTemp
        sError = sError & TimeStamp(sTemp) & "<br />" & vbCrLf
    End If
    SaveWorkbook = True
End Function

I would then call it in a protected way:

If Not SaveWorkbook(oWorkBook, sExcelFile) Then WScript.Quit -2

This lets us bail if an error has occurred rather then just keeping on blindly.

Build an error handling routine that manages reporting and quits when an error occurs.

Also note that Err.Clear does noting useful the way you are using it.