Hello All,

I have a decent understanding of excel. Notwithstanding, I am still a bit of a novice when it comes to VBA.

Here is my dilemma:
In an attempt to automate a query within one of my workbooks I recorded a simple macro to run/update said query. However, there is an issue... my query was setup in a way that always requires the end user to update the date range within the query. For example, when I want to update my data for the month of August 2015 I need to update the date range to 201508, which equals August 2015 in SQL language. So by default my recorded macro has a reference to a specific date for the query update, in this case 201508. I need to be able to update this date by simply referencing a cell in my workbook (let's say cell A1 on the worksheet tab) which will be the point of reference for my macro's date range. So if someone wants to see this information for September 2015 they can type in 201509 in a cell and run the macro for a quick update. I haven't decide on whether the macro will run when a date range is enter in the cell or if there will be a button. Regardless, any one have any ideas on how I can go about making changes to the below string to make this happen?

Sub Macro_IC_Invoice()
'
' Macro_IC_Invoice Macro
'

'
    Range("A2").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    With Selection.ListObject.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=Proddb - Live;Description=Live Production;UID=sa;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=DAVA-BRDG-L;DATABAS" _
        ), Array("E=proddb;LANGUAGE=us_english"))
        .CommandText = Array( _
        "Select ad.accounting_period,ad.fiscal_per_nbr,ad.co_code,ad.org_code,ad.acct_code,ad.currency_code,ad.sys_doc_type_code,ad.usr_batch_id,ad.db_amt_ac,ad.db_amt_cc,ad.cr_amt_ac,ad.cr_amt_cc,ad.prj_code," _
        , _
        "fcs.trn_desc" & Chr(13) & "" & Chr(10) & "from Proddb..gl_acct_detail ad " & Chr(13) & "" & Chr(10) & "left join proddb..fcs_trn_desc fcs ON ad.fcs_desc_skey = fcs.fcs_desc_skey " & Chr(13) & "" & Chr(10) & "Where substring(ad.acct_code,1,1) in ('I','P','R') " & Chr(13) & "" & Chr(10) & "and ad.accounting_perio" _
        , _
        "d = 201508" & Chr(13) & "" & Chr(10) & "and ad.usr_batch_id not like ('RGL') " & Chr(13) & "" & Chr(10) & "and (ad.acct_code like '%731%'  " & Chr(13) & "" & Chr(10) & "or ad.acct_code like '%711%')" & Chr(13) & "" & Chr(10) & "and ad.cr_amt_ac = 0" & Chr(13) & "" & Chr(10) & "" _
        )
        .Refresh BackgroundQuery:=False
    End With
    Sheets("IC Allocation Detail Query").Select
    Range("A2").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    With Selection.ListObject.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=Proddb - Live;Description=Live Production;UID=sa;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=DAVA-BRDG-L;DATABAS" _
        ), Array("E=proddb;LANGUAGE=us_english"))
        .CommandText = Array( _
        "SELECT ad.co_code, ad.fiscal_per_nbr, ad.fiscal_year, ad.org_code, ad.acct_code, ad.db_amt_cc, ad.cr_amt_cc, ad.db_amt_ac, ad.cr_amt_ac, ad.alloc_co_code, ad.gl_alloc_code, ad.posting_date, ad.account" _
        , _
        "ing_period, ad.mod_date" & Chr(13) & "" & Chr(10) & "FROM Proddb.dbo.gl_alloc_detail ad" & Chr(13) & "" & Chr(10) & "WHERE (ad.acct_code Like '%731%') AND (substring(ad.acct_code,1,1) In ('I','P','R')) AND (ad.accounting_period=201508) OR (ad.acct_code Lik" _
        , _
        "e '%711%') AND (substring(ad.acct_code,1,1) In ('I','P','R')) AND (ad.accounting_period=201508)" _
        )
        .Refresh BackgroundQuery:=False
    End With
End Sub