Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

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

lepome
Alteryx Alumni (Retired)
Created

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 usetools from the Interfacecategoryof 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 yourSheet_namefield 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
Comments
MCDR929
8 - Asteroid

This is exactly what I needed, but the challenge now is... how do I put this all into one workflow? Does the macro need to run as a separate workflow in order for this to work? 

lepome
Alteryx Alumni (Retired)

@MCDR929 
If I'm understanding the question correctly, you build the macro and then integrate it into the workflow.  What I have as an Input Data tool in my "Test.yxmd" would be the workflow you have built to do the processing, and you'd add the new tools (including the macro) to that.  I believe that you could use a Date Time Now tool instead of the Text Input and Formula tool to set the date suffix.