Output Files to a Shared Drive

[jason_clough14]

Is there a way to dynamically name the excel output file depending on the current day? Ideally, I could have the file be named the prior day for each business day.

[estherb47]

Hi @jason_clough14 

Yes. You can use a formula tool to add a new column to calculate the prior day. 

DateTimeAdd(DateTimeNow(), -1, "days")

Then, in the Output Data tool, configure it to change the file name using that new field.

If it's just changing the Sheet name, then you can try to change the entire file path instead. In that case, add the whole file path to the new field created in the formula tool.

Let me know if that helps!

Cheers,

Esther

[WillBowman]

Yep and it is pretty easy.

1.

Grab a formula tool and put in a forumlua for the prior day:

todate(datetimeadd(DateTimeNow(), -1, 'days'))

make sure to include toDate to strip the hh:mm:ss part.

2.

On your output tool configure the output to use the field as a suffix on the file name. It will make a file for each field value, but in this case they are all the same so there is only one output, with yesterdays date in appended to the name.

[jason_clough14]

Thanks Esther! Is there a way to specify business day? For example, I would want it to reference the prior Friday's date on a Monday.

[WillBowman]

Got u fam

if DateTimeFormat(DateTimeNow(),"%A") = 'Monday' then todate(datetimeadd(DateTimeNow(), -3, 'days'))
else
todate(datetimeadd(DateTimeNow(), -1, 'days'))
endif

Does not account for holidays.

[estherb47]

Hi @jason_clough14 

Combine mine and @WillBowman 's approaches and you'll have what you need!!

Cheers,

Esther

[jason_clough14]

@estherb47 @WillBowman Thanks both - the formula seems to be working but the file name is not changing to fill it. Anything here look wrong? I have it writing to my desktop called "ABLE Daily File" then looking for the date to preface that

[WillBowman]

It should be prepend or append like my first post. Change file name will make the entire file name change to the content of the field.

[estherb47]

Hi!

Try to amend the formula to include the file path (should be a constant available in the formula tool) plus a / plus the date field.

Then change the Output Data tool to change the entire file path.

Let me know if that works.

Cheers,
Esther

[jason_clough14]

It's not changing the file name at all with either option selected - do I have something wrong with the Write to File section at the top? It's just saving to my desktop