Using ADO to access another workbook

[Jack]

Can someone tell me what query string you use to access an
Excel 2003 workbook from another one.

I think I found the string you would use to get all the
records of an Excel range:

strquery = "Select * from [Recovered_Sheet1$]"

But what is the exact Syntax you would use if you just
wanted certain records? For example, I have a field (from
the first row of the table) called Property. What would
the above string be if I wanted say just all rows that
have "Boston" in this column?

Any help would be appreciated. Thanks.

 

[Harald Staff]

"Select * from [Recovered_Sheet1$] where Property='Boston'"

HTH. Best wishes Harald

 

[Ron de Bruin]

Hi Jack

Start here
http://www.rondebruin.nl/ado.htm

 

[Guest]

Hi Jack

Can someone tell me what query string you use to access an
Excel 2003 workbook from another one.

Yes.

Paste the following on a form and call it with a commandButton
on a sheet then just follow the notes in the code.

Private Sub CommandButton1_Click()

On Error GoTo BadTrip

Dim db_Name As String
Dim DB_CONNECT_STRING As String

'To use ADO objects in an application add a reference
'to the ADO component. From the VBA window select
'>Tools/References< check the box
' "Microsoft ActiveX Data Objects 2.x Library"

'You should fully quality the path to your file

db_Name = ("C:\Documents and Settings\") _
& ("The Cat Man\My Documents\Test.xls")

DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0" _
& ";Data Source=" & db_Name _
& ";Extended Properties=""Excel 8.0;HDR=Yes;"";"

'Create the connection
Dim cnn As New ADODB.Connection
Set cnn = New Connection
cnn.Open DB_CONNECT_STRING

'Test to see if we are connected
If cnn.State = adStateOpen Then
MsgBox "Welcome to! " & db_Name, vbInformation, _
"Good Luck TK"
Else
MsgBox "Sorry. No Data today."
End If

'Create the recordset
Dim Rs As ADODB.Recordset
Set Rs = New Recordset

'Determines what records to show

Dim strSql As String

'//Use to search on numbers
'strSql = "Select * from [Sheet1$A1:C100] where OrderID = " & 200 & ""
'//Search to search on strings
strSql = "Select * from [Sheet1$A1:C100] where OrderID = 'Boston'"

'Retreive the records
Rs.CursorLocation = adUseClient
Rs.Open strSql, cnn, adOpenStatic, adLockBatchOptimistic

'Copy the records to the worksheet
Worksheets("Sheet1").Range("A1").CopyFromRecordset Rs

'Close the connection
cnn.Close
Set cnn = Nothing

'Destroy the Recordset
Set Rs = Nothing

Exit Sub

BadTrip:
MsgBox "Procedure Failed"
cnn.Close
Set cnn = Nothing

End Sub

Good Luck
TK

 

[Jack]

Thanks to all!!