SSRS - MERGE external excel Documents with Generated report?

Question

I've got a report requirement that is:

1. Have 3 worksheets that is data pulled from a SQL database (easy.. done)
2. Find an Excel document on the network, and copy all the worksheets into this report (??)

I've been searching and the only thing I could find was to use EXCEL as a datasource.. where you would then select * from sheetname. That doesn't work for me.

What I need to do, is go to those files, and copy those worksheets EXACTLY how they are. Formatting and everything. They're actually surveys and forms which the users update on a constant basis.. adding and removing columns and rows constantly. And they're static, so I just want to copy those sheets and paste them into this report to combine with the data I pulled from the SQL database.

I also won't know how many sheets there are either.. as they could go from 1 to 20.. it has to be entirely dynamic.

I can do this kind of thing in an Excel Macro, but that doesn't help as the users want this all done in one single step. And I'm hoping that SSRS can do that..

I guess I need SSRS to MERGE other excel documents with the generated report..

is this possible?

Answer 1

No. You may, however, be able to set up an Excel macro that queries the database as well as reading in the existing sheets.