Download PivotTable Parameters

There were a lot of comments that people couldn’t get parameters into their pivot tables. I didn’t save the workbook with which I made that post, but I was able to recreate it. You can download it:


I didn’t have any trouble recreating it, other than changing the Sheet2 reference in the code to Sheet1. I’m sure I’m just blowing by a step and not explaining it. Hopefully looking at the workbook will shed some light on why others couldn’t get it to work.

Note that the external data that my pivot table points to will not likely be in the same path on your machine. From the Immediate Window, my stuff looks like this:

ODBC;DSN=MS Access Database;DBQ=C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind.mdb;DefaultDir=C:Program FilesMicrosoft Office 2000OfficeSamples;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

SELECT Invoices.PostalCode, Invoices.ExtendedPrice
FROM ‘C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind’.Invoices Invoices
WHERE (Invoices.PostalCode=?)

You’ll have to change those paths to get it to work on your machine. You may, however, not need it to work, but just seeing the differences between this workbook and yours may be all you need.

Posted in Uncategorized

7 thoughts on “Download PivotTable Parameters

  1. Interesting, with Excel 2003, the macro fails silently. When I attempt to put the parameter in via MS Query, I get the response “Parameters are not allowed for this query”

    (And the path to Northwind has been adjusted)



    This link doesn’t work. Can you repair it ?

    Thank you,

    H.G. Lamy

  3. Hi all,

    It seems that the Replace-command does not do the work properly:

    pc.CommandText = Replace(pc.CommandText, “‘05022′”, “?”, , 1)

    Gives “‘?'” and therefore it’s treated as a string and not as a parameter.

    Running the code gives an empty pivottable.

    This happen for me when running 2003.

    One way to solve it and also take more control of the process itself is to use the following approach:

    Option Explicit

    Sub Test()
    Dim ptCache As PivotCache
    Dim wsSheet As Worksheet
    Dim stSQL As String

    Set wsSheet = ActiveSheet

    With wsSheet
    Set ptCache = .PivotTables(1).PivotCache
    End With

    stSQL = “SELECT Orders.Freight, Orders.ShipCountry ” & _
    “FROM ‘C:Northwind’.Orders Orders ” & _
    “WHERE (Orders.ShipCountry=?);”

    ptCache.CommandText = stSQL

    End Sub

    Personally I sometimes prefer to use ADO instead of ODBC but that’s another story.

    Kind regards,

  4. OK, promted by Dennis’s work, and investigating further:

    pc.CommandText = Replace(pc.CommandText, ìë05022?î, ì?î, , 1) fails because their is no ‘05022’ in the customers table.

    There is a 5022 postal code though.

    changing “‘05022′” to “5022? – and the code snippet works just fine.

    Of couse, since my knowledge of databases and SQL is almost non-existant – there could be a reason for the 0-padding of the postal code field… :-)

    (And still curious why it failed silently)


  5. Christopher,

    In my version of Nortwind.mdb both 05022 and 5022 exist in the output of the query Invoices.

    So the issue still remains.

    (5022 is a numeric value while ‘05022’ is a text value.)

    Oh well, when I got more time I will take a closer look into it as well.

    Kind regards,

  6. In my version of Nortwind.mdb both 05022 and 5022 exist in the output of the query Invoices

    yup – that’s true…apologies for the confusion.

    I should have said:

    pc.CommandText = Replace(pc.CommandText, ìë05022?î, ì?î, , 1) “fails” because the CommandText of the pivot cache contains ‘5022’ in the SQL statement and not ‘05022’.

    When you open up the file, the selected item in the pivot table is for post code 5022 and not 05022.

    Which is why if ‘fails silently’ – it’s not failing at all…there’s no text to susbsitute! (Don’t you hate it when code does what it should and not what you assume!)


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.