Thread: [Macro] Excel Query Connection with Inputbox for changing filters

Dear All,

I'm currently trying to write a macro that will establish a connection with a database. I want to use this macro for a couple of hundred customer sheets that all need te same connection to the database but the connection should have a different customer filter for every sheet.
I need this filter because otherwise the pivottable would make the file too large.

Below you wil see the macro. The part where i use the MyInput in the query connection is this part:

(`Turnover KE24`.Customer='MyInput')" _

Somehow when i try to use the makro with a different customer it returns the customer on which the macro was written. I don't understand why this happens!

Does anyone know?

Sub QueryCustomer()
'
' QueryCustomer Macro
'

'
MyInput = InputBox("Enter Customernumber for Query")

    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "PIVOTS"
    Sheets("PIVOTS").Select
    Range("A1").Select
    ActiveWorkbook.Connections.Add "Query from MS Access Database1" _
        , "", Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=Z:\General\DATABASE_spllit.accdb;DefaultDir=Z:\General;DriverId=25;FIL=MS Access;MaxBufferSize=2048;" _
        ), Array("PageTimeout=5;")), Array( _
        "SELECT `Turnover KE24`.ID, `Turnover KE24`.Period, `Turnover KE24`.InvDate, `Turnover KE24`.Pstgdate, `Turnover KE24`.BusA, `Turnover KE24`.SOrg, `Turnover KE24`.CustHier01, `Turnover KE24`.HierName, " _
        , _
        "`Turnover KE24`.Customer, `Turnover KE24`.CustomerName, `Turnover KE24`.Billto, `Turnover KE24`.Shipto, `Turnover KE24`.Payer, `Turnover KE24`.MG1, `Turnover KE24`.Dv, `Turnover KE24`.MatlGroup, `Turn" _
        , _
        "over KE24`.Product, `Turnover KE24`.SGSAmt, `Turnover KE24`.SReturnAmt, `Turnover KE24`.SSaleqty, `Turnover KE24`.SRetQty, `Turnover KE24`.SOthSale, `Turnover KE24`.OrdRs, `Turnover KE24`.DChl" & Chr(13) & "" & Chr(10) & "FROM `" _
        , _
        "Z:\General\DATABASE_spllit.accdb`.`Turnover KE24` `Turnover KE24`" & Chr(13) & "" & Chr(10) & "WHERE (`Turnover KE24`.Customer='MyInput')" _
        ), 2
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("Query from MS Access Database1"), Version:= _
        xlPivotTableVersion12).CreatePivotTable TableDestination:="Pivots!R1C1", _
        TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion12
    Cells(1, 1).Select