Optimizing this Excel automation 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" &amp; fileNum.Length)
    xlSourceRange.Value = fileNum
    xlSourceRange = xlWorkSheet.Range("O2:O" &amp; dateRecorded.Length)
    xlSourceRange.Value = dateRecorded
    xlSourceRange = xlWorkSheet.Range("P2:P" &amp; 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 &amp; "/" &amp; 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 &amp; "/" &amp; filename)
    xlWorkSheet = CType(xlWorkBook.Sheets("1"), Excel.Worksheet)
    'xlWorkSheet.Activate()
End Sub
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.