[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

excel - get row/column info

Posted on 2000-01-11
5
Medium Priority
?
140 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:dhamijap
  • 2
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
ture earned 150 total points
ID: 2343249
dhamijap,

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

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

Ture Magnusson
Karlstad, Sweden
0
 
LVL 20

Expert Comment

by:hes
ID: 2343677
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
0
 

Author Comment

by:dhamijap
ID: 2345276
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
0
 
LVL 22

Expert Comment

by:ture
ID: 2346339
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
0
 

Author Comment

by:dhamijap
ID: 2346984
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
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question