# 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.Visible = True
Else
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

-
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. –  Eugene Ryabtsev Sep 27 '12 at 9:04
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. –  Austin Davis Sep 27 '12 at 15:22
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. –  Gaffi Sep 28 '12 at 1:10
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? –  Eugene Ryabtsev Sep 28 '12 at 3:56
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? –  Austin Davis Oct 1 '12 at 4:39

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.

-
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. –  Austin Davis Oct 16 '12 at 17:26