Prev: colorscales, icon sets, and databars referring to remote cells
Next: excel 2010 question regarding determining the last row
From: Keith on 11 Mar 2010 18:37
We use parameters and Excel formulas (Start Date=Now()+1 year, End
Date=Now(), etc.) to pull sales detail from an IBM system and it works fine.
I want to use a piivot table to summarize 14,000+ lines of details into
about 800 parts. Works great but I need to change the date range daily to
capture 1 year of history.
This is one of 12 different queries in the same file we want to do this
with. Is there a way to use parameters in a Pivot Table like we are now
doing with an MS Query?
From: Jim P on 12 Mar 2010 09:45
On Mar 11, 6:37 pm, Keith <Ke...(a)discussions.microsoft.com> wrote:
> We use parameters and Excel formulas (Start Date=Now()+1 year, End
> Date=Now(), etc.) to pull sales detail from an IBM system and it works fine.
> I want to use a piivot table to summarize 14,000+ lines of details into
> about 800 parts. Works great but I need to change the date range daily to
> capture 1 year of history.
> This is one of 12 different queries in the same file we want to do this
> with. Is there a way to use parameters in a Pivot Table like we are now
> doing with an MS Query?
The short answer is you can't use parameters when MS query returns the
record set to a pivot table.
I had some assistance from Debra Dagleish on this code. See www.contextures.com
The trick is you start with an existing pivot table and then replace
it with a new cache.
I have a range named QueryRows which contains the SQL used, for
Select item, quantity, retail from table where date between 20100101
You can enter the variable parameters into a cell and then use
contcatenation to vary the lines in your SQL.
For example =" where date between "&fromdate&" and "&todate
One other thing is you may have to use the "zoned" funciton on packed
zoned(itemtable.number) as item,
zoned(inventory.quantity) as On_hand
There's a command button on the worksheet containing the pivot table
wich has the following code.
Private Sub Import_Click()
On Error GoTo Err_cmdImport_Click
Dim AS400Conn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim ObjPivotCache As PivotCache
Dim ptOld As PivotTable
Dim QueryString As String
Dim pt As PivotTable
Dim strCmd As String
Dim ws As Worksheet
Dim wsTemp As Worksheet
Set ws = ActiveSheet
Set ptOld = ws.Cells(5, 1).PivotTable
Application.Calculation = xlCalculationManual
Set AS400Conn = New ADODB.Connection
AS400Conn.Open "Provider=IBMDA400;Data Source=000.000.000.000
(your tcpip address for the 400", "", ""
For Each c In Worksheets("SQL").Range("QueryRows").Cells
QueryString = QueryString + c.Value
'Open the recordset
Set rs = AS400Conn.Execute(QueryString, , adCmdText)
'Populate the pivot table cache from the record set
Set ObjPivotCache =
Set ObjPivotCache.Recordset = rs
'create a temporary sheet and pivot table to use the new cache
Set wsTemp = Worksheets.Add
Set pt = ObjPivotCache.CreatePivotTable _
'change old pivot table to use the new cache
ptOld.CacheIndex = pt.CacheIndex
'delete the temporary sheet and pivot table
Application.DisplayAlerts = False
On Error Resume Next
Application.DisplayAlerts = True
Set ObjPivotCache = Nothing
Set rs = Nothing
Set objCmd = Nothing
Set AS400Conn = Nothing
'Toggle off the display of the pivot table field list
ActiveWorkbook.ShowPivotTableFieldList = False
'Switch calculation back to automatic
Application.Calculation = xlCalculationAutomatic
I know it looks pretty complicated. It works welll when you need to
retrieve more than the 65,000 rows that Excel 2003 can accommodate in