I have a PowerScript that works great when I launch it with command prompt or ISE. The script basically refreshes an Excel file (connected to a SQL database). The refresh process takes a good 10-30 seconds to complete. The powershell script has section that pauses the process in order to let the refresh complete. The script then makes copy - creates a new file with today’s date and then saves for an email process (later). My problem is a windows task scheduler does run the script (without errors) - I know it launches because I write to a log file every time the script executes, but the process never completes - I never get a dated copy. Is there a way in Task scheduler to say - start the process - wait 30 seconds then end the process?
If it’s not completely you likely have something prompting for input.
Just to have it at least mentioned once … using the Excel ComObject is not supported in an unattended session launched via the task scheduler. You wouldn’t be the first one having issues with that.
I can recommend using the great module from Doug Finke ImportExcel.
Besides that - it’ll be hard to recommend something meaningful without seeing your actual code. 
Olaf - thanks so much for the reply. Here’s my code - please review.
Logging Example
$logFile = “\XXXXgpapp01\GPShare\000ExcelReports\PowerShell\LogFile.txt”
“Script started at $(Get-Date)” | Out-File -FilePath $logFile -Append
Define the path to the Excel workbook
$excelFilePath = “\XXXXgpapp01\GPShare\000ExcelReports\XXXX OpenAR_both_companies.xlsx”
$date = Get-Date -Format “yyyy-MM-dd”
$destinationFilePath = “\usw2gpapp01\GPShare\000ExcelReports\OpenAR_$date.xlsx”
Create an instance of the Excel application
$excel = New-Object -ComObject Excel.Application
Open the workbook
$workbook = $excel.Workbooks.Open($excelFilePath)
Refresh all data connections
$workbook.RefreshAll()
Wait for the refresh to complete
while ($excel.BackgroundQueryCount -gt 0) {
Start-Sleep -Seconds 20
}
Save a copy of the workbook to the destination path
$workbook.SaveAs($destinationFilePath)
Save the workbook
$workbook.Save()
Close the workbook
$workbook.Close()
Quit the Excel application
$excel.Quit()
Release the COM objects
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
Collect garbage
###Write-Output “Excel workbook refreshed successfully.”
Please go back and edit your post once again to fix the formatting of the code.
When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.
Thanks in advance
Guide to Posting Code - Redux <---- Click 
( !! Sometimes the preformatted text button hides behind the settings gear symbol. )
as @Olaf said, please format code when sharing on the forum.
However, even before you do that I can see right at the top
#Create an instance of the Excel application
$excel = New-Object -ComObject Excel.Application
You’re using the ComObject method of manipulating Excel. @Olaf already mentioned that you cannot use ComObjects in Task Scheduler:
If you leverage the ImportExcel module (which will require a rewrite of your script) you can launch it unattended.
1 Like
… and you don’t even need an installed instance of Excel … so could run it on a server for example. 
2 Likes
You will need to find another way to query the SQL database and then use the ImportExcel module to export to a .xlsx
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.