How to select other sheets using ADO

[farmer]

Hi,



I use the folowing code to get information from a closed workbook with
ADO. It automatically selects the first sheet.

How do I get it to select other sheets? e.g. sheet2


sub test()

Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim i As Integer

dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" &
"ReadOnly=1;DBQ=E:\AdoVBA\99budget.xls"
Set dbConnection = New ADODB.Connection

dbConnection.Open dbConnectionString ' open the database connection

Set rs = dbConnection.Execute("[" & "a:a" & "]")

Do While Not rs.EOF
UserForm1.ListBox1.AddItem rs.Fields.Item(i)
rs.MoveNext
Loop

rs.Close
dbConnection.Close
Set rs = Nothing
Set dbConnection = Nothing

End sub


Help appreciated,

Farmer

 

[Bob Phillips]

Farmer,

This is the code that I use to read closed workbooks

Public Sub GetData()
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

sFilename = "c:\Mytest\Volker1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = New ADODB.Recordset
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not oRS.EOF Then
ActiveSheet.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing

End Sub


Just change the Sheet1$ to your named sheet.

 

[farmer]

Hi,

Thanks It's just what I wanted. Works great.

Greetings,

Farmer