VB6 and Excel

You could use a function like this to get the last used row:

Public Function LastRowInSheet(wks As Worksheet) As Long
   ' Returns the number of the last row with data anywhere in it
   LastRowInSheet = 1
   On Error Resume Next
   With wks.UsedRange
      LastRowInSheet = .Cells.Find(what:="*", after:=.Cells(1), _
              SearchOrder:=1, _
              SearchDirection:=2).Row
   End With
End Function

then you can just use a variant to get the data:
varData = wks.Range("A1:G" & LastRowInSheet(wks)).Value

Regards,
Rory

If case you need to know how to access a worksheet from VB6

This code uses early binding, so set a reference in Project/References to the Microsoft Excel Object library.

    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Dim arv As Variant
    
    Set xlApp = New Excel.Application
    Set xlWbk = xlApp.Workbooks.Open("C:\Excel Documents\Book1.xls")
    Set xlWks = xlWbk.Sheets(1)
    arv = xlWks.Range("A1:G" & xlWks.UsedRange.Rows.Count).Value
    xlWbk.Close False
    xlApp.Quit
    'process array in arv here

Select allOpen in new window

Yes. But, the array index starts from 1 here.  And ubound refers the no. of rows in sheet.

    Dim i As Integer, j As Integer
    For i = 1 To UBound(arv) - 1
        For j = 1 To 7
            Print arv(i, j)
        Next j
        Print
    Next i

im looking to read the entire worksheet into an array there are 6 columns in the worksheeet and usually about 1000 rows. i want to have the data in an array so i can loop through it and have a few extra sections in the array so i can put flags into some of the rows. so if i have     an array with field one through to field 6 i could have three more sections flag one flag two and flag three. how can this be done? i can read in the worksheet ok and reference one cell but dont know how to get entire contents into an array.

If you just needed the worksheet contents, then as I said this would work:
varData = wks.Range("A1:G" & LastRowInSheet(wks)).Value
but if you want additionaly columns to your array, you would have to loop through the rows and columns individually and grab the value from each cell

for i = 1 to LastRowInSheet(wks)
    for j = 1 to 6
        arrData(i, j) = wks.Cells(i, j).value
    next j
Next i

for example.

is there any documentation on this kind of stuff such as that lastrowinsheet and range etc. hard to find any stuff thats not .net!

LastRowInSheet refers to that function I posted earlier - it's not built in. You would ideally need an Excel VBA book (the John Walkenbach ones are good as is the Wrox Press Excel 2002 VBA) since it's the Excel object model you need to know.
Rory

but how is that stored in an array and how can i reference the values in the array?

LastRowInSheet just gets you the row number, hence its use as the top value for the row counter. You need to declare an array (called say arrData) , then populate it using something like what I posted earlier - i.e.:

for i = 1 to LastRowInSheet(wks)
    for j = 1 to 6
        arrData(i, j) = wks.Cells(i, j).value
    next j
Next i

which assumes you have already declared and instantiated wks as a Worksheet variable. You can then reference any item of the arrData array just as you normally would.
Regards,
Rory

thanks i think im gettin what you mean now but do i need to declare the size of the array at the start or do i just do arrdata = array() or do i work out total number of rows in sheet and as i know the number of fields across that i need is 9 do i do arrdata(1000,9)?

thanks seems to work ok. how could i produce a printout of the data in the array?

Do you mean paper, or on screen? The easiest way would probably be to write it to a worksheet (or a text file) and print that if you want paper, otherwise you would use Debug.Print statments to output it to the Immediate Window.

to paper. any way to do it with crystal reports?

No idea - I don't have Crystal Reports. I'd be surprised if you could just print out an array though.