Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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
share|improve this question
    
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

3 Answers 3

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.

share|improve this answer

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.

share|improve this answer
1  
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
up vote 0 down vote accepted

OK I found what's causing my issue. Sadly is has little to do with my code. The excel sheet i have been given to work with is heavily filled with formulas and timing the save on the sheet standalone in excel is clocked around 20 seconds.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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