C-Spydo

Techie | Creative Artiste
Read Excel with vb.net

Lookup Cell Value in MS-Excel 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:

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

  • Ini Edo
    December 29, 2018 at 9:35 pm

    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/

  • mirillis action
    April 2, 2019 at 4:58 am

    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

Your email address will not be published. Required fields are marked *.

You may use these <abbr title="HyperText Markup Language">HTML</abbr> tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

WhatsApp chat