I had a script that would run a SQL stored procedure, wait, open a spreadsheet and do a refresh all. If I ran the script manually it would work just fine. As a scheduled task it would never save the spreadsheet. After some googling I found out you need to create a couple of folders.
Under C:\Windows\System32\config\systemprofile\ create a folder called Desktop
Under C:\Windows\SysWOW64\config\systemprofile\ create a folder called Desktop
After I did this, the script was able to save the updated spreadsheet.
#Set the file path (can be a network location)
$filePath = "<file path>\spreadsheet.xlsx"
#Create the Excel Object
$excelObj = New-Object -Com Excel.Application
#Wait for 10 seconds then update the spreadsheet
Start-Sleep -s 10
#Make Excel visible. Set to $false if you want this done in the background
$excelObj.Visible = $true
$excelObj.DisplayAlerts = $false
#Open the workbook
$workBook = $excelObj.Workbooks.Open($filePath)
#Wait for 10 seconds then update the spreadsheet
Start-Sleep -s 10
#Focus on the top row of the "Data" worksheet
#Note: This is only for visibility, it does not affect the data refresh
$workSheet = $workBook.Sheets.Item("Sheet1")
$workSheet.Select()
#Refresh all data in this workbook
$workBook.RefreshAll()
Start-Sleep -s 10
#Save any changes done by the refresh
$workBook.Save()
$workBook.Close()
#Uncomment this line if you want Excel to close on its own
$excelObj.Quit()
$excelObj = $null
write-host "Finished updating the spreadsheet" -foregroundcolor "green"
Start-Sleep -s 5