I had the same problem as the OP. See the posts by WillR in the link below for a description of how to set up parameters for your queries. I will try to give my explanation as well.
http://www.mrexcel.com/archive2/19400/22013.htm
Open your query in the MS Query editor. Change the hard-coded date (or whatever the data-type) to "[Parameter_Name]". When you press enter, you will be prompted to enter a value for the parameter.
Return to Excel, right click on your query and select "Parameters". Select the third radio button "Get the value from the following cell" and then enter the cell reference. You can check the box below that to have it auto-refresh when the value of the cell changes.
This should work, and is far simpler than coding it into VBA.
