excel - get row/column info

I am creating an application in which I need to open an existing excel document. I need to go through the document and get the column string values of column 1 thru 9 of each row and store those values in a two dimentional array. if there a blank value in the first column of any row, that indicates the last row.

Please provide sample code.

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.


This procedure should do it...

sub fillarray()
  dim r as long
  dim c as long
  dim arr() as variant
  dim ws as worksheet

  set ws=activesheet

    for c=1 to 9
      if ws.cells(r,1)="" then exit do
      redim preserve arr(1 to r,1 to 9)
    next c
end sub

Ture Magnusson
Karlstad, Sweden

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
Another way to try is add a data control to a form and use the following:

Dim UserData()
Dim Maxdata As Long
Dim x As Integer, y As Integer
Data1.RecordsetType = 0
Data1.DatabaseName = "Path\ExcelName.xls"
Data1.RecordSource = "SheetName" & "$"
Data1.Connect = "Excel 8.0;" ' or whatever version you are using
Data1.Options = 0

' Determine the number of rows
 MaxRow = Data1.Recordset.RecordCount
' 9 Columns, MaxRow Rows of Data
ReDim UserData(0 To 8, 0 To MaxRow - 1)

' Build the temp array to hold the data
x = 0
Do Until Data1.Recordset.EOF
  If Data1.Recordset(0) <> "" Then ' check for column headings
   For y = 0 To 8
     UserData(y, x) = Data1.Recordset(y)
   Next y
   x = x + 1
  End If

And if you do want to check for the blank first column add:
If Data1.Recordset(0) = " " Then
    Exit Do
End If
dhamijapAuthor Commented:
I tried your answer. It works but does not get me through all the rows. I needed it to go thru 9 columns and 10 rows, which are in my test files.
Can you please tell me how do I get to the rows as well?


I am working from your code to see if it will get me what i want.


Sorry. I didn't test run my previously posted code and I forgot that a 'Redim Preserve' will not be able to change the first dimension of an array, the way I tried to do.

This code is faster and more compact. And it works well. Yes - I have tested it this time.

Sub FillArray()
  Dim ws As Worksheet
  Dim r As Long
  Dim arr As Variant

  Set ws = ActiveSheet
  Do Until ws.Cells(r + 1, 1) = ""
    r = r + 1
  If r > 0 Then arr = ws.Cells(1, 1).Resize(r, 9)
End Sub

dhamijapAuthor Commented:

I have acomplished what I was looking for by using both answers from you. I would say that it really made me think and tought me very well. Thanks a lot.


I liked your answer as well. I will do it in both ways. I could not give you points. How ever I am ceating a simple question for you to get you 25 points.
Please answer that so that i can give you the points.

the question is :
How can I use the sheet tab name dynamicall in the applicaiton and what does "$" do at the end?

Data1.RecordSource = "SheetName" & "$"

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.