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

badboy66 — Tue, 14 Jul 2020 10:27:42 GMT

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

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

c-lopez — Fri, 17 Jul 2020 18:50:19 GMT

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-script

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

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

SowmyaNagaraju — Thu, 28 Oct 2021 19:56:53 GMT

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!

topic Alteryx Gallery/Server - Powershell "Excel - ComObject issue" in Alteryx Server Discussions

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

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

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