Search large excel sheet (faster)

Question

I have a fully working application that searches an Excel document for a ID number (set of 6 numbers). Once it finds the numbers it puts them into labels. My question is, the excel sheet is almost 60000 rows, and it takes awhile (even longer when I search by name). Is there a way to speed it up, or another option to search?

    Private Sub SearchID()
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet1 As Excel.Worksheet
    Dim rng As Excel.Range
    Dim codeabc As String
    Dim found As Boolean
    Dim i As Integer
    If AssociateID.Text = String.Empty Then
        popup.Close()
        MsgBox("Please make sure 'Associate ID' is filled out")
        Exit Sub
    End If
    xlApp = CreateObject("Excel.Application")
    xlBook = xlApp.Workbooks.Open("G:\grps\every\People Report\HRIS Remedy Report.xls")
    xlSheet1 = xlBook.Worksheets(1)
    rng = xlSheet1.Range("a1:a60000")
    codeabc = (AssociateID.Text)
    found = False
    For i = 1 To rng.Count
        If rng.Cells(i).Value = codeabc Then
            IDLabel.Text = AssociateID.Text
            NameLabel.Text = (rng.Cells(i).offset(0, 1).value())
            DepartmentLabel.Text = (rng.Cells(i).offset(0, 3).value())
            PositionLabel.Text = (rng.Cells(i).offset(0, 2).value())
            found = True
            xlBook.Close()
            popup.Close()
            Exit Sub
        End If
    Next i
    If Not found Then
        MsgBox("Associate ID: " & AssociateID.Text & " is not found. Please check the ID and try again")
        AssociateID.Clear()


    End If
    popup.Close()
    xlBook.Close()
End Sub

Answer 1

Excel calls from VB are inherently slow. As Excel files get larger, a large portion of the time it takes your code to run is simply Excel opening the file. You can get a significant performance boost by exporting your spreadsheet to a CSV, but you will have to completely rewrite your code to read the file with something other than Excel, i.e. TextFieldParser.

Since you have working code and just want a performance boost, I'm guessing this Excel spreadsheet is regularly updated by other users and converting the file manually wouldn't be practical. If that's the case, you can try Dale's advice above, or, if your environment's security settings permit, consider writing a Macro to automatically convert the Excel file every time it's updated.

Another way to reduce search times is to make the call to Excel happen before your code searches for a result. Something as simple as making your Excel objects global and having a background worker open your file at startup will eliminate the delay of opening the file. A more in-depth solution along these lines is to have your application maintain an entirely separate database in another format and update it in the background or at strategic times. For example, a background worker could compare the Excel file to a SQL database at startup and update it as appropriate. If the user typically initiates the search before the update is complete and updating the database daily meets your needs, you could instead set the process to run automatically when no one will be using the application, e.g. daily at 2:00 AM. I prefer SQL for fast searches, as did those in this question.

Keep in mind that these strategies involve drastic shifts in how your code operates and will make debugging and updating your program more involved. If the delay you are experiencing now is a minor inconvenience, it's probably not worth the effort of changing it. If it's crippling the usability of your application, however, you might not have another choice.