Refresh Excel Query

[npatela4]

Does Alteryx have the ability to refresh a specific query in Excel?

Thanks

Neel

[IraWatt]

Hey @npatela4,

I'm sure you could do it using the Python tool though I cant think of a more straight forward way. What are you querying out of interest, would it not be easier to run the query in Alteryx?

[rfoster7 · Accepted solution]

I built a macro that refreshes all data connections in the excel file you point it at. It just uses cscript. You'd have to play around with it to get it to do only one specific data connection, but if you open it up and take a look at what I'm doing it shouldn't be hard to figure out. 

[npatela4]

Thanks a lot! I will try this.

[Meenav]

Hi 

Thank you for this wonderful post. What should be done if I want to connect this macro to one of my workflow. This macro should run first before the other workflow tools.

[rfoster7]

It tends to looks like this. We write the data out to an XLSX file data tab, then, using block until done, after we write out the data we condense the stream to one row (so it only runs the refresh once), create a string column with the filename path and then send the filename path to the refresh excel macro. 

The refresh excel macro is creating a simple vbscript, writing it out to a temp file and running the temp file with cscript (or wscript) in order to do the refresh. 

[Meenav]

Hi

Thank you for the reply. But still am not quite sure about this. I have to add the refresh macro to my existing workflow and need to server automate it. The macro should run first before the other workflow tools. 

[Meenav]

Hi 

Thank you, this worked but when moved to Alteryx  server does not refresh excel.