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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Excel File Name AND Sheet Name

Justin_SVU
7 - Meteor

I have a workflow that needs to run and append the date to the file name then update 3 tabs within the sheet with specific tab names. I seem to be able to do 1 or the other. I can update specific named tabs, but not have the date appended to the file name OR I can get the date appended, but then it just makes the tab "sheet1". What I have tried is:

 

1. Just use a name without an appended today's date field. This results in being able to choose my tab name and I can get all tabs updated.

 

2. Using the Table & Render tools to append the date to the .xls or .xlsx file name. This results in not being able to name the tab. It just names it "sheet1"

 

Essentially what we are trying to accomplish is having a workflow that creates a spreadsheet identifying payments due this week, and payments needing removal from a list on separate tabs. It runs weekly and would append that weeks date to the file name. As stated above I seem to be getting 1 or the other, but not both. 

 

Any thoughts or help would be appreciated.

 

Thank you!

10 REPLIES 10
Claje
14 - Magnetar

Hi,

If you use the "Change entire file path" option in the Output Data tool, you can pass in a file name (and path) directly from your workflow.  You would want to use a Formula beforehand to write out the full filename (including date), and the sheet name.

Justin_SVU
7 - Meteor

That worked flawlessly. Thank you!

jforster
6 - Meteoroid

Did you use the standard format used in an output file (i.e. test.xlsx|||sheetname)? I'm getting an error from my render tool every time it tries to output the file this way and it crashes Alteryx.

Justin_SVU
7 - Meteor

I didn't use the render tool since I haven't had a lot of luck with that tool in the past. If I want something formatted in Excel I have generally used a command tool to kick of a bat file that runs a macro in the workbook.

 

In this case I used a normal output, selected 'Replace entire File Path' and unchecked keep field in output. I have an example from one of my workflows below. 

 

"file path\" + "Weekly Retention Updates " + [Date for Name]  + ".xlsx" + "|Payments Due - Not on Roster"

 

Be somewhat careful as if you use a field like I am and that field differs by row it will create multiple tabs or files depending where it is placed. At the same time that can be super useful when needed. Similar to bursting files.

jforster
6 - Meteoroid

Oh okay, thanks for the quick response. Yeah my issue is only with the render tool when trying to dynamically name the file and sheet as it seems to be much harder to configure.

Justin_SVU
7 - Meteor

No problem. I did a little trial and error and I got it to work.

 

The options in the render tool I chose are:

 

  • Output Mode: Choose a Specific Output File
  • Output File: I just named a random file on my desktop
  • Group Data into Separate Reports: I checked this and selected my File Name Field.
    • I also picked: Replace Entire Path With Group
  • Data Field: Table
  • Rest I left alone

 

When I ran it 1st it threw an Inbound Loop error due to an unknown pipe. I removed the piece of the file that was identifying the tab name and it worked.

 

In short: Delete everything to the right of the file extension and it should work.

jforster
6 - Meteoroid

Just figured it out to get both file and sheet name. Need to use a layout tool with the Layout mode as each individual record, orientation as vertical with section breaks, and then there should be a section name field pop up and you can define your tab name. The file name process is exactly what you just mentioned though.

Justin_SVU
7 - Meteor

Perfect. Thanks for the update. Glad you got it working. I am going to add this to my notes. I'm sure it will come in handy at some point.

jforster
6 - Meteoroid

clipboard_image_0.png

Labels