VBA macro in the workflow not running on Sandbox

[vj28]

EDIT : Pretty much no vba/vbs macros are running on the sandbox, irrespective of workflow. No idea why this is happening, if i remove the macros the workflow runs fine. 

Excel files have some formulas that need to calculate/refresh post populating said file with alteryx, so I use some VBA code to open, save and close file to "refresh", so the formulas calculate. (VBA code is triggered by a vbs file that I link through a Run tool.)

This whole thing works fine locally but in sandbox workflow gets stuck at that run tool infinitely. 

I tried opening the vba file manually and it opens fine, tried running the vbs script and and it gives no errors.. The file that's being refreshed also opens fine so no corrupted files or anything like that causing issues.

Run tool Configuration:

Command:

wscript

Command Arguments[Optional]

path\to\file

Working Directory [Optional]

(empty)

read results and write sources are some random empty template files

Interestingly, I used to face this same issue locally and the fix was always opening task manager and manually ending background excel tasks. But how do I do this on a sandbox server? I feel like this is the issue because another different workflow that was working fine now has that exact same issue, although none of the alteryx workflow was modified since it was last working fine. 

All files on sharepoint and I've confirmed that Alteryx has access to that sharepoint 

[jenny84]

Hello,

You correctly observed that manually ending Excel background tasks fixes it locally — this strongly implies Excel.exe is being silently launched but not closed, often stuck in memory waiting for a UI event or permission.    Dog language translator

Best Regards

[vj28]

Looks like I can't deal with this issue without running some kind of script on the server ending background processes and since I don't have those permissions nothing more I can do at the moment.

@jenny84 If I've understood this correctly; each person has their own instance of sandbox running on the server; would that mean that if I'm having issues with orphan excel processes and such, other people running things on the sandbox server won't have that same problem on their server instance? 

[fmvizcaino]

Hi @vj28 ,

This happened to me and one of my clients once.

Try checking with the server admin if they recently changed the service account/run as user responsible for running the workflows on the server.

This could happen if this user doesn`t have enough permissions to launch DCOM objects. If it is a DCOM error, the best way to see the log is through the Windows Events.

https://knowledge.alteryx.com/index/s/article/Workflow-with-VBScript-is-not-working-in-Alteryx-Serve...

https://knowledge.alteryx.com/index/s/article/How-to-enable-COM-object-on-the-server-1583461640379

Best,

Fernando Vizcaino

[WirkKarl]

That does sound really frustrating. I’ve run into similar issues before where macros just don’t behave the same way in a sandboxed environment. Even if everything works fine locally, sandbox restrictions (especially around running scripts or interacting with Excel via COM) can silently block or hang things without clear errors. From what you describe, it really does sound like Excel is getting stuck in the background and just isn’t being handled properly in the sandbox. I know that’s tough to troubleshoot when you can’t just open Task Manager and end it like you would locally. You might want to check with whoever manages the server if script execution or Excel automation is limited. Hang in there — it’s not just you running into this kind of thing!

[vj28]

Turns out the issue was because I cancelled a workflow abruptly on sandbox when said workflow got suck on the run tool (that triggered the vba/vbscript) and so excel.exe never properly closed on the server. Anytime this happened I was pretty much screwed and had to wait a full day before this problem went away on it's own (I'm guessing a scheduled cleanup that ran every 24 hours or so). 

Microsoft explicitly doesn't recommend server side excel automation , so I guess issues like this are to be expected. 

This is still something I'm very wary of; if my workflow gets stuck at the run tool I pretty much can't run anything else on my sandbox for the next 24 hours which has led me to be extra careful. 

[Vinod28]

Hi @vj28, I request you to explore the following VBA script, let me know if this works?

• • Sub AutoRunMacro()
    On Error GoTo Cleanup

    ' Your macro logic here
    ' Example:
    MsgBox "Running macro..."

    Cleanup:
    ' Clean up objects if needed
    Application.DisplayAlerts = False
    ThisWorkbook.Close SaveChanges:=False
    Application.Quit
    End Sub

[vj28]

@Vinod28  Your response is almost certainly AI generated. I don't see the point of asking questions on a forum if I'm not gonna get responses from a human being.

One of the links you posted is also a question I asked myself and didn't get an answer to. 

[Vinod28]

Hi @vj28, please try this script if you are using VB. Let me know if it helps. 

Dim xlApp
Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = False
xlApp.DisplayAlerts = False

Dim wb
Set wb = xlApp.Workbooks.Open("C:\Path\To\YourFile.xlsm")

' Run the macro
xlApp.Run "AutoRunMacro"

' Clean up
wb.Close False
xlApp.Quit

Set wb = Nothing
Set xlApp = Nothing

[Vinod28]

Hi @vj28, if the above responses don't help your requirement, I request you to open a support case through MyAlteryx