Hi,
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 dailyto
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?
Hi Keith
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
example
Select item, quantity, retail from table where date between 20100101
and 2010228
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
fields.
For example,
Select
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.
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 c
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
Next
'Open the recordset
Set rs = AS400Conn.Execute(QueryString, , adCmdText)
'Populate the pivot table cache from the record set
Set ObjPivotCache =
ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set ObjPivotCache.Recordset = rs
'create a temporary sheet and pivot table to use the new cache
Set wsTemp = Worksheets.Add
Set pt = ObjPivotCache.CreatePivotTable _
(TableDestination:=wsTemp.Range("A3"), TableName:="Temp")
'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
wsTemp.Delete
Application.DisplayAlerts = True
Exit_cmdImport_Click:
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
Range("A1").Select
Exit Sub
Err_cmdImport_Click:
MsgBox Err.Description
Resume Exit_cmdImport_Click
End Sub
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
a sheet.
Jim P
