J
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
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