I have a workflow that is running a batch file that updates an excel file. When I manually run the workflow on my computer or the scheduler, the excel file is updated. But when the scheduled workflow runs and the computer is locked (Windows 10 machine), the batch file does not execute and the file does not update. Is there a workaround for this problem?
How are you updating the file using a batch script?
Excel can be funny at times with how it is being worked on programtically, so it could be due to this.
You could also try to run the batch file using the Windows Scheduler and not Alteryx, at a time when your computer is locked, to test my theory.
With the app in Alteryx that can run commands
Script:
$app = New-Object -comobject Excel.Application
$app.displayAlerts = $false
$app.Visible = $false
$wb = $app.Workbooks.Open("\\cardinalhealth.net\shared\OH001\CORP\GTIT\Scheduled Reports\In Transit Dashboard\In Transit Dashboard V3.xlsx")
$wb.Name
$wb.RefreshAll()
Start-Sleep -s 60
$wb.Save();
$wb.Close()
$app.Quit()
Bat:
@ECHO OFF
PowerShell.exe -Command "& '%~dpn0.ps1'"
And the issue isn't Excel not updating, it is that Alteryx does not successfully run the script on our scheduler when the machine is locked.
When you say the script doesn't successfully run, what is it you mean?
Are you able to test writing to a text file, to check that its not an issue with the batch script?
If you could add the below somewhere in the batch file and check it is created
echo test successful > C:\TestOutput.txt
If it is, we know Alteryx is running the batch file OK, and that the issue is down to the Excel part, and probably the COM Object