Programming Tutorials

To read the entire worksheet in an Excel workbook through VB.net Code

By: Issac in VB.net Tutorials on 2009-01-31  

In this tutorial we can see how to find the used area in a worksheet and the last data row in Excel. For finding last row we are using WorkSheet.UsedRange. It will return the last cell. The following code opens an Excel file and reads the entire content.

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim range As Excel.Range
        Dim rCnt As Integer
        Dim cCnt As Integer
        Dim Obj As Object

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("c:\vbexcel.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")

        range = xlWorkSheet.UsedRange

        For rCnt = 1 To range.Rows.Count
            For cCnt = 1 To range.Columns.Count
                Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
                MsgBox(Obj.value)
            Next
        Next

        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

When you execute this source code it reads all content from the specified Excel file.