topic Re: Run Command tool not successfully executing excel macro through vb script in Alteryx Server Discussions

Run Command tool not successfully executing excel macro through vb script

Brian_Denso — Mon, 03 Jul 2023 17:34:57 GMT

I have a Run Command workflow. When I run it on my local computer, works fine. When I run it on the server Designer version it works fine and creates the expected excel file. When I run in the gallery, it executes successfully but the excel file is not created. The excel macro is manipulating some data and saving off a new file. I've attached the run command image as well as the vb script code below.

I confirmed that the server isn’t restricted. I cannot figure out why it won’t work on Gallery.

'Input Excel File's Full Path
ExcelFilePath = "C:\Test_Files\Template\Template.xlsm"

'Input Module/Macro name within the Excel File
MacroPath = "Module1.Save"

'Create an instance of Excel
Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?
ExcelApp.Visible = True 'or "False"

'Prevent any App Launch Alerts (ie Update External Links)
ExcelApp.DisplayAlerts = False

'Open Excel File
Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)

On Error Resume Next
'Execute Macro Code
ExcelApp.Run MacroPath

'Save Excel File (if applicable)
wb.Save

'Reset Display Alerts Before Closing
ExcelApp.DisplayAlerts = True

'Close Excel File
wb.Close

'End instance of Excel
ExcelApp.Quit

Re: Run Command tool not successfully executing excel macro through vb script

fmvizcaino — Mon, 03 Jul 2023 17:50:06 GMT

Hey @Brian_Denso ,

See if this helps. https://community.alteryx.com/t5/Alteryx-Server-Knowledge-Base/Workflow-with-VBScript-is-not-working-in-Alteryx-Server/ta-p/493518

Two quick tests would be to add your user as the run as or account running the AlteryxService service or open the server`s designer (shift + right click - run as a different user) with the run as/service account and test the same script.

Best,

Fernando Vizcaino

Re: Run Command tool not successfully executing excel macro through vb script

ArnaldoSandoval — Tue, 04 Jul 2023 05:31:24 GMT

Hi @Brian_Denso

Avoid full path for file names in objects deployed to the Server or Gallery, they will work fine in your local environment, while I doubt this folder (C:\Test_Files\Template) exist on the server; when deploying objects to the server-gallery any physical file reference should be relative to the folder hosting the workflow, and better when they dont venture to folder outside its home directory.

hth

Arnaldo

Re: Run Command tool not successfully executing excel macro through vb script

Brian_Denso — Tue, 04 Jul 2023 13:09:14 GMT

Fernando,

The workflow executes successfully, I'm assuming just the input/output version, but the macro itself isn't ran which creates the resulting excel file.

I was able to go the sever events and realized that there were some permissions errors within the registery that I was able to correct.

Now I have an application error for excel.

I'm unable to access the server right now due to company planned power outage but the error message is along the lines of:

Faulty Application: EXCEL.EXE, version 16.

I tried to do some research on how to fix but was unsuccessful. I'm thinking there are potentially more permissions that need to be corrected.

Arnaldo,

Without specifying which excel file to open, how will I get the vbscript to correctly choose the macro?

As for the file location, C:\Test_Files\Template does exist within my server environment. I have been attempting to avoid any folder permisssion errors so I began saving documents to the C: Drive within the server environment.

Re: Run Command tool not successfully executing excel macro through vb script

nehakakar — Tue, 04 Jul 2023 13:39:10 GMT

You should consider using relative paths instead of full paths.

Try this!

'Get the folder path of the script ScriptFolderPath = Left(WScript.ScriptFullName, InStrRev(WScript.ScriptFullName, "\")) 'Input Excel File's relative path ExcelFilePath = ScriptFolderPath & "Template\Template.xlsm" 'Input Module/Macro name within the Excel File MacroPath = "Module1.Save" 'Create an instance of Excel Set ExcelApp = CreateObject("Excel.Application") 'Do you want this Excel instance to be visible? ExcelApp.Visible = True 'or "False" 'Prevent any App Launch Alerts (ie Update External Links) ExcelApp.DisplayAlerts = False 'Open Excel File Set wb = ExcelApp.Workbooks.Open(ExcelFilePath) On Error Resume Next 'Execute Macro Code ExcelApp.Run MacroPath 'Save Excel File (if applicable) wb.Save 'Reset Display Alerts Before Closing ExcelApp.DisplayAlerts = True 'Close Excel File wb.Close 'End instance of Excel ExcelApp.Quit

The ScriptFolderPath variable to construct the relative path to the Excel file,

This cript will work regardless of the server or gallery environment's folder structure.

Re: Run Command tool not successfully executing excel macro through vb script

fmvizcaino — Tue, 04 Jul 2023 14:51:55 GMT

@Brian_Denso ,

It is clear that it is a permission issue.

If you could enable the option to Allow users to select credentials, you could use your own credential in this specific workflow.

https://help.alteryx.com/20221/server/select-workflow-credentials-setting-your-server

Best,

Fernando Vizcaino

Re: Run Command tool not successfully executing excel macro through vb script

Brian_Denso — Thu, 06 Jul 2023 02:00:00 GMT

Fernando,

Just to follow up, I have found the solution. It was a permission issue. The server was setup with a default user which needed to have permissions in the COM registries. Originally, I only fixed the issues specific to my personal login and not the default user account.

Here is the link I used to fix the permissions in case this comes up for anyone in the future. My registries that needed permission were different from the link, but the process was the same.

How to Fix 'Application-specific permission settings' Error - Appuals.com

The application-specific permission settings do not grant Local Launch permission for the COM Server application with CLSID
{21B896BF-008D-4D01-A27B-26061B960DD7}
and APPID
{03E09F3B-DCE4-44FE-A9CF-82D050827E1C}
to the user NA\USER SID (S-1-5-21-1289235617-2937510960-3569147640-19828) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.