423,113 Members | 1,957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,113 IT Pros & Developers. It's quick & easy.

Filter by value expressed in a cell not in the VBA

P: 34
I have the following code which pulls the results of a table in Microsoft access into excel. Currently the code filters the results from within the code, however I would like to filter the results by the vaule expressed in a cell.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Sub ADO_Demo()
  4. '   This demo requires a reference to
  5. '   the Microsoft ActiveX Data Objects 2.x Library
  6.  
  7.     Dim DBFullName As String
  8.     Dim Cnct As String, Src As String
  9.     Dim Connection As ADODB.Connection
  10.     Dim Recordset As ADODB.Recordset
  11.     Dim Col As Integer
  12.  
  13.     Cells.Clear
  14.     MsgBox "This demo retrieves the data for the records in which ITEM = LEASE and DIVISION = N. AMERICA."
  15.  
  16. '   Database information
  17.     DBFullName = ThisWorkbook.Path & "\budget.mdb"
  18.  
  19. '   Open the connection
  20.     Set Connection = New ADODB.Connection
  21.     Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
  22.     Cnct = Cnct & "Data Source=" & DBFullName & ";"
  23.     Connection.Open ConnectionString:=Cnct
  24.  
  25. '   Create RecordSet
  26.     Set Recordset = New ADODB.Recordset
  27.     With Recordset
  28. '       Filter
  29.         Src = "SELECT * FROM Budget WHERE Item = 'Lease' "
  30.         Src = Src & "and Division = 'N. America'"
  31.         .Open Source:=Src, ActiveConnection:=Connection
  32.  
  33. '       Write the field names
  34.         For Col = 0 To Recordset.Fields.Count - 1
  35.            Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
  36.         Next
  37.  
  38. '       Write the recordset
  39.         Range("A1").Offset(1, 0).CopyFromRecordset Recordset
  40.     End With
  41.     Set Recordset = Nothing
  42.     Connection.Close
  43.     Set Connection = Nothing
  44. End Sub
  45.  
Dec 13 '07 #1
Share this Question
Share on Google+
1 Reply


FishVal
Expert 2.5K+
P: 2,653
Assuming the cells are on worksheet "Input", cells B1, B2.

Expand|Select|Wrap|Line Numbers
  1. ................
  2. Src = "SELECT * FROM Budget WHERE Item = '" & ThisWorkBook.WorkSheets("Input").Range("B1") & "' "
  3. Src = Src & "and Division = '" & ThisWorkBook.WorkSheets("Input").Range("B2") & "'"
  4. ................
  5.  
Dec 16 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.