Every day, I run queries in Access and paste them into Excel. I have been working on a macro to use Microsoft Query to directly import the data using a macro. However, I'm not sure how to introduce a variable condition, for lack of a better way to put it.

Here's an example of what I've been working on:
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=MS Access Database;DBQ=C:\ReportsNew.mdb;DefaultDir=C:;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=Range("A3"))
        .CommandText = Array( _
        "SELECT `zExQuery Weight 1`.State, `zExQuery Weight 1`.SiteNumber, `zExQuery Weight 1`.SiteName, `zExQuery Weight 1`.GrossWeight, `zExQuery Weight 1`.Axle, `zExQuery Weight 1`.Tandem, `zExQuery Weight " _
        , _
        "1`.Steering, `zExQuery Weight 1`.DataDate" & Chr(13) & "" & Chr(10) & "FROM `C:\ReportsNew`.`zExQuery Weight 1` `zExQuery Weight 1`" & Chr(13) & "" & Chr(10) & "WHERE (`zExQuery Weight 1`.DataDate={ts '2006-11-16 00:00:00'})" _
        )
        .Name = "Query from MS Access Database"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
But rather than "DataDate={ts '2006-11-16 00:00:00'})"" I would like some way to search for records for a specific date. If it matters, I am always look for records from yesterday. If there is a way to introduce a variable, say from a cell in my spreadsheet, that would be great.

One last thing, though I can work around this detail... I would like to search by date, but I'd prefer not to get the dates back in the data (as they'll all be the same anyway). Can you have a field as a condition without making it part of the results from the query ?

Thank you in advance.