Creating daily excel files

[adam_jones]

Is there a way to output a new excel file daily to a folder or is the only option to add a new tab to an existing excel file?

I have a simple workflow, I have a formula that creates a new title which would be "project name" + DateTimeToday() , however all of the output tools or variations are requiring an excel file path. However, I don't have one nor do I want to create an excel file every day manually for it to upload to. End result, when the dataset schedule runs each day, it will output a new excel file with the current days data to a specific folder location, to a using the formula above as the file title.

Thank you!

[Robin_McIntosh]

I think this is what you are looking for.  Just update the formula (yellow highlight) with the filepath and name you want.  The file name in the Output tool is just a placeholder and not an actual file.  But you need to make sure you value the bottom of the tool configuration to take the filename from your formula.

[adam_jones]

That is one of the scenarios I've tried, and no matter what combo on the output tool I use, it requests a sheet. However, as you can see in the screenshot, I do have a sheet selected (unless I'm missing another one somewhere). If I take off the Change Entire Path option in bottom, it allows me to process the file then, but it doesn't create a new one it just overwrites the old one and doesn't change the file name with the date.

[Robin_McIntosh]

@adam_jones What does your formula for File Name look like?  And what is the specific error message?

Looks like the Output File you on the Output tool may be a network drive.  Do you have access to that drive and/or is is entered correctly?  This is most likely the problem.  If you are creating the actual file path in the formula, just use a placeholder like I have in the example screenshots above for the Output tool (OutputExample.xlsx|||Sheet1).

[adam_jones]

Below is the formula tool I used, I just used the actual file name in place of what you had. I assumed, that when uploading the new file, it would use this name and change the date so it created a new file. The path is correct, as I was able to do a test run and upload the data to the blank excel file that was originally there. However, it simply updated the data and did not create a new file with the date name or update the actual file with the date in the name.

[adam_jones]

apologies, below is the error.

[Robin_McIntosh]

**Edit - I just realized you are wanting the date to be part of the file name.  However, you still need to update the formula to include the 3 bars and sheet name which can be Sheet1 or a specific name.

So the forumla should be:   '\\bhsfs\Shares\Projects\BI\DOH\BMH Juvare Bulk Upload_' ++ ToString(DateTimeToday()) ++ '.xlsx|||SheetNameHere'

----------------------------------

@adam_jones Since you are changing the entire file path with the formula file name, your formula needs to be updated to include the path as well as the 3 bars '|||'.

Formula should be:  '\\bhsfs\Shares\Projects\BI\DOH\BMH Juvare Bulk Upload.xlsx|||' ++ ToString(DateTimeToday())

[Robin_McIntosh]

@adam_jones see my edit above.

[adam_jones]

Ok, that seems to have gotten rid of the error message (thank you!). Now tomorrow, when this runs for 8/26 will this create a new file or will it create a new sheet in the file?  I need it to create a new sheet with the file being name 'BMH Juvare Bulk Upload 8-26-2022' and then subsequently for each following day as long as the report runs.

[Robin_McIntosh]

So 'BMH Juvare Bulk Upload 8-26-2022.xlsx' is going to be the initial file that is used going forward and then new sheets are added each day.

Sheet 1 will be named 8-26-2022

Sheet 2 will be named 8-27-2022

Sheet 3 will be named 8-28-2022

Will you ever need to create a new file going forward?  Say 'BMH Juvare Bulk Upload 10-01-2022.xlsx' with sheet names of dates starting 10-1-2022 and so on?

Try this updated workflow where you have a text input with an initial file name.  This file name is then used going forward and the formula updates the sheet name with the current date.  Then the Output tool is updated to Create New Sheet on option 3.  Whenever, you need to create a new initial file, update the text input.  Hope that makes sense.