Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO to access another workbook
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO to access another workbook
"Select * from [Recovered_Sheet1$] where Property='Boston'"
HTH. Best wishes Harald "Jack" skrev i melding ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO to access another workbook
Hi Jack
Start here http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jack" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO to access another workbook
Hi Jack
"Jack" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO to access another workbook
Thanks to all!!
-----Original Message----- 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
access cell in other workbook | Excel Discussion (Misc queries) | |||
link Access workbook to Excel workbook | Excel Discussion (Misc queries) | |||
Access values from another workbook | Excel Programming | |||
Can't access to a WorkBook | Excel Programming | |||
Can't access to a Workbook | Excel Programming |