Alteryx Gallery/Server - Powershell "Excel - ComObject issue"

[badboy66]

Dear all,

We created a powershell script in order to open/refresh/save/close an Excel file.

The script is called via the "Run command" tool in Alteryx as follows:

Powershell code:

Param ([string]$pfad_target)

$a = New-Object -ComObject "Excel.Application"

$a.Visible = $false
$a.displayAlerts = $false

$b = $a.Workbooks.Open($pfad_target)  #line triggering the error
$b.RefreshAll()
$b.Save()
$b.Close()

$a.Quit()

Problem:

The workflow runs well while being executed in the local Alteryx Designer and via Remote on the Alteryx Server Designer.

However, if the workflow was uploaded to the Alteryx Gallery (Private Studio) the script shows the following misbehaviour:

• The user has to specify its credentials to run the workflow --> while logging $env:USERNAME we can see, that the username has the same credentials as defined in the Alteryx Gallery 
• The script stops at the command "$b = $a.Workbooks.Open($pfad_target)"   --> showing the error message "Microsoft Excel cannot access the file ..."

Curious:

• When executing the workflow in the local Designer and via Remote on the Alteryx Server Designer --> The Microsoft Excel process appears in the Task Manager being executed with the correct Windows user credentials
• When starting the workflow via the Alteryx Gallery --> The Microsoft Excel process appears in the Task Manager of Windows Server being executed with the "SYSTEM" user (Please note, while logging the powershell script the $env:USERNAME still shows the correct Windows credentials)

Server version:

• Client: 2019.3.17947
• Server: 2019.3.5.17947
• Server Binaries: 2019.3.5.17947
• Service Layer
  • Master: 2019.3.5.17947

Designer version: 

• 2019.3.5.17947

Could you please advise what is going wrong here?

Thank you & best regards

[c-lopez]

Hi @badboy66,

If Server does have access to the file and you want to run as a particular user to execute the script them you could specify that on the ps1 script 

https://stackoverflow.com/questions/28989750/running-powershell-as-another-user-and-launching-a-scri...

Another thing to try is specify credentials of the workflow when uploading it to server

[SowmyaNagaraju]

Hi, I'm facing the same issue - while trying to modify Excel file using PowerShell script. Could you please tell if you found the solution to this problem. Thank you!