CODEWITHSUNDEEP
asp.netvb.netexcel
Rachel
Rachel

Reputation: 132558

Writing to Excel file containing formulas is extremely slow

We have an automatic process that opens a template excel file, writes rows of data, and returns the file to the user. This process is usually fast, however I was recently asked to add a summary page with some Excel formulas to one of the templates, and now the process takes forever.

It successfully runs with about 5 records after a few minutes, however this week's record set is almost 400 rows and the longest I've let it run is about half an hour before cancelling it. Without the formulas, it only takes a few seconds to run.

Is there any known issues with writing rows to an Excel file that contains formulas? Or is there a way to tell Excel not to evaluate formulas until the file is opened by a user?

The formulas on the summary Sheet are these:

' Returns count of cells in column where data = Y
=COUNTIF(Sheet1!J15:Sheet1!J10000, "Y") 
=COUNTIF(Sheet1!F15:Sheet1!F10000, "Y")

' Return sum of column where data is a number greater than 0
' Column contains formula calculating the difference in months between two dates
=SUMIF(Sheet1!I15:Sheet1!I10000,">0",Sheet1!I15:Sheet1!I10000)  

' Returns a count of distinct values in a column
=SUMPRODUCT((Sheet1!D15:Sheet1!D10000<>"")/COUNTIF(Sheet1!D15:Sheet1!D10000,Sheet1!D15:Sheet1!D10000&""))

And the code that writes to excel looks something like this:

Dim xls as New Excel.Application()
Dim xlsBooks as Excel.Workbooks, xlsBook as Excel.Workbook
Dim xlsSheets as Excel.Sheets, xlsSheet as Excel.Worksheet
Dim xlsCells as Excel.Range

xls.Visible = False
xls.DisplayAlerts = False

xlsBooks = xls.Workbooks

xlsBooks.Open(templateFile)
xlsBook = xlsBooks.Item(1)  

' Loop through excel Sheets. Some templates have multiple sheets.
For Each drSheet as DataRow in dtSheets.Rows
    xlsSheets = xlsBook.Worksheets
    xlsSheet = CType(xlsSheets.Item(drSheet("SheetName")), Excel.Worksheet)
    xlsCells = xlsSheet.Cells

    ' Loop though Column list from Database. Each Template requires different columns
    For Each drDataCols as DataRow in dtDataCols.Rows

        ' Loop though Rows to get data
        For Each drData as DataRow in dtData.Rows
            xlsCells(drSheet("StartRow") + dtData.Rows.IndexOf(drData), drDataCols("DataColumn")) = drData("Col" + drDataCols("DataColumn").toString).toString
        Next
    Next
Next

xlsSheet.SaveAs(newFile)
xlsBook.Close
xls.Quit()

Upvotes: 2

Views: 1712

Answers (3)

KV Prajapati
KV Prajapati

Reputation: 94645

With auto mode calculation, recalculation occurs after every data input/changed. I had the same problem, was solved by setting Manual calculation mode. (Reference MSDN link.)

xls.Calculation = Excel.XlCalculation.xlCalculationManual

Also, this property can only be set after a Workbook has been opened or it will throw a run-time error.

Upvotes: 1

Charles Williams
Charles Williams

Reputation: 23520

Every time you write to a cell Excel recalculates the open workbooks and refreshes the screen. Both of these things are slow, so you need to set Application.Screenupdating=false and Application.Calculation=xlCalculationManual

Also there is a high overhead associated with each write to a cell, so it is much faster to acuumulate the data in an array and then write the array to the range with a single call to the Excel object model.

Upvotes: 4

joshua9k
joshua9k

Reputation: 179

One way that has saved me over the years is to add 

Application.ScreenUpdating = False

directly before I execute a potentially lengthy method, and then

Application.ScreenUpdating = True

directly after, or at least at some later point in the code. This forces Excel to not redraw anything on the visible screen until it is complete That issue is where I've found lengthy running operations to stem from quite often.

Upvotes: 0

Related Questions