0

The problem that I am experiencing is that the For Each ws In xlWorkbook_.Worksheets statement is taking excessively long to process. With only 7 worksheets it is taking 23 seconds to process the one statement. The template file that I am using is an .xlsx and all previous reports that I have written using ExcelWriter have used a .xls template file. That is the only real difference that I can see. Using the .xls templates with the same approach the statement will process sub-second. I am using the latest version of OfficeWriter (8.6.1) and 2010 version of Excel.

Has anyone else had this issue or have an alternate way of looping through each sheet in the workbook that would perform significantly faster?

The code I am using looks effectively like this:

Dim xlApp_ As ExcelApplication
Dim xlWorkbook_ As Workbook
Dim xlTemplate_ As ExcelTemplate
Dim ws As Worksheet

....
Code to initially populate the data using the ExcelTemplate
  - copies single sheet to x number of additional sheets depending on how many are needed
  - renames the datamarkers on the sheets that were added and binds the datasets to the datamarkers
  - processes the template to populate all of the sheets with data
....

xlWorkbook_ = xlApp_.Open(xlTemplate_)

For Each ws In xlWorkbook_.Worksheets
  ....
  code to change worksheet name and format various parts of the worksheet
  ....
Next
user3113086
  • 1
  • It could be that the casting to `Worksheet` inside the enumeration is slow. Try looping by sheet index/number. – Bjørn-Roger Kringsjå Dec 18 '13 at 18:09
  • Comment out all the code inside the For/Next loop. If it is now very fast then you've mis-identified the source of the slow-down. – Hans Passant Dec 18 '13 at 19:05
  • @Bjørn-RogerKringsjå I tried looping by sheet index/number. It took the same amount of time when I made a call to get the sheet count for the loop. Seems like the first call to xlWorkbook_.Worksheets is what is taking all of the time. – user3113086 Dec 19 '13 at 16:25
  • @HansPassant I am single stepping through the code in the UI. It is a significant wait while processing that single line of the code. By the way, I already tried commenting out the code inside of the loop in tracking down the line of code taking so much time. – user3113086 Dec 19 '13 at 16:29
  • Debugger stepping speed is affected by things that have nothing at all do with how efficient your code runs. It is a common mistake to press F11 when you really meant F10. The debugger will try to accommodate that by trying to skip code that is not "Just My Code" but that takes a while. – Hans Passant Dec 19 '13 at 16:39
  • Do these sheets have a huge amount or data or thousands of formulas? That can make parsing them take longer – Aviva M. Dec 23 '13 at 22:31
  • The sheets range in rows from 270 to about 2000. There are 7 columns with formulas, repeated for each group footer defined in the template. The total number of Formulas in the workbook are 777. – user3113086 Dec 26 '13 at 20:27
  • I tried removing all of the formulas and just adding them back in within code inside of the "for each" loop. No change in the performance at all... – user3113086 Dec 26 '13 at 20:38
  • Here is some additional information: The Template file is using the %%group on 6 columns to perform nested grouping on the data. The detail row within the grouping has conditional formatting defined to change the background color of the detail records (alternating between 2 colors for each of the records based on whether it is an odd or even row) – user3113086 Dec 26 '13 at 20:47
  • This issue is being handled off-line through SoftArtisans technical support. We will update the thread when there is new information. – Aviva M. Jan 10 '14 at 15:01

0 Answers0