How to avoid orphaned excel nodes/ hanging processes on Sandbox

[vj28]

I use VBA macros as part of my workflow and sometimes when the workflow gets stuck at that part when running through sandbox I cancel the workflow, but it looks like the server still has EXCEL.exe processes running and this causes issues across the sandbox while trying to run different workflows. 

Sometimes I also get the vba file in question locked by the alteryx engine :

I'm guessing sysbitechalteryxnp refers to an instance of the alteryx engine. 

Here's that macro's code, I've made sure any possible issues that might cause orphan excel nodes or hanging processes are avoided :

VBA :

Sub macro1()
Dim objExcel
Dim objworkbook

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
'view the excel program and file, set to false to hide the whole process
objExcel.Visible = False

Set objworkbook = objExcel.Workbooks.Open("path\to\file")

objExcel.Calculation = xlCalculationAutomatic
objworkbook.RefreshAll
objExcel.CalculateUntilAsyncQueriesDone
objworkbook.Save
objworkbook.Close True

objExcel.Quit

Set objworkbook = Nothing

Set objExcel = Nothing

End Sub

VBS :

Option Explicit

' -----------
dim workbook_path
workbook_path = "path\to\file\trigger_formulas_vba.xlsm"
' Place your workbook file here
dim macro_name
macro_name = "macro1"
' Place your macro name here
' -----------

dim file_system
dim full_workbook_path
set file_system = CreateObject("Scripting.FileSystemObject")
full_workbook_path = file_system.GetAbsolutePathName(workbook_path)
' File address housekeeping

Dim ExcelProgram
Set ExcelProgram = CreateObject("Excel.Application")
' Tell the script what Excel is

ExcelProgram.Application.WorkBooks.Open full_workbook_path
' Open your workbook

ExcelProgram.Application.Visible = False
' Open it in the background

ExcelProgram.Application.Run "'"&full_workbook_path&"'!"&macro_name
' Run your Macro - this tells the Excel running in the background to find this workbook and macro.

ExcelProgram.Application.displayalerts = False
' Do not show prompts since we want this to be automated - Could switch to True to get prompts

ExcelProgram.Activeworkbook.Save
' Do not forget to save your work

ExcelProgram.Activeworkbook.Close
' Close the workbook

Is there any way to deal with this issue, without having to contact IT/sys admin to manually clear out/ kill any background excel processes?

[fmvizcaino]

Hi @vj28 ,

To my knowledge, whenever you cancel a workflow, the VBA script is stopped and the close operation (ExcelProgram.Activeworkbook.Close) doesn`t happen. Even if you add exceptions in your VBA, it won`t work.

The way I would do it is to add a Python script (inside your workflow with the Python tool) before running your VBA to force close our xlsm file before running it once again.

Keep in mind that your process is running on a server where other workflows are running and probably using excel, so be very specific and use the file name not to kill other excels running.

I would guess sysbitechalteryxnp is the service account responsible for running your entire Alteryx Server.

Best,

Fernando Vizcaino

[vj28]

@fmvizcaino Force closing processes on the server requires admin privileges though, or am I misunderstanding? 

[fmvizcaino]

The Alteryx Server user running your workflow has admin permissions. I suggest creating a simple Python script and testing it on your server to see if it works. 

[vj28]

@fmvizcaino ah yeah, makes sense. I'll try that out