Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.

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

badboy66
5 - Atom

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:

 

run command.png

 

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)

task manager.png

 

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

 

 

2 REPLIES 2
c-lopez
Alteryx
Alteryx

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

c-lopez_0-1595011812052.png

 

SowmyaNagaraju
6 - Meteoroid

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!