How to Automate and Schedule Microsoft Excel Workbooks

Microsoft Excel is one of the oldest and most powerful peaces of software ever written. In fact, there are more than 1 billion users of Microsoft Office to date and a good number of those use Microsoft Excel. Lots of businesses also utilize Microsoft Excel on a daily basic to store data as well to carry out data analysis.Microsoft Excel is very versatile in that it can connect to all kinds of datasources (directly and via ODBC) to pull in data and this data is refreshed every time the Microsoft Excel Workbook is opened. This has gotten even more powerful with the recent introduction of Microsoft Power Query and Microsoft PowerPivot.A typical usage scenario for Microsoft Excel in organizations is to have Microsoft Excel Workbooks that have one or more datasource connections and the data is formatted in one form or another. The Microsoft Excel user typically uses these Excel Workbooks to create "reports" that can then be shared with other people in the organization. They also in some instances utilize Excel Macros to calculate and slice the data in the Workbook.We decided to take the above scenario and automate it remiCrystal. Once the Microsoft Excel Workbook is set up with the database connections, data formatting and if necessary Microsoft Excel Macros, remiCrystal can take on the task of refreshing the data, running the Macros, exporting a single Microsoft Excel Worksheet or the entire Microsoft Excel Workbook to other formats such as PDF, Word, RTF, Excel and many more. Once the export is done, remiCrystal can then deliver this output to your Network drive, Email, Printer, FTP, SharePoint or even Dropbox.In this post we are going to show you how to achieve the above.

1. Start by opening remiCrystal and give your remiCrystal task a name and description
2. Go to the Reports & Actions tab and press the Excel Workbook button
3. In the Open dialog, select your Microsoft Excel Workbook and open it
4. If your Workbook is password protected, you may enter the password and then press the load button (blue arrow pointing right) 
5. To have remiCrystal run Macros for you, go the Macros tab and pick the ones you would like executed from the dropdown list 
6. Your datasources will be shown in Connections tab and you can specify their credentials if required 
7. You can populate any cell in any worksheet by using the control provided in the Cell Values tab 
8. Last but not least, if you would like your Excel Workbook's worksheet exported to a different format, you can set this up in the Output Format tab

As you can see, automating and scheduling Microsoft Excel with remiCrystal is a straight forward affair. If you are a medium to heavy user of Microsoft Excel, we believe automating your Excel reports is a very valuable proposition and we are proud to bring this feature to remiCrystal .If you haven't already, you can download remiCrystal here.