Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read Rows within a Column through ADO ...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read Rows within a Column through ADO ...
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...b;EN-US;257819 -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Joe HM wrote : 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read Rows within a Column through ADO ...
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read Rows within a Column through ADO ...
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 : 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read Rows within a Column through ADO ...
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, -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=510907 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read Rows within a Column through ADO ...
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read Rows within a Column through ADO ...
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 -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=510907 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read Rows within a Column through ADO ...
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to read Rows within a Column through ADO ...
.... oh and yeah ... the code is not operating on data within it's own
spreadsheet. There is easier ways for that ... Joe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Read data from a Row to a Column | Excel Discussion (Misc queries) | |||
How can I read over than 65,536 rows from excel spreed sheet | Excel Discussion (Misc queries) | |||
how can i change column to read a.b.c.etc and not 1.2.3. | Excel Discussion (Misc queries) | |||
how does excel read only odd numbered rows of data froma column? | Excel Worksheet Functions | |||
Read only lock on rows | Excel Programming |