Searching a huge Excel sheet in a very faster way using VB.NET

• Download SearchExcel_demo.zip - 340.8 KB
• Download SearchExcel_Src.zip - 339.4 KB

Introduction

Usually everybody come across of searching huge excel sheets, when working with excel automation.This code searches excel sheet and read data from cells and insert to specified column in a faster way.

Background

I got a job in which i want to search a big excel sheet of more than 4000 records in another sheet consists of 6000 records.By manually doing it took 2 days for me to complete.So i gone for excel automation.If you are searching for a data in a huge excel sheet by cell by cell in a column,it will take around 10sec to search for a data.If you have this much records to search,it will take a big time.By using VLookUp, searching become extremely fast.It will take only 1 min to search 2000 records.

Using the code

In the FirstFile TextBox,specify the excel sheet in which the data to be searched is present.In the code i have specified the worksheet as 1st worksheet and column as the 1st column.In the SecondFile TextBox ,specify the excel sheet in which searching for the data is to be done.I have added 2 excel sheets in the demo zip file.

The code searches for data in the 1st column of 1st worksheet of second excel sheet and read data in the corresponding row from the second column and returns in 2-D array.If no data is present VLookUp will return "-2146826246".

For Automating a Microsoft Office Program with VB.NET requires Project Library referenced by your VB.NET project.

Here we are Automating Excel, so we need to add a reference to the Microsoft Excel Object Library.

(1) Within the Visual Studio .NET IDE, choose the Project > Add Reference... menu item. Next you will want to click on the COM tab at the top because the Excel object model is a Component Object Model (COM) library. Now scroll down until you find the "Microsoft Excel 11.0 Object Library" (which could be 10.0, 9.0, 8.0 or even 12.0, depending on your system).

(2) Next, click the [Select] button and then click [OK]. The reference to the Excel Object Library is now loaded.

'For importing office components,we want to use this statement

  Imports Microsoft.Office.Core

<pre lang="<span" class="code-string">"<span" class="code-string">"vb.net">Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim row As Integer
        Dim a As String
        Dim Value As String
        Dim SecondFileRange As String
        Dim FirstFileRange As String
        Dim lastrow1, lastrow2, lastCol2 As Integer
        Dim xlappFirstFile As Excel.Application = Nothing
        Dim xlappSecondFile As Excel.Application = Nothing
        Dim xlFirstFile_WB1 As Excel.Workbook = Nothing
        Dim xlSecondFile_WB2 As Excel.Workbook = Nothing
        Dim xlSecondfile_WS2 As Excel.Worksheet = Nothing
        Dim xlFirstfile_WS1 As Excel.Worksheet = Nothing

        Try
            xlappFirstFile = CreateObject("Excel.Application")
            xlappSecondFile = CreateObject("Excel.Application")

            'Open the first file


            xlFirstFile_WB1 = xlappFirstFile.Workbooks.Open(txtbxFirstFile.Text)
            xlFirstFile_WB1.Application.Visible = True
            xlFirstFile_WB1.Application.WindowState = Excel.XlWindowState.xlMinimized
            xlFirstfile_WS1 = xlappFirstFile.Application.Worksheets(1)
            lastrow1 = xlFirstfile_WS1.UsedRange.Rows.Count

            'Open the second file


            xlSecondFile_WB2 = xlappSecondFile.Workbooks.Open(txtbxSecondFile.Text)
            xlSecondFile_WB2.Application.Visible = True
            xlSecondFile_WB2.Application.WindowState = Excel.XlWindowState.xlMinimized
            xlSecondfile_WS2 = xlappSecondFile.Application.Worksheets(1)
            lastrow2 = xlSecondfile_WS2.UsedRange.Rows.Count
            lastCol2 = xlSecondfile_WS2.UsedRange.Columns.Count

            'Calculate the Range in First file to which the searched data is to be inserted


            FirstFileRange = Chr(txtbxCol.Text + 64) & "2:" & Chr(txtbxCol.Text + 64) & lastrow2

            'Calculate the Range in Second file in which searching is to be done


            SecondFileRange = "A2:" & Chr(lastCol2 + 64) & lastrow2

            Dim searchvalues(lastrow2, 0) As String

            'Search for the data


            For row = 2 To lastrow1
                a = xlFirstFile_WB1.Worksheets(1).Cells(row, 1).value()
                Value = xlappSecondFile.VLookup(a, xlSecondfile_WS2.Range(SecondFileRange), 1, False)
                If Value <> "-2146826246" Then
                    searchvalues(row - 2, 0) = xlappSecondFile.VLookup(a, xlSecondfile_WS2._
_Range(SecondFileRange),2, False)
                End If
                'End If

            Next

            'Insert the values into the col:2 of first excel file


            xlFirstfile_WS1.Range(FirstFileRange).Value = searchvalues

            'Change the font and size


            xlFirstfile_WS1.Range(FirstFileRange).Font.Color = ColorTranslator.ToOle(Color.Blue)
            xlFirstfile_WS1.Range(FirstFileRange).Font.Name = "Verdana"
            xlFirstfile_WS1.Range(FirstFileRange).Font.Size = 8

        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            Me.Close()
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
            ReleaseObject(xlFirstfile_WS1)
            xlFirstFile_WB1.Close(SaveChanges:=True)
            ReleaseObject(xlFirstFile_WB1)
            ReleaseObject(xlSecondfile_WS2)
            xlSecondFile_WB2.Close(SaveChanges:=False)
            ReleaseObject(xlSecondFile_WB2)
            xlappFirstFile.Quit()
            ReleaseObject(xlappFirstFile)
            xlappSecondFile.Quit()
            ReleaseObject(xlappSecondFile)
        End Try
    End Sub  

When you automate a Microsoft Office application from Microsoft Visual Basic .NET , the Office application does not quit when you call the Quit method.

These steps are to be performed for releasing the COM objects.

1. Call System.Runtime.InteropServices.Marshal.ReleaseComObject to decrement the reference count of the RCW.
2. To release the reference to the variable, set the variable equal to Nothing or Null.
3. Use the Quit method of the Office application object to tell the server to shut down.
4. Call GC.Collect() method and the GC.WaitForPendingFinalizers() method after you release the last object.

Step 4 is used because the runtime performs garbage collection on the RCW, the GC.Collect() method forces the garbage collector to run and might release any references that the RCW still has.

GC.Collect() and GC.WaitForPendingFinalizers() is called twice because the runtime callable wrapper ("RCW") for each COM object has a finalizer that does the actual work of releasing the COM object from .NET. And .NET objects that have finalizers (the RCW in this case) need a second round of garbage collection to be fully removed from memory.

<pre lang="<span" class="code-string">"<span" class="code-string">"vb.net">'Subroutine to release objects.

    Private Sub ReleaseObject(ByVal ob As Object)

        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ob)
        Catch
        Finally
            ob = Nothing
            GC.Collect()
            GC.WaitForPendingFinalizers()
        End Try
    End Sub   

Points of Interest

While writing the code ,I got stuck with 2 situations.First one is i want to convert column to range string.Here i used chr() to convert decimel to ascii.The ascii code for 'A' is 65.So i added the col number with 64.

<pre lang="<span" class="code-string">"<span" class="code-string">"vb.net">FirstFileRange = Chr(txtbxCol.Text + 64) & "2:" & Chr(txtbxCol.Text + 64) & lastrow2 

Second is in the case of Excel.Worksheet.Range.Value.It takes array.If we are using 1-D array,it will replace every cell in the range by a single value.Here we want to replace only one column.So i declared a 2-D array as 1-D array with dimension lastrow1 X 0.If you want replace 2 columns,the dimension is to be changed to lastrow1 X 1.

<pre lang="<span" class="code-string">"<span" class="code-string">"vb.net">Dim searchvalues(lastrow2, 0) As String
'Search for the data
            For row = 2 To lastrow1
                 a = xlFirstFile_WB1.Worksheets(1).Cells(row, 1).value()
                 Value = xlappSecondFile.VLookup(a, xlSecondfile_WS2.Range(SecondFileRange), 1, False)
               If Value <> "-2146826246" Then
                    searchvalues(row - 2, 0) = xlappSecondFile.VLookup(a, xlSecondfile_WS2._
_Range(SecondFileRange), 2, False)
               End If
             Next

'Insert the values into the col:2 of first excel file

            xlFirstfile_WS1.Range(FirstFileRange).Value = searchvalues  

History

Original article submitted:27 Nov 07