Optimizing this Excel automation

Question

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")
    debugging(False)
    putfilesIntoYTD(dt)
    save("test.xlsx")
    closexl()
    cleanup()
    MsgBox("fin")
End Sub
Sub closexl()
    xlWorkBook.Close()
    xlApp.Quit()
End Sub
Sub cleanup() ' Releases all of the com objects to object not have excel running as a process after this method finishes'
    GC.Collect()
    GC.WaitForPendingFinalizers()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSourceRange)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
    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
    Else
        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
        Next
    Else
        For i As Integer = 0 To dt.Rows.Count - 1
            array(i) = dt.Rows(i)(columnIndex).ToString()
        Next
    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)
    'xlWorkSheet.Activate()
End Sub

Answer 1

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.

Answer 2

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.