community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How To: Modify File Name for Multi-Tab Excel Workbook

Alteryx
Alteryx
Created on

You probably already know that you can output results to multiple sheets of an Excel file.  If not, you should check out our resource that explains how to do that very thing.  But what if you run that workflow every day, and you want to keep the outputs from days past?

 

Then you have a choice:  You can modify the Output Data Tool with a new filename every day, but that gets tedious.  You could make the workflow into an app that queries the user for a new filename. Or you could make it completely automated by appending the current date to the filename.  

 

But wait.  If you're outputting to multiple sheets already, how do you configure the Output Data Tool to change the base filename?  The Take File/Table Name from Field option is already in use with the sheet names!

 

Using Field for Sheet Name.png                                                                      app for filename.png  

 

Whether you make the workflow into an app or not, the answer is fundamentally the same: You use tools from the Interface category of the tool palette to modify the output.  

 

App Example

As you may know, Interface tools are never used alone.  The Action Tool is used to control how to use the input obtained from another Interface tool.  In the app case above, the Action Tool updates part of the output file name with the specific string that the user enters into the Text Box Tool.  We make the action type Update Value, which is the default, select the File value, and indicate the portion of the path and filename we want to change.  In my example, I want to store the file in the same directory as the workflow so I use .\ for the path and I want a completely new file name, so I replace that text.

 

Action.png

 

Batch Macro Example

Now, how do you set it up to just update the file name with the current date? 

It's fundamentally similar to the app example.  Create a new workflow with the Output Data Tool, but this time, use a Control Parameter Tool instead of a Text Box.  The only configuration difference is to retain the base file name and only replace the underscores.

 

Append Date.png

 

We want to be able to add this macro to our original workflow to output the whole file, so we also need to add a Macro Input Tool.  Leave the Show Field Map box unchecked for convenience.  You will need to create some dummy data as a Text Input to try the macro.  It's very convenient to use a row of data with header, but any configuration should work as long as you have your Sheet_name field to feed into the Output Data Tool.  Try it out, and save the Macro with a convenient name.

 

Now, all you have to do is add the macro to your workflow together with a tool or two to generate the current date:  

 

A Formula Tool can generate the current date:

 

DateTimeFormat(DateTimeNow(), '_%m-%d-%Y')

 

Here I have formatted the date as a string using dashes rather than slashes because I don't want the numbers to be interpreted as part of the file path. 

 

workflow with macro.png

 

The final step is to configure the Macro to use the Current_date as the control parameter.  Click on it, and then on the Questions tab in the Configuration window on the left.  Select the field from the drop down.

 

As a nice touch to help your future self, annotate the macro by selecting it and clicking on the annotate tag in the Configuration window.

 

Annotate Macro.png

 

 

There you go!  A tool you can use in other workflows as needed that will output a multi-sheet file with a unique name each day.  

Attachments