Hi All,

nowadays I'm working on a blueprint to develop a customized ordering system and have following question : 'Is it possible to search for records in a sheet using the SQL language? (ADO?)' the sheet I want to Qry is inside the same workbook were all forms are located.

Hope someone have an answer for this one

Thx and greetings from Belgium

S.

Either import into Access and run from there or have a look at QueryTables (Data -> Import External Data -> New Database Query...)

Maccas

not possible, the application should run in XLS,
is it possible to create a recordset via code from an xls range?

Do QueryTables not work for you? I think you can get them to work as XL only.

never used, can you explain in a few lines?

Hi,

You can find this macro in 'excel 2003 VBA' from WROX

Public Sub QueryWorksheet()

  Dim RS As ADODB.Recordset
  Dim ConnString As String

  'inside the same workbook
  ConnString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
    "Extended Properties=Excel 8.0;"

  'another workbook
  ConnString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ThisWorkbook.Path & "\The other workbooks name.xls;" & _
    "Extended Properties=Excel 8.0;"


  Dim SQL As String

  ' Query based on the worksheet name.
  SQL = "SELECT * FROM [Sales$]"

  ' Query based on a sheet level range name.
  ' SQL = "SELECT * FROM [Sales$MyRange]"
  ' Query based on a specific range address.
  ' SQL = "SELECT * FROM [Sales$A1:E14]"
  ' Query based on a book level range name.
  ' SQL = "SELECT * FROM BookLevelName"

  Set RS = New ADODB.Recordset

  On Error GoTo Cleanup

  Call RS.Open(SQL, ConnString, _
    CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _
    CommandTypeEnum.adCmdText)

Dim aField As ADODB.Field
Dim NextCell As Integer

With Sheet2.Range("a1")
  For Each aField In RS.Fields
    .Offset(0, NextCell).Value = aField.Name
    NextCell = NextCell + 1
  Next aField
  .Resize(1, RS.Fields.Count).Font.Bold = True
End With

  Call Sheet2.Range("a2").CopyFromRecordset(RS)

Cleanup:
  If (Err.Number <> 0) Then
    Debug.Print Err.Description
  End If

  If (RS.State = ObjectStateEnum.adStateOpen) Then
    RS.Close
  End If

Set RS = Nothing
End Sub