Button that exports dataset to new excel file each time it is pressed

I have made a button to write the last week of data from a dataset to an excel file. It works great except each time I press it it overwrites the last time I pressed it in the excel file. I would like a button that creates a new file or a new sheet in the file each time I export the data. How would I do this? Here is my code below:

params = {“StartDate”:system.date.now(),“EndDate”:system.date.addHours(system.date.now(), 7)}
results = system.db.runNamedQuery(“Downtime/FEXW/WestExitDTTrueOEEExcel”, params)
spreadsheet = system.dataset.toExcel(True, [results])
filePath = “C:\Users\pjolesbr\OneDrive - Clarios\Projects\Downtime Project\OEE MTTR MTBF.xlsx”
system.file.writeFile(filePath, spreadsheet)

you can put a timestamp into the filepath. this way your filepath is always unique and it will not overwrite the same file.

example:

filePath = “C:\Users\pjolesbr\OneDrive - Clarios\Projects\Downtime Project\OEE MTTR MTBF”+ str(system.date.now()) + “.xlsx”

result: ‘C:\Users\pjolesbr\OneDrive - Clarios\Projects\Downtime Project\OEE MTTR MTBFThu Jul 28 09:43:12 CEST 2022.xlsx’

in addition you can format system.date.now() to whatever format works for you.

Thank you I figured this out but I kept getting errors. Turns out I didn’t think about how you cannot have colons in filenames. I reformatted the date using periods instead of colons and dashes instead of slashes and it worked!

1 Like