Lookup Cell Value in MS-Excel VB.Net

Read Excel with vb.net

I Ran into a snag with this stuff in a vb.net 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.

It 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. Choose 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

Code Snippet

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. ( You can modify for c# also). Feel free to give your opinion in comments section.
You can also check out the contacts sections (http://cspydo.com.ng/ ) on the homepage.
N.B:
cspydo
Techie since 2010. Awesome at computer programming, music, writing and Logic. Born and raised in the early nineties in the iconic city of Ibadan. I have a B.Sc in Computer Science from the University of Ibadan and a range of diplomas, certifications and cutting-edge skill sets in diverse areas including web development, android application development, public speaking, leadership and management, security cameras, satellite installations, etc.
http://cspydo.com.ng/

2 thoughts on “Lookup Cell Value in MS-Excel VB.Net

  1. Recent games between this pair at Anfield have been regular goalfests, and Al Hain-Cole expects more entertainment when the Gunners face Klopp’s men Liverpool face a tough test to their Premier League title credentials when they welcome Arsenal to Anfield on Saturday.

    Jurgen Klopp’s men are six points clear at the top of the table at the halfway stage of the competition, having won 16 and lost none of their opening 19 fixtures.

    Learn more here >>> http://scriboeditorial.com/

  2. Some tips i have observed in terms of computer system memory is that often there are technical specs such as SDRAM, DDR and so forth, that must fit the specific features of the motherboard. If the pc’s motherboard is very current while there are no computer OS issues, changing the memory literally normally takes under a couple of hours. It’s one of the easiest pc upgrade techniques one can imagine. Thanks for revealing your ideas.

Leave a Reply