Modify Query Parameters with VBA

[robertokalos]

I would like to be able modify my query parameters by using VBA.

The VBA I recorded is:

With Selection.QueryTable
.Connection = _
"ODBC;DRIVER={AR System ODBC Driver};ARServer=HAYDN;UID=harvanek;PWD=dorotka5;ARAuthentication=;SERVER=NotTheServer"
.CommandText = Array( _
"SELECT ""CR-Ticket"".Application, ""CR-Ticket"".""Business Priority"", ""CR-Ticket"".Item, ""CR-Ticket"".""Request Date"", ""CR-Ticket"".Department, ""CR-Ticket"".""Requested by"", ""CR-Ticket"".""Service Type"", ""CR-Ticke" _
, _
"t"".""CR ID"", ""CR-Ticket"".Status, ""CR-Ticket"".""Assigned To"", ""CR-Ticket"".""Closed Date"", ""CR-Ticket"".Priority, ""CR-Ticket"".Phase, ""CR-Ticket"".""Target Release"", ""CR-Ticket"".Module, ""CR-Ticket"".Entity" & Chr(13) & "" & Chr(10) & "FRO" _
, _
"M ""CR-Ticket"" ""CR-Ticket""" & Chr(13) & "" & Chr(10) & "WHERE (""CR-Ticket"".Item In ('Borrowings','Treasury')) AND (""CR-Ticket"".Status<>'Rejected' And ""CR-Ticket"".Status<>'Closed') AND (""CR-Ticket"".Entity='Finance+Risk') AND (""CR-" _
, _
"Ticket"".""Request Date""<={ts '2011-10-31 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY ""CR-Ticket"".""Service Type"" DESC, ""CR-Ticket"".Status, ""CR-Ticket"".""Assigned To"", ""CR-Ticket"".""CR ID""" _
)
End With


The field of the query I want to be able to modify is the date (=2011-10-31). I would like to be able to use different dates. The input of the day could be a cell in the excel file.

Please Help me!!!!

 

[John_w]

Try this (untested). Assumes the date is in cell A1 of the active sheet.

With Selection.QueryTable
    .Connection = _
    "ODBC;DRIVER={AR System ODBC Driver};ARServer=HAYDN;UID=harvanek;PWD=dorotka5;ARAuthentication=;SERVER=NotTheServer"
    .CommandText = Array( _
    "SELECT ""CR-Ticket"".Application, ""CR-Ticket"".""Business Priority"", ""CR-Ticket"".Item, ""CR-Ticket"".""Request Date"", ""CR-Ticket"".Department, ""CR-Ticket"".""Requested by"", ""CR-Ticket"".""Service Type"", ""CR-Ticke" _
    , _
    "t"".""CR ID"", ""CR-Ticket"".Status, ""CR-Ticket"".""Assigned To"", ""CR-Ticket"".""Closed Date"", ""CR-Ticket"".Priority, ""CR-Ticket"".Phase, ""CR-Ticket"".""Target Release"", ""CR-Ticket"".Module, ""CR-Ticket"".Entity" & Chr(13) & "" & Chr(10) & "FRO" _
    , _
    "M ""CR-Ticket"" ""CR-Ticket""" & Chr(13) & "" & Chr(10) & "WHERE (""CR-Ticket"".Item In ('Borrowings','Treasury')) AND (""CR-Ticket"".Status<>'Rejected' And ""CR-Ticket"".Status<>'Closed') AND (""CR-Ticket"".Entity='Finance+Risk') AND (""CR-" _
    , _
    "Ticket"".""Request Date""<={ts '" & Format(Range("A1").Value, "yyyy-mm-dd 00:00:00") & "'})" & Chr(13) & "" & Chr(10) & "ORDER BY ""CR-Ticket"".""Service Type"" DESC, ""CR-Ticket"".Status, ""CR-Ticket"".""Assigned To"", ""CR-Ticket"".""CR ID""" _
    )
End With

 

[robertokalos]

It worked!!!!!

Thank you a lot!!

Have a great day