How to Append Field to Excel Output Filename?

[nrossin]

I am struggling to understand how to use a field to alter the output filename.

I have generated a field that I would like to use for the filename when writing my output Excel workbook.

For example, I am importing Input.xlsx and want the Output Data tool to write to 1118 Input.xlsx (which is the value of a field) to a sheet named New Sheet.  Is this possible?

I have tried all the options under Take File/Table Name From Field but the best I can do is change the name of the sheet, not the file itself.  How would I accomplish this?

On a related note, I would also need to somehow extract just the path of the input file; not sure how to do that either. If I could get that, I could just build the complete path & filename myself, I think. Would that also allow me to set the sheet name using |||'New Sheet'$?

[GarthMiles · Accepted solution]

@nrossin

If I'm understanding you correctly then you'll want to 'Change Entire File Path' (not the file/table name)

[nrossin]

@GarthMiles

I assume I could do that by extracting the original path from the input file. However, I do not know how to strip out the filename from that path; do you?

EDIT: I found the FileGetDir() function :)

[GarthMiles]

@nrossin

I built a macro for you but for some reason community won't let me attach the YXMC. Do you have a dropbox I upload to?

[GarthMiles]

@nrossin

try this

(macro is circled)

[nrossin]

Thank you for trying, Gareth. Unfortunately, we use Alteryx 2018.2 and cannot open this. I was able to use the FileGetDir() function as a workaround, though.

[GarthMiles]

@nrossin

Oh, right...forgot about that. See if you can download from here: link

Then...

All you probably need to do is open the YXMC file in the text editor of your choice (e.g., Notepad, VS Code, Atom, etc.) and replace the second line of the file from:

<AlteryxDocument yxmdVer="2018.4">

to:

<AlteryxDocument yxmdVer="2018.2">

and hit save!