Excel SQL Parameters from cell

  • I have excel setup to populate a table A-E. there are ~30 sheets that contain a form of the sql file each one is pulling data for each day of the month. So, Sheet1 has data for 10/01/2012 However, at the end of the month I have to create a new excel sheet and manually change the dates to ie. November, December. I am wondering if there is a way to have the sql code pull from the sql server and have a parameter for a cell in excel for the "between" dates. lets say the START date for Sheet1 is in "Sheet1'!H3 and END date is in "Sheet1'!H4


    SQL
    SELECT dispatch_history.user_id, dispatch_history.history_code, DATEADD(hh,-5,dispatch_history.time) AS 'TIME', dispatch_history.dispatch_number AS 'PO#', dispatch.dispatch_code
    FROM ERS.dbo.dispatch dispatch, ERS.dbo.dispatch_history dispatch_history
    WHERE dispatch.dispatch_number = dispatch_history.dispatch_number AND ((dispatch_history.history_code='ACCEPT') AND (dispatch_history.time Between {ts '2012-10-01 05:00:00'} And {ts '2012-10-02 05:00:00'}) OR (dispatch_history.history_code='CANCDISP') AND (dispatch_history.comments Like 'Cancel Reason = MOVED%') AND (dispatch_history.time Between {ts '2012-10-01 05:00:00'} And {ts '2012-10-02 05:00:00'}))


    fyi: the time above shows its pulling 2012-10-01 @5am to offset the GMT time


    Thanks

  • Re: Excel SQL Parameters from cell


    If thats not possible, what about a Macro to copy a Cell which contains the concatenated or (=A1&" "&A2) sql formula to make it work....I have the excel formula ready to copy but when i tried this as soon as I copied the cell and clicked "properties" for the sql table the copy comand resets and nothing is there...


    for example:


    the code I need copied is in sheet1!A1 and I need it pasted into the "command Text" of the sql. I cannot seem to format a macro to paste because excel "forgets" whats copied as soon as a dialog box opens up.


    Thanks!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!