Count lines (max) with values

I would like to count the lines that have values. I tried oSheet.Rows.Count but that doesn't work. Any idea about this?

My code is the following:

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add

oSheet = oBook.Worksheets("Sheet")

oSheet.Range("A" & max).Value = "0000111"
oSheet.Range("B1").Value ="Name"

oBook.SaveAs("C:\New folder\excel\" & datenw & ".xlsx")
oExcel.Quit()

回答1:

As said in the comments, the following code should get you the count of rows that have values based on your Range:

Dim rowCount As Integer = oSheet.UsedRange.Rows.Count()

There is however a slight issue with your code I believe. This probably won't work:

oSheet = oBook.Worksheets("Sheet")

The reason it won't, is because "Sheet" doesn't exist on a new Workbook. "Sheet1" does, so this needs to be changed to:

oSheet = oBook.Worksheets("Sheet1")
'or
oSheet = oBook.Worksheets(1) 'remember Excel collections  are one based not zero based

Lastly I would look at the way you are closing Excel as oExcel.Quit() is probably leaving an instance of Excel running. Have a look at this answer which links to Siddharth Rout's bit of code:

Private Sub ReleaseObject(ByVal obj As Object)
    Try
        Dim intRel As Integer = 0
        Do
            intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        Loop While intRel > 0
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

You also to make sure you release in the right order and release everything. This is usually in backwards order:

ReleaseObject(oSheet)
oBook.Close()
ReleaseObject(oBook)
oExcel.Quit()
ReleaseObject(oExcel)

However with all that said I would look at using the Microsoft.Office.Interop.Excel namespace directly rather than declaring objects:

Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load

        Dim oExcel As New Excel.Application

        Dim oWorkbooks As Excel.Workbooks = oExcel.Workbooks
        Dim oWorkbook As Excel.Workbook = oWorkbooks.Add()

        Dim oSheets As Excel.Sheets = CType(oWorkbook.Sheets, Excel.Sheets)
        Dim oWorksheet As Excel.Worksheet = CType(oSheets(1), Excel.Worksheet)

        Dim oARange As Excel.Range = oWorksheet.Range("A" & max.ToString()) 'Not sure what max is but I took the assumption it's an Integer
        oARange.Value = "0000111"

        Dim oBRange As Excel.Range = oWorksheet.Range("B1")
        oBRange.Value = "Name"

        Dim oUsedRange As Excel.Range = oWorksheet.UsedRange()
        Dim rowCount As Integer = oUsedRange.Rows.Count()

        oWorkbook.SaveAs("C:\Test.xlsx")

        ReleaseObject(oUsedRange)
        ReleaseObject(oBRange)
        ReleaseObject(oARange)

        ReleaseObject(oWorksheet)
        ReleaseObject(oSheets)

        oWorkbook.Close()
        ReleaseObject(oWorkbook)
        ReleaseObject(oWorkbooks)

        oExcel.Quit()
        ReleaseObject(oExcel)

    End Sub

    Private Sub ReleaseObject(ByVal obj As Object)
        Try
            Dim intRel As Integer = 0
            Do
                intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            Loop While intRel > 0
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class

I would also then look at turning Option Strict On:

Restricts implicit data type conversions to only widening conversions, disallows late binding, and disallows implicit typing that results in an Object type.

回答2:

Define a row variable as Long, then start a loop which will end when it finds a blank value in column A:

Dim lRow as Long = 1

Do until oSheet.Range("A" & lRow).Value=""
  ' increment the loop variable
  lRow+=1
Loop

' display the result in a message block
MsgBox(lRow-1)