Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for best method to automate monthly Excel report
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for best method to automate monthly Excel report
This is a fairy broad request. Why don't you break it down and we can help
from there. "Eric Bragas" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for best method to automate monthly Excel report
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" wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for best method to automate monthly Excel report
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for best method to automate monthly Excel report
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to automate a report based in several Workbooks | Links and Linking in Excel | |||
Monthly Report | Excel Programming | |||
Automate Excel report to place certain data into existing report? | Excel Worksheet Functions | |||
How do I set up a rolling monthly report in Excel? | Excel Discussion (Misc queries) | |||
Monthly Report | Excel Discussion (Misc queries) |