I know exactly what you're going through. I've an application that has over 10,600 lines of vba code.
it refreshes from 3 different data sources. I've got the refresh time reduced from over 3 hours to an hour and 12 minutes. During this time, Excel is unavailable and if I try to use other applications, it sometimes extends the time for the refresh. What I did was keep a copy of the file in a folder called: ../../../Batch/ Then, I have a workbook_Open event that looks at the path name of the file. If it includes "/Batch/" then it executes the refresh and exits. I then created a Windows Scheduled Task and have it open the file at 4:00 in the morning. That way, it is updated by 5:30am every day. Of course, that means that I have to leave my workstation logged in (but locked) at night. I've considered writing the whole refresh script into a VBScript session. I THINK I can run this in a Scheduled Task without being logged in, since it doesn't open up a Windows application. Then, I could simply read from a text file and update Excel... either of these approaches sound promising? Paul ________________________________ From: Holsten <cholma...@gmail.com> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> Sent: Wednesday, October 21, 2009 12:56:48 AM Subject: $$Excel-Macros$$ Run Excel Macro? 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 -~----------~----~----~----~------~----~------~--~---
