Link to home
Start Free TrialLog in
Avatar of dhamijap
dhamijap

asked on

excel - get row/column info

Hello:
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.

dhamijap
ASKER CERTIFIED SOLUTION
Avatar of ture
ture

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hes
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
Data1.Refresh

' Determine the number of rows
 Data1.Recordset.MoveLast
 Data1.Recordset.MoveFirst
 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
Data1.Recordset.MoveNext
Loop

And if you do want to check for the blank first column add:
If Data1.Recordset(0) = " " Then
    Exit Do
End If
Avatar of dhamijap
dhamijap

ASKER

Ture:
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?

dhamijap


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

dhamijap
dhamijap,

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
  Loop
  If r > 0 Then arr = ws.Cells(1, 1).Resize(r, 9)
End Sub

/Ture
Ture:

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.

dhamijap


hes:
 
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" & "$"

Thnaks