Wow! Some great suggestions! I read through these on my way to the office this morning and had hoped to be able to try some but I was swamped on another project almost all day. I can't express enough how thankful I am to have found an outlet for my curiosity. It seems like there is always something new to learn or read about and there are always solid suggestions here in this community.
First @ Paul: I am very excited about your suggestion of utilizing Windows Task Scheduler. My raw data is automatically downloaded overnight so if I am able to follow your directions that might work. My only fear on that is that the data may not finish downloading before the event would need to be scheduled. It also sounds like I would also have to stack several consecutive events taking care to allow enough time for the previous one to complete.(because of the excel application freezing up during refresh) I am not completely sure that understand how to employ WTS to open Excel though... I need to read your directions closer. @ Hemant What a wonderfully simple idea. If that is all it takes I will literally slap myself and I intend to test the [windows] +r in the morning. One question I have is how would I keep an excel instance invisible? Do you mean minimized?(sorry if that is a dumb question). I will send you a copy of the refresh and email macro, although it does currently depend on opening outlook. Unless I am mistaken you would have to ftp the file to your host... or maybe you plan to use it as .ASP? Let me know and I will try a couple of things or I can send "as is". I also have a "save as" line in the macro to add a date stamp for each refresh but you can take that part out if you like. Again just let me know if you are still interested. @ Jef Good idea however when the new data is downloaded it is automatically linked to the DB because it always saves over the old table. The data is spun down through several qrys to make the 3 small tables I use to refresh the reports. It would likely take about as long to manually run access and paste into Excel. @ Joe I have no doubt that you are correct about the weight of the Access DB exceeding normal operating use or expectation. I would love to adopt SQL into our company but have met resistance. The advice was that I could experiment with the free version but after reading that it would not include many of the report automation features that the enterprise edition would include I did not try it. I am also interested in the dash-boarding and BI capabilities available with sql server and perhaps I should experiment enough to perk interest. Again thank you all for the time and though put into this. It feels good to know that I am not totally crazy and that other people have gone though the same experience, or are at least thinking about it. On Oct 22, 10:39 am, "Joe C." <jsk.c...@gmail.com> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
