The createReport method takes around 30 seconds to execute and I was wondering how I could optimize it. I'm using the Interop.Excel class and the workbook I'm importing to is very formula intensive.

Dim UserDate As New Data_Entry_Form.UserDate
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim xlSourceRange, xlDestRange As Excel.Range
Dim path As String = Application.StartupPath
Dim dt As New DataTable
Public Sub New()

End Sub
Sub createReport(dt As DataTable) ' this procedure determines the flow of the excel manipulation'
    openfile("2012 Master.xlsx")
End Sub
Sub closexl()
End Sub
Sub cleanup() ' Releases all of the com objects to object not have excel running as a process after this method finishes'
    xlApp = Nothing
End Sub
Sub debugging(mode As Boolean) 'Determines if we want to display anything during the dubugging process'
    If mode = True Then
        xlApp.DisplayAlerts = True
        xlApp.Visible = True
        xlApp.DisplayAlerts = False
        xlApp.Visible = False

    End If
End Sub
Sub putfilesIntoYTD(dt As DataTable)
    'dt = CType(dbTools.getYTD(startDate, endDate), DataTable)'
    Dim fileNum() As String = convertColumntoArray(dt, 0)
    Dim dateRecorded() As String = convertColumntoArray(dt, 1)
    Dim closerAndTypeCode() As String = convertColumntoArray(dt, 2)
    xlSourceRange = xlWorkSheet.Range("N2:N" & fileNum.Length)
    xlSourceRange.Value = fileNum
    xlSourceRange = xlWorkSheet.Range("O2:O" & dateRecorded.Length)
    xlSourceRange.Value = dateRecorded
    xlSourceRange = xlWorkSheet.Range("P2:P" & closerAndTypeCode.Length)
    xlSourceRange.Value = closerAndTypeCode

End Sub

Function convertColumntoArray(dt As DataTable, columnIndex As Integer) As String() ' Converts a single column of an datatable into an array of strings'
    Dim array(dt.Rows.Count) As String
    If columnIndex = 1 Then
        For i As Integer = 0 To dt.Rows.Count - 1
            array(i) = CDate(dt.Rows(i)(columnIndex).ToString()).Date
        For i As Integer = 0 To dt.Rows.Count - 1
            array(i) = dt.Rows(i)(columnIndex).ToString()
    End If
    Return array
End Function

Sub save(filename As String) ' Saves the sheet under a specfic name'
    xlWorkSheet.SaveAs(path & "/" & filename)
End Sub

Sub openfile(filename As String) ' opens the excel file under a specfic type'
    xlApp = New Excel.Application()
    xlWorkBook = xlApp.Workbooks.Open(path & "/" & filename)
    xlWorkSheet = CType(xlWorkBook.Sheets("1"), Excel.Worksheet)
End Sub
  • \$\begingroup\$ 30 seconds for how many records? Anyway, seens like a lot of opportunities for an improvement. Can you detail your benchmarks? Like which lines of code are slowing down everything and if that .xlsx behaves ok with interactive copy-paste. \$\endgroup\$ Sep 27, 2012 at 9:04
  • \$\begingroup\$ Ok I found what's causing my issue. Its specially the save statement which takes 20 seconds!!! I never would of thought that one line of code would be the issue. \$\endgroup\$ Sep 27, 2012 at 15:22
  • \$\begingroup\$ Do you have any empty cells that are taking up space? If you find the last row/column of your document, check to see if it is really the end of the document. Often, Excel will read in numerous blank cells which increase the file size, and thus the file save time. \$\endgroup\$
    – Gaffi
    Sep 28, 2012 at 1:10
  • \$\begingroup\$ Hmm... 20 seconds to save is quite a lot. How much data is there? I'd open the resulting document interactively and try to re-save it, both as-is and with deleting everything after the data to make sure tere is no non-empty "empty" cells, as per Gaffi's. Does it behave any different? \$\endgroup\$ Sep 28, 2012 at 3:56
  • \$\begingroup\$ I dunno I'll have to check when I get to work on Monday. Pardon my ignorance but when you say interactively do you mean just opening the excel file normally and timing the save outside of visual basic? \$\endgroup\$ Oct 1, 2012 at 4:39

2 Answers 2


There is definetely a way to improve that even though the excel sheet has a lot of formulas. In VBA there is an:

Application.Calculation = xlManualCalculation

That can be used to help dramatically improve the time it takes, when the problem is many formulas in a sheet. And then in the end you should turn it back to automatic.

As you are using VB with a connection to the Excel Workbook through a COM, the way to do it is very similar. It is:

ExcelApp.Calculation = XlCalculation.xlCalculationManual

Where ExcelApp is the excel application object.


The first step in attempting to optimize a function like this is to narrow down the problem. Determine how long each of the function calls, and then specific lines of code, take to execute. This will tell you where you need to focus your efforts.

  • 1
    \$\begingroup\$ Most of the execution time occurs with the xlWorkSheet.SaveAs(path & "/" & filename) I did a "Save As" interactively and it took about the same amount of time 30-40 seconds. So I think my problems is due to the sheet i'am working with and not the code. \$\endgroup\$ Oct 16, 2012 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.