How to Automate Excel Reports

Do you ever feel like you’re spending more time creating reports than you’re spending analyzing and acting on your reports? With robotic process automation (RPA), it’s simple to set up an Excel automation task that makes report generation much easier. By taking out the tedious, manual work of transferring data from your databases to Excel, RPA software gives you back your time to instead spend it making critical business decisions based on the data in your reports. Here’s how:

Choose Your Excel Automation Software

Want to follow along but don't have an automation tool? Try it yourself with a free, instant download of an Automate trial. Automate is a powerful RPA solution that streamlines your most tedious, manual processes from Excel and beyond.

How to Automate Excel Reports

For this example, we’re going to show you how to create a task to access an SQL database and write the results to a Microsoft Excel spreadsheet. With Automate’s simple drag-and-drop actions, you can create your Excel report automation task in under 10 minutes.

Follow along yourself as Pat Cameron, Director of Automation, gives you a guided tutorial through the steps to set your Excel automation task up.

1. Download Automate for free
2. Connect to the database using the Database Action. There's a number of ways that you can do that, you can make a database connection and then build that connection in the task, select the driver that you want to use, the server, etc. You can also use a connection string, enter that into the box or use a data link file.
3. Execute the stored procedure. You have two options when you make this connection, you can either create a SQL query here or run a stored procedure. If you need to add or pass any parameters to this stored procedure when it runs, enter them here. You can enter the name, the value, the data type, and select from the box. Then for the direction, input or output.
4. Write the results of this stored procedure to a data set, ds_cust.
5. Close the connection, as you don't need to have that open while writing to the spreadsheet, since it’s done from the data set.
6. Run the first steps, and then take a look at the data that's in the ds_cust data set just to make sure that you've got the right records that you want to write.
7. Find the Excel Action, create a new spreadsheet. You can select the location where you want the spreadsheet to be.
8. Select the Set Cells Action from Excel to set the value of the cells in your worksheet from the data set. Run this task and it will make the connection, run the stored procedure, close up the connection, and then it will create a spreadsheet and write the contents of the spreadsheet, or the contents of the data base to that spreadsheet. That’s the end of the first task.
9. The second task is, a little bit different. Instead of running a stored procedure, you can write the query statements right in the task itself.
10. Create a variable and add today's date to your spreadsheet. Create a variable called today and then format it so you can find the format action, and format that date and time, what I want it to look like when I add it to the spreadsheet name. Use the same step to establish the connection.
11. Use the pre-defined connection string and execute a SQL statement. So instead of running a stored procedure, you get the actual details for the SQL statement.
12. Select statement and add that into that field. You can add as many of those as you like, and you can separate them with a semi-colon and write that data out to a data set as well.
13. Then you'll close up that connection, just like with the other one. Again, you don't need to leave that open while you're creating your spreadsheet.
14. Now if you run this and take a look at the data set to make sure you’ve got the right data, you can just right click on Data Set, select Inspect and now you can see your records.
15. Create a new spreadsheet by finding the Excel Action again, and drag the Create Option over to create a spreadsheet. Then you can see the variable for today's date on the name of that spreadsheet.
16. Write out the contents of the data set, by selecting the data set in the upper right hand corner, and include the column names here as well.
17. Close up your workbook when finished.
18. Run it and the spreadsheet gets created, the data gets written and then you can close up that spreadsheet.

That's all you need to do to access a sequel data base and then write the results onto an Excel spreadsheet for easy reporting.