We have LotusScript that writes out assorted data from one or more documents to an Excel spreadsheet. Nothing really fancy. And once the spreadsheet is completed, the code turns over control of the spreadsheet over to the user.
Everything used to work great. But for some users, lately, this process of writing out to the spreadsheet has been taking a long time. And what’s even stranger is that when the user gains control of the spreadsheet, scrolling around in the spreadsheet is painful. If you were to hold down the mouse on the down elevator arrow to scroll through the rows, the spreadsheet scrolls at a rate of about one row per second! It used to all just go scrolling by in a whoosh! If the user saves the spreadsheet, closes, and then opens the spreadsheet directly (double-click or fire up Excel and then do File - Open), the spreadsheet scrolling behaves normally (fast) when you scroll.
I cannot duplicate the slow output to the spreadsheet or the sluggish scrolling problem on my workstation. What takes me 9 seconds to process is taking some users upwards of 50 seconds to generate (and on the same documents that I’m pointing to).
Any thoughts from anyone as to what I can try?
Thanks in advance for suggestions.
Subject: RE: LotusScript writing to Excel output slow
Since the slowness is occurring in Excel even after your code is done running, the source of the problem would seem to lie in Excel, not in Notes. You might therefore inquire in Microsoft forums.
Perhaps these users have an Excel macro virus that’s using up most of their CPUs? If you delete parts of the data, does the spreadsheet speed up? Do they have more data in their spreadsheets than other users? If they save, exit Excel and reopen, is it faster?
Subject: RE: LotusScript writing to Excel output slow
Thank you, Andre for chiming in!
If the users save, exit Excel, and reopen, the spreadsheet behaves normally (it is fast to scroll, jump, etc.).
Some users who run the code to export the same data to Excel do not experience the slowness (I’m one such person). But others do.
It crossed my mine that perhaps a virus or something is involved, but these workstations should be up to date with antivirus definition files.
I have not inquired with the Microsoft forums, but I think I’ll post something there (even though I think the responses are going to be “it’s not Excel’s problem”).
Subject: RE: LotusScript writing to Excel output slow
Did you get anywhere with this? I hve just spent a day trying to work out why some complicated code that produces multi-sheet tables with lots of formatting merged cells etc. was running like a dog when it had been running fine when I started created it. Stripped it all down and tracked it to setting the page settings at the beginning of the report… moved them to the end and turned off display of page breaks and probably 20+times the speed. Amazing!
http://support.microsoft.com/kb/199505
Above MS document explains some of the issues.
Steve
Subject: RE: LotusScript writing to Excel output slow
Interesting Steve, I’ll have to keep this info in mind for the future. Our output was pretty slow right from the get-go (you can see this when the spreadsheet is visible while the data is being written out), but it got really really slow towards the end (where most of what we were doing were “hardcoded” population of specific cells, such as sheet.Cells(9, 1) = "Account Number: " & doc.CustomerID(0). We don’t have any page setup code in this particular code, but I think I will go back in there and add some to disable the page break settings. We have code that also writes out Microsoft Word documents and we find that the output is somewhat faster if we turn off Spell Check, Grammar Check, and Check Spelling As You Type. Plus (and I can’t explain this one), some users do better if word.visible = False.
Anyhow, our Excel code problem solution was a Windows solution. After we did the usual defrag and disk cleanup, we renamed the users Windows profile directory (renamed C:\Documents and Settings\username to C:\Documents and Settings\usernameold) and copied most of the contents from the old directory over into the new. I don’t fully understand why it worked, but that’s what our laptop administrators suggested after I couldn’t nail down a good solution in Lotus Notes. Now the Excel output is fast as before and the user can scroll up and down in the finished spreadsheet before they do any saving of the Excel file. But I fear over time that we’ll have to go through this exercise again for the users.