VB6 and Excel

How can the contents of an excel workshet be read into an array in a vb6 application. There is no set number of rows in the worksheet but there is always 7 columns.
aidanmcg33Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
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
0
GrahamSkanRetiredCommented:
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

Open in new window

0
rsomasundarCommented:
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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

aidanmcg33Author Commented:
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.
0
Rory ArchibaldCommented:
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.
0
aidanmcg33Author Commented:
is there any documentation on this kind of stuff such as that lastrowinsheet and range etc. hard to find any stuff thats not .net!
0
Rory ArchibaldCommented:
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
0
aidanmcg33Author Commented:
but how is that stored in an array and how can i reference the values in the array?
0
Rory ArchibaldCommented:
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
0
aidanmcg33Author Commented:
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)?
0
Rory ArchibaldCommented:
What I would do is declare it dynamically, then resize it as you want:

Dim arrData()
Dim lngRowCount as Long
...

lngRowCount = LastRowInSheet(wks)
Redim arrData(1 to lngRowCount, 1 to 9)
for i = 1 to lngrowcount
    for j = 1 to 6
        arrData(i, j) = wks.Cells(i, j).value
    next j
    arrData(i, 7) = Flag1
    arrData(i, 8) = Flag2    
    arrData(i, 9) = Flag3
Next i

for example.

Regards,
Rory
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aidanmcg33Author Commented:
thanks seems to work ok. how could i produce a printout of the data in the array?
0
Rory ArchibaldCommented:
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.
0
aidanmcg33Author Commented:
to paper. any way to do it with crystal reports?
0
Rory ArchibaldCommented:
No idea - I don't have Crystal Reports. I'd be surprised if you could just print out an array though.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.