Creating daily excel files

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!

Output

Help

Accepted answers

Robin_McIntosh

Then this answer will generate a new file each day it is run with the current date as part of the file name. If you also want the sheet name to be the current date, then update the formula as: '\\bhsfs\Shares\Projects\BI\DOH\BMH Juvare Bulk Upload_' ++ ToString(DateTimeToday()) ++ '.xlsx|||' + ToString(DateTimeToday())

Otherwise change the SheetNameHere to Sheet1.

All comments

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.

Discussion_Excel_Output_Formula.yxmd

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.

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

Otherwise change the SheetNameHere to Sheet1.

adam_jones

That was it! I see the new file, that makes perfect sense. Thank you again.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335