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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
ASKER
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
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
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.RecordCoun
' 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