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
>

Execute Excel macro after export

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
IANI'm currently working on a DLL to do exactly this. We execute a macro to
strip out hyperlinks because the excel renderer doesn't have an
OmitHyperlinks option.
Contact me if you are still interested.
Raul
"iani4profit@.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
>

Execute DTS package from ADO in VB

I'm using ADO objects in a VB Macro for Excel. I'd like to execute a DTS package located on SQLServer.

What is the syntax to do this?

Here's my current database connection code:Option Explicit

Dim db_connection As ADODB.Connection
Dim db_results As ADODB.Recordset
Dim db_error As ADODB.Error

Private Sub DB_Initialize()
Set db_connection = New ADODB.Connection
db_connection.Open "Provider='SQLOLEDB';Data Source='BACK_SQL';" & _
"Initial Catalog='my_db';Integrated Security='SSPI';"
Set db_results = New ADODB.Recordset
End SubIs it possible to use a stored procedure to execute a DTS package?

What would the stored procedure be?|||Originally posted by odinsdream
Is it possible to use a stored procedure to execute a DTS package?

What would the stored procedure be?

I don't know very much about VB but I definately know my DTS packages.. yes you can execute a DTS package via a stored procedure

server_name=server name dts package is on
user_name=login to access server
password=user's password
package_name = DTS package name
package_password=DTS package pwd

Create Procedure sp_ExecuteDTS AS

exec master.. xp_cmdshell 'dtsrun /Sserver_name /Uuser_name /Ppassword /N"package_name" /Mpackage_password'

GO
-------

if your package does not have a pwd then your sp should look like this

Create Procedure sp_ExecuteDTS AS

exec master.. xp_cmdshell 'dtsrun /Sserver_name /Uuser_name /Ppassword /N"package_name"

GO|||So how would one execute that stored procedure from VBA for Access?