first suggestion, create two instances of excel. have one do your macros, the other you can use freely.
maybe your concern is not with excel, but the weight of your SQL queries. if you're aggregating a large amount of data, the time it takes your DB server to process the query will freeze your excel processes until the data is retrieved. as such, if your DB server takes X amount of time, your excel will be frozen for the same X amount of time, plus the time it takes to bring back the aggregated data from the server to your excel sheet. if this is the case, maybe think about pre-aggregating your raw data. perhaps a datepart into constituent days, instead of each transactional row (assuming you're dealing with transaction data). look into SELECT INTO statements. also, i'm not sure using access anymore is the best idea, especially if you're dealing with mission critical data. sql 2005/2008 is readily available, often for free. if you delve into the many functionality of sql server, report automation is included as a standard package. of course, this come with a bit of a learning curve. hope that helps. On Oct 20, 9:56 pm, Holsten <cholma...@gmail.com> wrote: > Hi All, > > First let me say that I love reading through the suggestions and > examples that have been posted for other peoples problem's and have > picked up some great formulas and macros and for that I am already > thankful to you all. Now for my question. I have been searching for > a way to automate some reports that I run. I use excel 2007 and the > main problem I have is that the reports are updated bi-weekly via > refresh of external qry to an access database on my server. When I > click "refresh all" the qrys run and excel becomes unusable to me for > several minutes. I am sure that It doesn't sound that bad but I have > to repeat the process for 6-10 reports. The time adds up and I need > to find a way to report faster. > > Please tell me if I am just a dreamer but in my mind it seems like > there should be a way for me to activate the "refresh all" and email > myself the finished product without locking up my computer or excel > for 2 hours. I do have a macro to run the refresh then email me the > product but again I still have the problem of excel locking up. Is > there a way for me to have the refresh (or macro to email self) run > without opening excel? I have punch and pie for anyone with a > promising suggestion. :) > > I am sure that that I have not explained details in full and I welcome > any questions. > > Thanks for your time. --~--~---------~--~----~------------~-------~--~----~ ---------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~----------~----~----~----~------~----~------~--~---
