Visual Basic 6 determine the number of active rowsClosed

Question

After reading an Excel spreadsheet into a visual basic project, how do I determine the number of active rows?

ac3mark · Answer

When you say, "reading", what do you mean?  Are you "reading" and storing values into an array?  Can you set a variable to false upon finding no data in your ROW?

You are asking a VERY vague question, can that can be answered in any number of about 10 ways.  Post some code, and we will try to make a fit!

TomCorrigan · Answer

Dear ac2mark,

Many thanks for your help.
My apologies for the confusion.
What I should have said was "after the Excel file is opened, the number of lines in the spreadsheet, is required before the data can be accessed."
If you attempt to read a line beyond this, the program 'hangs'.

The following example might clarify my problem

Cheers,

Tom


Private Sub Form_Load()

'   Declare object variables for Microsoft Excel,
'   application workbook, and worksheet objects.
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    
    Dim BookOpen As Boolean
    Dim ssRow As Integer
    Dim xData() As Double
    Dim yData() As Double

'   Assign object references to the variables.
'   Use Add methods to create new workbook and worksheet objects.
    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets.Add

    Set xlBook = xlApp.Workbooks.Open("c:\Graph.xlsx")
    Set xlSheet = xlBook.Sheets.Item(1)
    BookOpen = True


'    For ssRow = 1 To ?          This is where I need the count of active rows.

'    There is a function xlsheet.Rows, but I can't see how it's used.
    
    ReDim Preserve xData(ssRow)
    ReDim Preserve yData(ssRow)
    
    xData(ssRow) = Val(xlSheet.Cells(ssRow, 1))
    yData(ssRow) = Val(xlSheet.Cells(ssRow, 2))

'    Next ssRow
    

'   Close Microsoft Excel with the Quit method.
    xlApp.Quit

'   Release the objects.
    Set xlApp = Nothing
    Set xlBook = Nothing
    Set xlSheet = Nothing
    
    End
    
End Sub

ac3mark · Answer

There is a caveat to this fucntion, you must have the sheet you want counted, set to the active sheet.  After that, you can initialize a variable with:

Rowcount = Cells(ThisWorkbook.Worksheets(YOUR_PREVIOUSLY_ACTIVATED_SHEET).Rows.Count, 1).End(xlUp).Row 


Don't forget to issue a NEXT statement so the code know when to loop!

I hope this helps!

Have FUN!

Visual Basic 6 determine the number of active rows

3 responses

Similar discussions

Subscribe To Our Newsletter!