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:
Download PivotTableParameters.zip
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;
?sheet1.PivotTables(1).pivotcache.commandtext
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.
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)
cheers,
Christopher
PivotTableParameters.zip
This link doesn’t work. Can you repair it ?
Thank you,
H.G. Lamy
Link is fixed.
I did it in Excel 2000, but will check it out in 2003 to see if I get the same results.
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,
Dennis
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)
cheers,
Christopher
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,
Dennis
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!)
cheers,
Christopher