Lookup Cell Value in MS-Excel VB.Net

Ran into a snag with this stuff in a program I was working on recently. Searched online and found a lot of confusing, not really functional answers. So, built on the ideas and came up with this.

Works perfectly, VB.Net, pretty sure any C# person can translate to C#, and hence, Java, etc.


1. Right Click on your project in Visual Studio.
2. Select Add Reference
3. In the Reference Box, Search for Excel
4. Select Microsoft.Interop.Excel from the Results and Click Ok
5. You Should see Microsoft.Interop.Excel under references in your project. That means you are good to go.

6. Put this at the top of your code  Imports Excell = Microsoft.Office.Interop.Excel

And Here is the method itself:


Public Function OpenExcelData(ByVal FileName As String, ByVal SheetName As String,
                                  ByVal column As Integer, ByVal row As Integer)
        Dim vvalue As Decimal = 0
        Dim dvalue As Object = vbNull
        If IO.File.Exists(FileName) Then
            Dim Proceed As Boolean = False
            Dim xlApp As Excell.Application = Nothing
            Dim xlWorkBooks As Excell.Workbooks = Nothing
            Dim xlWorkBook As Excell.Workbook = Nothing
            Dim xlWorkSheet As Excell.Worksheet = Nothing
            Dim xlWorkSheets As Excell.Sheets = Nothing
            Dim xlCells As Excell.Range = Nothing
            xlApp = New Excell.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks

            xlWorkBook = xlWorkBooks.Open(FileName)
            'xlApp.Visible = True
            xlWorkSheets = xlWorkBook.Sheets


            Dim wsno As Integer = 0
            If (sheetparam = 2) Then
                wsno = 1
            ElseIf (sheetparam = 3) Then
                wsno = 2
            End If

            Try
                xlWorkSheet = CType(xlWorkSheets(wsno), Excell.Worksheet)

                Dim xRng As Excell.Range = CType(xlWorkSheet.Cells(row, column), Excell.Range)
                dvalue = xRng.Value()

            Catch os As Exception
                'MessageBox.Show(os.ToString)
            End Try
            vvalue = Decimal.Parse(dvalue)
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()

            ReleaseComObject(xlCells)
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
        Else
            MessageBox.Show("'" & FileName & "' not located.")
        End If
        Return vvalue
    End Function

    Public Sub ReleaseComObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub

Modify as necessary. Feel free to give your opinion in comments section.