Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help for time saving!
Hi
Sorry for my crossposting. I found this NG after I posted the question in microsoft.public.excel. I do my best next time. I have got created an Excel Script that creates a Word Document. A bill. But for only 8 bill entries the script needs to much time. For this entries it works about 30 secounds. If you want to help me look at this page: www.kluesener.info/excel.html And if you find anything that saves time please tell me how I can do it better. Sorry for my bad english Many Thanks Jens |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help for time saving!
Jens,
A couple of tips to speed up your program. 1) ScreenUpdating = False (will run faster as screen doesn't need to be updated.) 2) Visible = False (If you don't need it on screen) 3) With appWord.Application.ActiveDocument.Paragraphs(1).R ange.Font .Name = "Arial" .Size = BRIEF_FONTGROESSE_ADRESSE_KLEIN End With (All the other settings are the default values so don't need to be set unless you're changing them) There are other ways to speed it up, like changing the For Next loops to Do loops. One of the Excel MVPs (Can't remember which one, but Google will find it) has a web page dedicated to speeding up VBA. Although this is specifically for Excel, most of the recommendations will apply to Word VBA as well. HTH Henry "Jens 'Kluesi' Kluesener" wrote in message ... Hi Sorry for my crossposting. I found this NG after I posted the question in microsoft.public.excel. I do my best next time. I have got created an Excel Script that creates a Word Document. A bill. But for only 8 bill entries the script needs to much time. For this entries it works about 30 secounds. If you want to help me look at this page: www.kluesener.info/excel.html And if you find anything that saves time please tell me how I can do it better. Sorry for my bad english Many Thanks Jens |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help for time saving!
Hi Jens,
Item #2 of Henry's post is eliminating the code you don't need. Page Setup type of things can be very time consuming and if you don't need to change something then don't. Same for your fonts. You probably got all the extra code from the macro recorder. Turning off Calculation would probably help quite a bit, but then I don't call Word from Excel so you would have to be extra careful because I can't warn you of what not to do. But when you are finished you need to have restored Screen Updating and Calculation before you finish, an be especially mindful of abnormal termination. Print Preview can turn on page break mode and that is more time consuming. Don't know if you have that on or not, but you might want to make sure it is off. Especially if you have to change things like your fonts, margins.. You can save current status of Screen Updating and Calculation in variables, and turn them off, then restore their original settings when finished. Just so that you can post snippets of code in newsgroups, I would try to reduce line lengths to about 72 bytes to avoid wrapping when you or someone else displays your code in a newsgroup. . For more general notes on Excel only: More information in the following Slow Response and Memory Problems http://www.mvps.org/dmcritchie/excel/slowresp.htm and also use the links to Charles Williams site, or : Decision Models - Excel Pages - Calculation Secrets, Downloads and Tips (it is a lot easier to use Mozilla at this site than Internet Explorer) http://www.decisionmodels.com/optspeed.htm You will probably gain a lot more by turning off calculation. Between turning off screen updating and turning off calculation you can make bad code run faster than otherwise good code. More information on processing only the cells you want to process by using SpecialCells see Proper, and other Text changes -- Use of SpecialCells http://www.mvps.org/dmcritchie/excel/proper.htm HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Henry" wrote in message ... Jens, A couple of tips to speed up your program. 1) ScreenUpdating = False (will run faster as screen doesn't need to be updated.) 2) Visible = False (If you don't need it on screen) 3) With appWord.Application.ActiveDocument.Paragraphs(1).R ange.Font .Name = "Arial" .Size = BRIEF_FONTGROESSE_ADRESSE_KLEIN End With (All the other settings are the default values so don't need to be set unless you're changing them) There are other ways to speed it up, like changing the For Next loops to Do loops. One of the Excel MVPs (Can't remember which one, but Google will find it) has a web page dedicated to speeding up VBA. Although this is specifically for Excel, most of the recommendations will apply to Word VBA as well. HTH Henry "Jens 'Kluesi' Kluesener" wrote in message ... Hi Sorry for my crossposting. I found this NG after I posted the question in microsoft.public.excel. I do my best next time. I have got created an Excel Script that creates a Word Document. A bill. But for only 8 bill entries the script needs to much time. For this entries it works about 30 secounds. If you want to help me look at this page: www.kluesener.info/excel.html And if you find anything that saves time please tell me how I can do it better. Sorry for my bad english Many Thanks Jens |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving changes to option to apply all the time. | Excel Discussion (Misc queries) | |||
automatically put date and time on the cells when saving ? | New Users to Excel | |||
Saving in 2007 and 2003 formats at the same time | Excel Discussion (Misc queries) | |||
Saving files in two different formats at the same time | Excel Discussion (Misc queries) | |||
Saving a time stamp with a macro? | Excel Programming |