Executing jobs through VBA on non-Designer computer

[patrick_digan]

Hi, I'm looking to see if anyone else has been able to write some VBA to execute jobs on a remote server (even if that user doesn't have a designer license). My use case is this: we have some legacy jobs that Excel is still the best fit for; however, I want to be able to incorporate the power of Alteryx (fuzzy matching and CASS addresses in particular). In an ideal world, any user at my company would have the ability to click a button in excel and it would have some VBA that would interact with our server and get results back. For example, if they provide an address in excel, my vba could execute a job with the address as my inputs. The output would be the CASS address. I don't have the VBA skills to setup the necessary connections with our server (assuming it's possible). Any assistance/guidance would be much appreciated! 

[dataMack]

Excel and VBA is a hack at best - I'm sure it's possible, but that route will be full of problems and ongoing maintenance issues for you.  I'd go the pther direction and migrate your stuff to an Alteryx server with an Analytics Gallery- that way users can have a nice web interface to point to their address file, then get the results back.

[MarqueeCrew]

How about developing an application in the gallery and having others call your application through an API?

[patrick_digan]

@dataMack I totally agree with your points. Right now, all new jobs are being setup in Alteryx. We still have some legacy jobs with nice User Interfaces or manual proccesses that we haven't been able to find a way to have it work nicely in Alteryx (and the excel/access "applications" are working).

@MarqueeCrew My API knowledge leaves a lot be desired. Would you have any resources pointing me towards calling the Alteryx API through VBA? 

[MarqueeCrew · Accepted solution]

@patrick_digan

The api docs are located here:  https://gallery.alteryx.com/api-docs/

I don't have first-hand experience in the area, but this is the path that I'd go down to provide Alteryx benefits to other applications in my organization.

[jdunkerley79]

I would guess the easiest way is to use a file based semaphore approach. 

Have the VBA drop a file in a specific which triggers the server to run the job. I dont have much experience with server product but  guessing possible.

This file could contain config writen from VBA as needed. Something like (assuming required address is in the address variable):

Open "\\AlteryxServer\Triggers\process.txt"  For Output As #1
Print #1, address 
Close #1 

You will probably need a delay on the workflow so it does read until VBA has closed the file

Make the workflow in the server drop an additional file when process has completed and have the VBA wait until present before returning to Excel.

WHILE DIR("\\AlteryxServer\Triggers\processdone.txt") == "")
    Application.Wait(Now + #0:00:01#)
WEND

Not a perfect solution and you will probably need to pick up anyfile in the directory and run all in parallel. I'd guess naming the trigger <HostName>.<ProcessId>.txt and the result the same but .done would work.