 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SHARING] Why are Excel Macros so slow?
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SHARING] Why are Excel Macros so slow? Read-Only TopicGo | Search | Notify | Tools |
| [SHARING] Why are Excel Macros so slow? | Login/Join |
| Master |
We are using wf to fill excel templates with macros in them. My colleague has created a macro that sets up margins, adjusts print area, sets column heights and does a bit of formatting and then self deletes the macros. The spreadsheets are small: Roughly 5 worksheets, 30 columns, max 100 rows. Yet this takes 30 seconds to run. I did a bit of searching for obvious stuff like this and he's incorporated it already. http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm The guy is an experienced coder so I dont expect him to do anything crazy and he is pre counting the extent of his sheet so he doesn't go and process 65,536 rows or whatever the limit is. Are Excel Macros just slow or is there anything we can do to speed things up. The prob is that we need macros to run for 100 spreadsheets and so the whole process is taking about 50 minutes. Help! Regards JohnThis message has been edited. Last edited by: Kerry, Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2 Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo | ||
|
| <JG> |
This really is an Excel issue, You should post it to an Excel forum. | ||
|
| Master |
What you're experiencing is a very common VBA issue related to printing. When you "record" an excel macro (which is never the most efficient way to run.) it records all kinds of page setup info that YOU DON NOT NEED. When you pop up the print settings window, that is outside of MS EXCEL and is actually a call to the printing DLL from within Excel when you print, so excel records all the DEFAULT print info as well because it wants to specify all those values when you go to print, this way it will be the same every time.... Feel free to PM me with your VBA and I'll clean it up for you, or just go through and start deleting lines that look unimportant... usually if it doesn't look necessary, it ain't. Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database. | |||
|
| Virtuoso |
Some years ago I created a VBA script in Access that created 45 spreadsheets with 5 pages each from the access database and linked tables on MS-SQL. It took all 2 or 3 minutes to run (on a server). Memory is very important, but there are many ways to speed up a script. What do you see on screen if you run the script?
| |||||||
|
| Expert |
Jason, Just for info, the code was not recorded (the very thought), it was hand coded and only has required settings within it. Frank, The issue is when opening the Excel workbook after it has been created within WF. For info the WF batch process creates roughly 95 workbooks with 5 worksheets in them and takes around 65 - 90 seconds depending upon server load. Care is taken to turn off screen updates but as there are no formuale the autocalculation is not turned off - we will be trying that next. The workbooks are currently being auto processed within a VB Script so the time lost during manual processing is regained but the process still takes about 15 to 20 seconds per workbook. Incidently, I am fairly OK with the timing as it is very close to the previous DTS and VBS package that it replaces, John was just asking if anyone had managed to get a significant improvement.  T
| |||||||||
|
| Master |
Folks To follow on from what Tony was saying, it seems peculiar that all these ss's that take seconds to produce in wf take nearly an hour to format. Is everyone's experience of excel macros that they are this slow? If so does anyone know what they are doing to get this slow? At first I thought it would be the undo facility causing multiple copies of the sheet being placed into memory but microsoft have already thought of this and disabled the undo facility - indeed you cannot undo what you did before you ran the macro. Any ideas experiences etc We are office 2003. Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2 Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo | |||
|
| Master |
VBA is kinda like checkers. It only takes a second to learn the concepts, but there's a lot of nuances that make you good at it. I've written many VBA scripts that took hours to run, only to come back to to them later and use better knowledge to write a much better macro. Doesn't matter what version of ms Office you're running, there's a hundred ways to accomplish one thing in VBA. Feel free to send me the macro in question, sometimes a second set of eyes can help out. Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database. | |||
|
| Expert |
Sharing a solution to an old problem A small addendum to this as it is something I encountered just recently when the batch processing of Excel template outputs started to take a lot longer than usual (1 every 3-5 minutes instead of 4 per minute). I worked the problem down to the time taken to process all the page settings required (only change to the defaults were coded). I couldn't understand the cause at first but looking at the hand cranked method it became apparent that Excel is accessing the default printer dialog for each change made - per Jason's post above (VBA post version 4 has one change one call  ).A new default printer had recently been added (RDP seems to add that of the incoming users machine!). Changing the default printer to "Microsoft Office Document Image Writer" saw a sudden increase in processing speed. The processes are now churning through at 15 - 20 per minute instead of the previous 4 per minute.This message has been edited. Last edited by: Tony A,
| |||||||||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SHARING] Why are Excel Macros so slow?
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SHARING] Why are Excel Macros so slow?Copyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |