Execute Excel macro when exporting in SSRS

We have a report that users typically export to Excel. I've created
a
macro in Excel that I run against the the exported report to do some
follow up work. Unfortunately, this requires that I export the
report
myself, run the macro and forward on to those interested.
I'm looking for a way to execute the macro automatically every time
someone exports to Excel (or sets up a subscription that sends Excel
output). Any ideas?
If there is a way to do this with a custom DLL, etc. we would be
interested in hiring a consultant to complete the task.
Thanks for you help
IANOn Oct 2, 8:48 pm, IAN <iani4pro...@.gmail.com> wrote:
> We have a report that users typically export to Excel. I've created
> a
> macro in Excel that I run against the the exported report to do some
> follow up work. Unfortunately, this requires that I export the
> report
> myself, run the macro and forward on to those interested.
> I'm looking for a way to execute the macro automatically every time
> someone exports to Excel (or sets up a subscription that sends Excel
> output). Any ideas?
> If there is a way to do this with a custom DLL, etc. we would be
> interested in hiring a consultant to complete the task.
> Thanks for you help
> IAN
In terms of exporting the report to excel via subscription, if you are
not aware, this is built into the Report Mgr. Also, a report can be
exported to Excel via URL. Here's an example:
http://ServerName/reportserver?/DirectoryNameWhereApplicable/ReportName&rs:Command=Render&Param1=ParamValue&rs:Format=Excel
This opens an export dialog that defaults to the Excel format. Another
option that can most likely be used in conjunction w/Excel
manipulations (known as Automation) is utilizing the web service
available w/SSRS and the Report Server. An example link is found here:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||If you're talking about using the standard interface, I'm pretty sure the
answer is no. It generates a new copy of the file and that's it. Of course,
you could always build your own aspx page that generates the xls file and
then uses the excel object model to do whatever you want. An option for
subscriptions would be to send the xls file to a share, and have a .net app
that is looking at that folder (filesystemwatcher) and runs whatever code
you need when a new xls file shows up in the directory.
Mike G.
"IAN" <iani4profit@.gmail.com> wrote in message
news:1191376082.251031.190910@.n39g2000hsh.googlegroups.com...
> We have a report that users typically export to Excel. I've created
> a
> macro in Excel that I run against the the exported report to do some
> follow up work. Unfortunately, this requires that I export the
> report
> myself, run the macro and forward on to those interested.
> I'm looking for a way to execute the macro automatically every time
> someone exports to Excel (or sets up a subscription that sends Excel
> output). Any ideas?
>
> If there is a way to do this with a custom DLL, etc. we would be
> interested in hiring a consultant to complete the task.
>
> Thanks for you help
>
> IAN
>