Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
input the date range for update Pivot table
Dear all,
Could I create two cell for input the date range and update pivot table (data from sql server) according the date range? Thanks, Jason |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
input the date range for update Pivot table
you'll have some SQL which includes the parameters?
so we have two place holders (aka tokens) in the textual SQL query string eg sSQL = "SELECT {...fields etc } FROM { blah } WHERE ( MYDATE '%date1%' AND MYDATE < '%date2%' )" its the WHERE clause you need look change MYDATE for the appropriate field from your database table if your dates are in Ranges A1 abd B1 then the next line should be sSQL = REPLACE(sSQL,"%date1%",Range("A1").Value) sSQL = REPLACE(sSQL,"%date2%",Range("B1").Value) "Jason" wrote in message ... Dear all, Could I create two cell for input the date range and update pivot table (data from sql server) according the date range? Thanks, Jason |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
input the date range for update Pivot table
Hi Patrick,
Where can I place the following statement ? macro? Best regards, Jason "Patrick Molloy" wrote: you'll have some SQL which includes the parameters? so we have two place holders (aka tokens) in the textual SQL query string eg sSQL = "SELECT {...fields etc } FROM { blah } WHERE ( MYDATE '%date1%' AND MYDATE < '%date2%' )" its the WHERE clause you need look change MYDATE for the appropriate field from your database table if your dates are in Ranges A1 abd B1 then the next line should be sSQL = REPLACE(sSQL,"%date1%",Range("A1").Value) sSQL = REPLACE(sSQL,"%date2%",Range("B1").Value) "Jason" wrote in message ... Dear all, Could I create two cell for input the date range and update pivot table (data from sql server) according the date range? Thanks, Jason |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
input the date range for update Pivot table
i'd assumed that you already had the VBA code for getting the data out of
the sql server "Jason" wrote in message ... Hi Patrick, Where can I place the following statement ? macro? Best regards, Jason "Patrick Molloy" wrote: you'll have some SQL which includes the parameters? so we have two place holders (aka tokens) in the textual SQL query string eg sSQL = "SELECT {...fields etc } FROM { blah } WHERE ( MYDATE '%date1%' AND MYDATE < '%date2%' )" its the WHERE clause you need look change MYDATE for the appropriate field from your database table if your dates are in Ranges A1 abd B1 then the next line should be sSQL = REPLACE(sSQL,"%date1%",Range("A1").Value) sSQL = REPLACE(sSQL,"%date2%",Range("B1").Value) "Jason" wrote in message ... Dear all, Could I create two cell for input the date range and update pivot table (data from sql server) according the date range? Thanks, Jason |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
input the date range for update Pivot table
Hello,
The PivotTable's filter is discrete instead of a continuous range of date. So, based on the dates in the two cells, we need to add all dates we want into the VisibleItemsList. The followings are codes I record from VBA. It updates the pivot table to only present data from 2009-01 to 2009-02. ActiveSheet.PivotTables("PivotTable2").CubeFields( 86).EnableMultiplePageItem s _ = True ActiveSheet.PivotTables("PivotTable2").PivotFields ( _ "[Time].[Year - Month - Date].[Year Name]").VisibleItemsList = Array("") ActiveSheet.PivotTables("PivotTable2").PivotFields ( _ "[Time].[Year - Month - Date].[Month Name New]").VisibleItemsList = Array( _ "[Time].[Year - Month - Date].[Year Name].&[Calendar 2009].&[Month 01]", _ "[Time].[Year - Month - Date].[Year Name].&[Calendar 2009].&[Month 02]") If you want to get notification when the date cell is modified, you can just listen to the Sheet's Change event. It will pass the modified cell as its parameter so that we can get the changed date and update our pivot table there, Private Sub Worksheet_Change(ByVal Target As Range) 'test if the target is one of the two date cells 'if yes, update your pivottable based on the two cells here ... End Sub Best regards, Colbert Zhou , remove 'online.') Microsoft Online Community Support Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update pivot table data range | Charts and Charting in Excel | |||
Date in cell to update upon Pivot Table refresh | Excel Programming | |||
Auto Update Range of a Pivot Table | Excel Programming | |||
How do I set the pivot table to auto-update the data range? | Excel Discussion (Misc queries) | |||
Macro to update pivot table data range | Excel Programming |