Finding Last Row in Excel from VB.NET

Question

I am trying to learn how to communicate with an Excel spreadsheet from within VB.Net and have found some topics from Siddharth Rout that have been most helpful. However when I try to find the last row used I get the following error message:

An unhandled exception of type 'SystemMissingMemberException' occurred in Microsoft.VisualBasic.dll Additional information: Public member 'xlapp' on type 'Range' not found

The error occurs when I run the line beginning lRow =

Help greatly appreciated

My code includes

Imports System.Data.OleDb
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop.Excel
'~~> Define your Excel Objects
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet

'   Find the LAST row with data in Column A
lRow = xlWorkSheet.Range("A" & xlWorkSheet.Rows.Count).xlapp.xlUp.Row
MsgBox("The last row which has data in Col A of Sheet1 is " & lRow

Solution 1

Try to use this below code,by which you can get the number of rows used

lRow=ActiveSheet.UsedRange.Rows.Count

Solution 2

Try the following, one function gets last used row for a column, one for last used row for a sheet.

Example

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim fileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx")
        Dim sheetName As String = "Sheet1"
        Dim lastRow As Integer = 0

        lastRow = UseRowsdByColumn(fileName, sheetName, "A")
        MessageBox.Show(lastRow.ToString)
    End Sub
End Class

Code Module

Option Strict On
Option Infer Off

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices

Module ExcelCode

    ''' <summary>
    ''' Get last used row in sheetname
    ''' </summary>
    ''' <param name="FileName">path and filename to excel file to work with</param>
    ''' <param name="SheetName">Worksheet name to get information</param>
    ''' <returns>-1 if issues else lasted used row</returns>
    ''' <remarks></remarks>
    Public Function UsedRows(ByVal FileName As String, ByVal SheetName As String) As Integer

        Dim RowsUsed As Integer = -1

        If IO.File.Exists(FileName) Then
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)

            xlApp.Visible = False

            xlWorkSheets = xlWorkBook.Sheets

            For x As Integer = 1 To xlWorkSheets.Count

                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

                If xlWorkSheet.Name = SheetName Then
                    Dim xlCells As Excel.Range = Nothing
                    xlCells = xlWorkSheet.Cells

                    Dim thisRange As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)

                    RowsUsed = thisRange.Row
                    Marshal.FinalReleaseComObject(thisRange)
                    thisRange = Nothing

                    Marshal.FinalReleaseComObject(xlCells)
                    xlCells = Nothing

                    Exit For
                End If

                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

            Next

            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()

            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
        Else
            Throw New Exception("'" & FileName & "' not found.")
        End If

        Return RowsUsed

    End Function
    ''' <summary>
    ''' Get last used row for a single column
    ''' </summary>
    ''' <param name="FileName"></param>
    ''' <param name="SheetName"></param>
    ''' <param name="Column"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UseRowsdByColumn(ByVal FileName As String, ByVal SheetName As String, ByVal Column As String) As Integer
        Dim LastRowCount As Integer = 1

        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)

        xlApp.Visible = False

        xlWorkSheets = xlWorkBook.Sheets

        For x As Integer = 1 To xlWorkSheets.Count

            xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

            If xlWorkSheet.Name = SheetName Then

                Dim xlCells As Excel.Range = xlWorkSheet.Cells()
                Dim xlTempRange1 As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
                Dim xlTempRange2 As Excel.Range = xlWorkSheet.Rows


                Dim xlTempRange3 As Excel.Range = xlWorkSheet.Range(Column.ToUpper & xlTempRange2.Count)
                Dim xlTempRange4 As Excel.Range = xlTempRange3.End(Excel.XlDirection.xlUp)

                LastRowCount = xlTempRange4.Row

                Marshal.FinalReleaseComObject(xlTempRange4)
                xlTempRange4 = Nothing

                Marshal.FinalReleaseComObject(xlTempRange3)
                xlTempRange3 = Nothing

                Marshal.FinalReleaseComObject(xlTempRange2)
                xlTempRange2 = Nothing

                Marshal.FinalReleaseComObject(xlTempRange1)
                xlTempRange1 = Nothing

                Marshal.FinalReleaseComObject(xlCells)
                xlCells = Nothing

            End If

            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing

        Next

        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()

        ReleaseComObject(xlWorkSheets)
        ReleaseComObject(xlWorkSheet)
        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xlApp)

        Return LastRowCount

    End Function

    Public Sub ReleaseComObject(ByVal obj As Object)
        Try
            If obj IsNot Nothing Then
                Marshal.ReleaseComObject(obj)
                obj = Nothing
            End If
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub

End Module

Comments

[mchar]

By used means "have ever used", not just the latest!