Looking for best method to automate monthly Excel report

[Eric Bragas]

Hi everybody,

On the 4th of every month, I need to provide an Excel file to several
users. Unfortunately, I'm doing this the manual way: I run the query
(with current date parameters) in query analyzer, get results in text,
copy text to Excel, remove the "xx lines affected", and do some simple
formatting. Then I go to sheet2, insert a pivot table, and point the
source of that table to sheet1 (all rows). Then I save the file
locally and send it to a group of users.

This happens every month and I'm getting tired of it! There must be a
better way.

Can somebody please advise me on the best way to do this? I can write
T-SQL, VBA, VBScript...it really doesn't matter. I'm just looking for
a good way to do this. It doesn't have to happen automatically/on a
schedule; it's feasible to let the user trigger the process. I just
want to be removed from the process entirely.

Any ideas?

Thanks,
Eric

 

[Barb Reinhardt]

This is a fairy broad request. Why don't you break it down and we can help
from there.

 

[Mike Fogleman]

Eric, I'm sure most of this can be done with VBA from a Master WorkBook or a
template. You would probably need 3 sheets, one for the data, one for the
Pivot Table, and one for the mailing list which could be hidden. The Pivot
Table needs to be created only once and just pointed to the new data
dynamically and refreshed. Excel can do database queries, so depending on
where your raw data is would govern how Excel would get it.We would need
more specific info about that. Once you have the data, the cleanup and
formatting would be done through code to make it "Pivot Table Friendly",
update the table, and send it to your email list.
Ron DeBruin has an EXCELlent site about emailing from Excel.
http://www.rondebruin.nl/tips.htm.
Is this what you were expecting? It all seems doable and well worth the
effort you will need to put in to it up front. We are here to help you get
what you want, but we won't do it all for you. There are people who get paid
to do that.

Mike F

 

[Eric Bragas]

Barb,

Thanks for your reply. I deliberately didn't break it down because I
wanted to find out how others might do it. We've decided to create a
view on SQL Server and have SQL Server export data based on a query
from a web page, so users will download the file when they need it as
opposed to me sending it. I'll probably create a macro to generate
the users' pivot table, because I'm pretty sure SQL Server can't do
that.

Thanks,
Eric

 

[Eric Bragas]

Hi Mike,

Your idea was my original idea--just coding the whole thing in an
Excel template. But in fact a better way to do it would be to create
a stored procedure on the SQL Server that takes the parameters and
does the query itself and just passes data back out. I like the Excel
way--it sounds fun, as I am a VB developer but don't have extensive
experience in Excel development--but it unfortunately won't be the
fastest way, and doesn't give the other DBA's an opportunity to
maintain it.

Thanks for your input. You definitely answered my question, and I'll
probably be using your suggested method in the near future on other
projects.

Sincerely,
Eric

 

[Mike Fogleman]

Barb, did you just call Tinker Bell a broad?

This is a fairy broad request. Why don't you break it down and we can
help
from there.