Question

Adding new sheet to Excel daily

  • 9 August 2022
  • 3 replies
  • 6 views

Is there a way to make a bot add a new Excel sheet daily? What I'm trying to do is have the bot read results from SQL and paste the results into Excel.. but I don't want to have it overwrite the old data as this will run each day. I was thinking it'd be easiest to have it just add a new sheet after it gets the SQL results, but I'm not seeing that command under the Excel options.

 

The only other thing I can think of is to have it paste under the previous day's results, but I'm not entirely sure how to make it know where the new "Active Cell" should be each day. This is something basic I wrote for a different bot, maybe there's a way to modify it?

 

image


3 replies

You can use "Excel Advanced" action module instead of "Excel" module, as there is "Create worksheet" command. And after you create the sheet and update the cells for the day, you can also use "rename worksheet" command in "Excel Advanced" with #system date variable , so that BOT can create different sheet for each day when you run the BOT.

 

Yeah that does sound like it'd work.. just doesn't look like I have Excel Advanced on my version.

 

Would you know about the other option I mentioned, getting it to paste results 1 row down in excel each day? ie if day 1 excel has 10 rows filled out, day 2 should paste results starting at row 11.. i can get it to do that normally, but not really sure about getting it to move on subsequent runs

Userlevel 3
Badge +7

Hi @Phillip Graves​ ,

 

I wouldn't recommend creating sheets each day, as opening that Excel file would consume a lot of memory.

What you could do instead is create CSV files which are static lightweight files each day.

 

You can also program the bot to delete files older than say 2-3 months if required.

 

Kind Regards,

Ashwin A.K

Reply