How to Utilize Prepend in Output Tool

[kelly_wu]

I have output tools with a file path (U:/Desktop/Output/April2021.xlsx|||'DataA$A1:B15') but I want to update the file path (specifically the month and year) while keeping everything else the same. I tried using the "prepend" option but it's not working. I don't want to make it a macro, is there a way to update the month without a macro?

[Luke_C]

Hi @kelly_wu 

If I'm understanding correctly, you want to update the output file name for the month you run it, so for example in may it would be May2021 with May's data? If so, you can use the formula tool to create a file name:

Then, in the output tool, select 'take file/table name from field' in the bottom left. Choose 'Change entire file path' and select the new field we created in the formula tool. Then uncheck 'keep field in output'.

[kelly_wu]

Hi @Luke_C , that would be great but I have multiple output tools that specify the same sheet but different ranges. Ideally I created the beginning path "U:/Desktop/Outputs/NewMonthNewYear.xlsx" because I have multiple output tools that are as follows: 

- Data$A1:B15

- Data$A17:B30

- Data A$32:B40

etc. 

I'm curious if there's a way to use the prepend option instead of using macros or multiple formula tools that configure the full path with the sheet and range as you included because there are about 25 output tools. 

[Luke_C · Accepted solution]

Hi @kelly_wu 

When working with xlsx files the prepend option will only update the tab name, not the file name. 

[apathetichell]

Change Entire filepath in lieu of prepend and  use your cool new dynamic month/year filepath/name that @Luke_C  developed, and which I just noticed he pointed out as well.

you can include variables for the sheet ranges in your filepath as well which make make the ranges more dynamic.

If you were just doing every 15 rows with skipping the 16th you could use tostring(floor([recordid]/16)*16)+1) for your starting range  and (tostring(floor(recordid/16*16)+15) for your ending range  (or something like that. I notice though that each range has a different number of rows. If you can create a series of beginning and ending rows ([recordids]) of a range (and you'll need an identifier to explain which record is attached to which range) you can use that in your formula with an if statement.

Note - I can see this being a pain if it's 4 or 5 ranges but if it's 50 or 60 - this is worth your time and effort.