ADO to read Excel 1

[LARiot]

Hi, how can ADO (or a better way, I'm all um, eyes) be used to read an excel sheet in VBA? I don't want to link the table nor do I want to import the sheet.

Right now I would have it so the users would save excel sheet (13 for each of 3 workbooks, 39 total) in cvs format. I've searched everywhere for examples of what would seem like fairly common code.

Any help is appreciated.

-Neema

 

[Remou]

Here is an example:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& CurrentProject.Path & "\Tek-Tips.xls" _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
strsql = "SELECT * FROM [sheet1$]"

rs.Open strsql, cn
Debug.Print rs.Fields(0)

More information:

http://www.connectionstrings.com/

 

[LARiot]

Wow, it worked. Not that I doubted it would, it's just kinda cool when it does.

Thanks Remou

 

[LARiot]

OK, silly question. How do I read the next line of code?

 

[LARiot]

OK, figured it out. In case anyone else needs it, it's rs.movenext

 

[LARiot]

One other question. Is there a way of retrieving sheet names? I don't want specify the names in the code as there will be 39 and it's not good coding protocol.

Or better yet just asking for the 1st, 2nd, etc worksheets?

Thanks again.

 

[Remou]

Another example:

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& CurrentProject.Path & "\a1.xls" _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
     
    Set rs = cn.OpenSchema( _
         adSchemaTables, Array(Empty, Empty, vstrTable))
    
    Do While Not rs.EOF
        Debug.Print rs.Fields("TABLE_NAME")
        rs.MoveNext
    Loop

 

[LARiot]

I'm not sure how to use this. It won't go into the Do While loop as rs.EOF is true. Should I insert something into vstrTable? Does the Openschema method in of itself open the excel sheet?

Thanks.

 

[Remou]

You asked for a way of finding the names of each sheet, the snippet above will do that. In more detail:

Dim i As Integer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rsX As ADODB.Recordset

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set rsX = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& CurrentProject.Path & "\a1.xls" _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
     
    Set rs = cn.OpenSchema( _
         adSchemaTables, Array(Empty, Empty, vstrTable))
    
    Do While Not rs.EOF
        Debug.Print rs.Fields("TABLE_NAME")
        strsql = "SELECT * FROM [" & rs.Fields("TABLE_NAME") & "]"
        rsX.Open strsql, cn
        Debug.Print rsX.Fields(0)
        rsX.Close
        rs.MoveNext
    Loop

 

[LARiot]

Thanks Remou. I don't mean to sound ungrateful, but I can't get the code to work. I'm a little confused by the second parameter in the openschema method. Why is it an array? Should I enter something into vstrTable as it's blank the whole time?

Thanks again

 

[Remou]

I should have included some references:
Check if table exists
thread705-933401

http://support.microsoft.com/kb/186246

There is no need to change this line:
[tt]Set rs = cn.OpenSchema( _
adSchemaTables, Array(Empty, Empty, vstrTable))[/tt]

Which will return the names of each sheet, though you can change vstrTable to strSheet to make it more readable, if you wish. This line:

[tt] strsql = "SELECT * FROM [" & rs.Fields("TABLE_NAME") & "]"[/tt]

Is using the field TABLE_NAME to put the sheet name into strsql.

 

[PHV]

This revisited code works for me (not the original):
Sub listXLsheets()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rsX As ADODB.Recordset
Dim strsql As String
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set rsX = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& CurrentProject.path & "\workbook name.xls" _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set rs = cn.OpenSchema(adSchemaTables)
Do While Not rs.EOF
Debug.Print rs.Fields("TABLE_NAME")
strsql = "SELECT * FROM [" & rs.Fields("TABLE_NAME") & "]"
rsX.Open strsql, cn
Debug.Print rsX.Fields(0).Name, rsX.Fields(0)
rsX.Close
rs.MoveNext
Loop
rs.Close
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LARiot]

Yeah, it's workin'. Thanks both of you guys. I think I was just missing the second recordset declaration (rsX) and usage.

 

[LARiot]

How can I get excel to return it's actual data instead of formatted data? Right now it only does this when the Excel sheet is open.

Thanks.

 

[LARiot]

OK, I've searched and asked and no one knows the answer to this. The only way was to remove the character with which the data was formated.

Meaning I took "3.3%" and with some left(), right() and instr() functions made it look like "3.3"

This was the only resolution found to what seems should be a mere option in the connection string.