I've been reading questions for days and I can't find an answer. I used Windows Scheduler to daily run an Rscript which creates an Excel file (but as this file has an XML problem for what I read in another question) but I can't use this file in PowerBI unless I open and save it, so Powershell and Windows Scheduler come to my rescue. But the problem appears when I schedule the task in Windows Scheduler. Because manually using the script below works perfectly, but if I make a test run in Windows Scheduler the file can't be successfully saved, so the process stucks with Excel asking me if I want to save the file.
I have this script to open my fresh Excel file and basically make no changes, then being saved and exit Excel. So, if I run this script manually from Powershell everything is fine, the file is opened, saved and closed just as I need.
$erroractionpreference = "SilentlyContinue"
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $true
$Workbook = $Excel.Workbooks.Open("file location")
$Worksheet1 = $Workbook.Worksheets.Item(1)
$Worksheet1.Activate()
$Worksheet1.Cells.Item(1,22) = ""
$Workbook.Save("file location")
$Workbook.Close()
$Excel.Quit()
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Worksheet1)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
Remove-Variable Excel
Remove-Variable Worksheet1
IF I CHANGE THIS PART OF THE SCRIPT, THE EXCEL FILE IS CLOSED BUT NOT SAVED.
$Workbook.Save("file location")
$Workbook.Close($false)
$Excel.Quit()
As I said before I don't why it happens when using the scheduler. Also I have tried modifying the script with saveas or even taking some parts out but same result.
Or do you know another solution for my problem at the beginning?
