Use VBA (Excel) to pass a parameter to SSRS report

Question

I want to itterate through an Excel column and use the value in each cell as a parameter to be passed into an SSRS report.  I can hancle the vba code to manage the looping but need to understand how to use the cell value to call SSRS.  In addition, I want to create the report as a PDF file and save it to a folder on a network share or my hard drive.  I do not need the report to be rendered to the screen.  Just create and save to a file.  Each file must have a unique name where the parameter passed is the portionof the name that makes it unique.  Any ideas on how this could be done?  Example code for calling SSRS and saving the file as a PDF would be appreciated.

 

---

Robert Stroud

Answer 1

Hi Robert,

Using SSRS in VBA is not possible (at least there is no easy way to do this) as far as I know. You would have to consider using VSTO solution instead.

If you are working on a particular workbook, you can create a VSTO document solution, or you can create an Application-level add-in for all workbooks.

For how to export Excel to SSRS by using .NET code, you can refer to this document:

http://www.tek-tips.com/faqs.cfm?fid=5918

and

http://msdn.microsoft.com/en-us/library/ms159238.aspx

I hope this helps.

---

Best Regards, Calvin Gao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.