How to read Rows within a Column through ADO ...

[Joe HM]

Hello -

I have been experimenting with ADO a little bit and would now like to
open a connection to an Excel file and read the text of rows within a
column. So far I was able to get the first rows of columns (headers)
but how can I get text from a specific cell or range?

Here is what I have so far ...

Dim szFullName As String

Dim objConnection As ADODB.Connection
Dim objCatalog As ADOX.Catalog
Dim objTable As ADOX.Table
Dim lIndex As Long
Dim szConnect As String
Dim szSheetName As String

szFullName = CStr(Application.GetOpenFilename("Excel Files
(*.xls),*.xls", , "Select an Excel File"))

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
szFullName & ";Extended Properties=Excel 8.0;"

Set objConnection = New ADODB.Connection
objConnection.Open szConnect
Set objCatalog = New ADOX.Catalog
Set objCatalog.ActiveConnection = objConnection

Dim objColumn As ADOX.Column

For Each objTable In objCatalog.Tables
If InStr(objTable.Name, "A") > 0 Then
For Each objColumn In objTable.Columns
MsgBox (objTable.Name & " >" & objColumn & "<")
Next objColumn
End If
Next objTable

objConnection.Close
Set objCatalog = Nothing
Set objConnection = Nothing

Any suggestions are highly appreciated!

Thanks!
Joe

 

[keepITcool]

to be able to retrieve the data from cell blocks (without field names)
change the connection string to:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
szFullName & ";Extended Properties=""Excel 8.0;HDR=NO"""

then use SELECT * FROM [Sheet1$A1:B10]"

Note if HDR=NO your adox column name dumper will not work the same

also read
http://support.microsoft.com/default.aspx?scid=kb;EN-US;257819



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Joe HM wrote :

 

[Joe HM]

Hello -

Thanks for you feedback. I had already found the MS site but I still
cannot figure out how to add all of that to my code? They mention the
SELECT * FROM ... but don't mention how to integrate that into code. I
also found their ExcelADO example but that was written for ASP. What I
want to do is open an excel file and search through a column until I
find an empty cell.

What would be the easiest way to implemen that?

Thanks!
Joe

 

[keepITcool]

depends what you want to do with that empty cell..

are you sure that using ADO is usefull?
why not stay with excel itself?

pls explain a bit more.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Joe HM wrote :

 

[tony h]

An observation and I hope you take it in good spirit.

If your intention is just to find the blank row and do something with
it in Excel then stick with Excel there are plenty of good and easy
ways to do it.

If someone is intending to learn about ADO and hasn't much experience
with SQL then I suggest they start by plaaying about with Access (which
uses ADO). Once they have got the principles sorted out there it is much
easier to see how to apply it to Excel.

I quite often use ADO for operations on a spreadsheet but I find it
takes a good deal of experience to determine when it is the right thing
to do. Three yypical reasons I use ADO (or DAO) on a spreadsheet are :
- analysing large volumes of data on a spreadsheet
- importing data from a spreadsheet into a database
- comparing data where SQL provides better facilities

I hope this helps and good luck

regards,

 

[Joe HM]

Hello -

I needed more than just finding empty cells but I could actually figure
it out by myself >

fullName = "C:\Book1.xls"
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
fullName & _
";" & "Extended Properties=""Excel 8.0;HDR=NO"""
Set adoConn = New ADODB.Connection
adoConn.Open sConn

src = "SELECT * FROM [Sheet1$C47:C100]"
Set adoRs = New ADODB.Recordset
adoRs.Open Source:=src, ActiveConnection:=adoConn

If Not (adoRs.BOF Or adoRs.EOF) Then
adoRs.MoveFirst

Do While Not adoRs.EOF
lValue = adoRs.Fields(0).Value

If Not IsNull(lTEXT) Then
...
Else
Exit Do
End If

adoRs.MoveNext
Loop
Else
Debug.Print "No Record"
End If

adoRs.Close
adoConn.Close

That works like a charm and is way faster than opening the files
through Excel and parsing through the cells.

The only problem I saw is that if I have Column A and B merged for some
rows, I cannot read in what is in Column B even if I start below the
last merged cells. Is there a way to get to the data anyway?

Thanks again!
Joe

 

[tony h]

It should return all columns but if A&B are merged on one row the 2nd
field will be the value NULL and it only that row that will have this
charastic.

The code as you supplied run with a print to the immediate window.
a3 b3 c3
a2 Null c2
a1 b1 c1

Also do set the object to Nothing as well as closing them and it would
be safer to use option explicit and declare the variables properly.

I presume the code is not operating on data within it's own spreadsheet
- this tends to have fewer problems.

Well done.

regards

 

[Joe HM]

Hello -

Thanks for all the help! Yeah ... I actually set the object to nothing
when I'm done.

The only weird thing is that your example of merged cells works but for
another one that has several rows with merged cells it would not work.
Oh well ... I think I found another workaround for that.

Thanks again!
Joe

 

[Joe HM]

.... oh and yeah ... the code is not operating on data within it's own
spreadsheet. There is easier ways for that ...

Joe