Help counting rows in excel

[Mike Bishop]

I have an application that needs to know how many excel rows are in a worksheet before they are imported into my application.

at the moment I am asking the end user to enter this into a text box, but would like my application to do this can anyone suggest the best way to do this?

thanks

[Reverend Jim]

Are thr rows contiguous? For example, If I scan down a particular column, can I assume that the first blank cell I encounter indicates the end of the rows? If so I can provide some simple code to do what you want. I'll look it up and post it here.

[Reverend Jim]

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet

        xls.Workbooks.Open("d:\temp\test.xls")
        sheet = xls.ActiveWorkbook.Sheets(1)

        Dim row As Integer = 1

        Do Until sheet.Cells(row, 1).Value Is Nothing
            row += 1
        Loop

        MsgBox("last row is " & row - 1)

        xls.Workbooks.Close()
        xls.Quit()
        releaseObject(sheet)
        releaseObject(xls)

    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

This code scans column 1 from row 1 onward looking for a cell that has nothing in it. It assumes that the cell before this is the last valid one. I have found that you have to deliberately release both the sheet and the Excel objects or you will be left with an Excel process running in the background. There is some question as to why this is necessary but I haven't found a definite answer.

[Phasma]

Hey Mike,
Give this a go. This was some VBA code I translated to VB.net

Option Strict On
Option Explicit On

Imports Excel = Microsoft.Office.Interop.Excel 

'The worksheet you want the Last Row that has data.
xlWkshtMain = CType(xlWkbNew.Worksheets(Index:=1), Excel.Worksheet) 

'New Excel Application
xlApp = New Excel.Application

  ''' <summary>
  ''' Returns a Long Integer 
  ''' of the last row with data
  ''' </summary>
  ''' <returns></returns>
  ''' <remarks></remarks>
  Private Function FindLastRow() As Long

    Dim LastRow As Long

    LastRow = xlWkshtMain.Cells.Find(What:="*", After:=xlApp.Range("A1"), _
                                     SearchOrder:=Excel.XlSearchOrder.xlByColumns, _
                                     SearchDirection:=Excel.XlSearchDirection.xlPrevious).Row

    Return LastRow

  End Function

   ''' <summary>
  ''' Returns a Long Integer 
  ''' of the last row with data 
  ''' </summary>
  ''' <param name="wrkSht"></param>
  ''' <returns></returns>
  ''' <remarks></remarks>
  Private Function FindLastRow(ByVal wrkSht As Excel.Worksheet) As Long

    Dim LastRow As Long

    LastRow = wrkSht.Cells.Find(What:="*", After:=xlApp.Range("A1"), _
                                     SearchOrder:=Excel.XlSearchOrder.xlByColumns, _
                                     SearchDirection:=Excel.XlSearchDirection.xlPrevious).Row

    Return LastRow

  End Function