Query base off Excel Parameter??

[gmatriix]

Hello All,

I have this code below. What I am trying to do is to have this code to query base of a cell in excel. For instance:

If I type in 28 in say....A2 it will change the query and return the data base on "28". I am alittle confused on where to put the "WHERE" clause.???

And Ideas??? Please help!

Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=F:\BPM\NAL Metrics & Reporting\Metric-RDB.mdb;DefaultDir=F:\BPM\NAL Metrics & Reporting;DriverId=25;" _
        ), Array("FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:= _
        Range("A1"))
        .CommandText = Array( _
        "SELECT Query1.Week_No, Query1.Period_No, Query1.Quarter_No, Query1.Program, Query1.DC, Query1.Late1, Query1.TTCLate, Query1.`Root Cause Code`, Query1.Detail" & Chr(13) & "" & Chr(10) & "FROM `F:\BPM\NAL Metrics & Reporting\Metri" _
        , "c-RDB`.Query1 Query1" "WHERE Query1.Week_No = ("A2"))"
        .Name = "Query from MS Access Database"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Thanks

 

[ajetrumpet]

Just thought I would say hi. How are ya?