Running a VBA script in Alteryx Gallery

Question

Hi, everyone. I would like to ask some help on running a VBA script from the Gallery. Originally it was stored locally and executed through Alteryx Designer - which has access to local applications such as Microsoft's. Since the process is moving to Gallery, it's running in the server, therefore, there's no  access to the local applications. The script is being attrached to the workflow, in order to upload the workflow along with the script to gallery. Aditionally, I just consulted this to some AIs which say even though it's possible to do this theoretically, in practice is not recommended. Has anyone tried this this before? Any suggestion? Thank you in advance.

EDIT: Workarounds on how to access the local apps through the gallery or other are welcomed.

danilang · Answer

Hi @MemoIgu16

The AIs are correct(for once) in in this case.  MS does not recommend using any Office app on a server (Unattended Mode)https://support.microsoft.com/en-us/topic/considerations-for-server-side-automation-of-office-48bcfe93-8a89-47f1-0bce-017433ad79e2.  Second paragraph.

One work-around is to preformat an excel file master, use a python tool to copy it to a shared drive and then have excel write to range while preserving format.

Dan

MemoIgu16 · Answer

Hi, Fernando!

Thank you for your help. So, basically it's formatting a bunch of excel files within a folder. I've explored the python approach but as it's an external program, there are some caveats/limitations which include not able to autofit rows nor columns, and not able to set a specific font/size as default out of Calibri 11 without looping through each cell - which might be expensive for the amount of information.

fmvizcaino · Answer

Hi @MemoIgu16 ,

Could you please expand on what you are doing with this VBA script?

You won't be able to access your local Excel from the Gallery, so your Server will need to have it installed on the server machine.

My suggestion is to use a Python script if you don't have Microsoft Office installed on your server.

Best,

Fernando