Push or Pull to Excel for reporting data

Question

I am unsure which is the best way to go here.

I have a third party Excel 2003 spreadsheet that needs to be filled in on a monthly basis and emailed.

Currently it is a manual process and I am in the process of automating the generation of the spreadsheet.

I have been throwing around different ideas of how to get the data into the spreadsheet.

1. I have thought of using SSRS to create a report that is in a similar format and get the user to cut and past.

2. Alternatively writing a VBA addin that retrieves that data from a webservice and then adds the data to the spreadsheet.

3. Or using the third party spreadsheet as a template and open it on the server via oledb and adding the data then serving it as a downloadable file.

Which is better or are the better solutions out there?

Answer 1

Personally for me I would try to get the users used to having a “proper” report i.e. one made in SSRS/business objects etc. If the users are still adamant they want it in excel for what ever reason then I would go with pulling the data rather than pushing it.

My reason is that if the report needed to be changed then all the code changes would be contained within the report and not have some changed on the applications side and some on the reports side.

Answer 2

SSRS has an option to store the report as a spreadsheet - why not go with that option? Then you could schedule the task in SSRS to generate the report in xls format and email it to your distribution list.

Answer 3

Due the nature of the requriements of the project, in the end, I went with an Excel Add-In that retrieves that necessary data and puts it in the cells as necessary.

I will probably also build an ssrs report as a complement.