Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Programming
Hi,
I have a pivot table and I'm tryng to change the page data dynamically. The page data is a period dimension and based on the quarter, and month, it should filter the page area...here is my code below and I will explain where the problem is below... This is a custom function that gets the quarter...nothing wrong here Function QTRNum(ENTER_DATE) QTRNum = DatePart("q", ENTER_DATE) End Function ----------------------------------------------------------- This is the function that does the work. Function currMonth() 'Declare some stuff Dim D As String Dim Y As String Dim m As String Dim Q As String 'Set values. I'm palcing the format of the data for the for the page field Q = "[Quarter " & Module5.QTRNum(Date) & "]" Y = "[" & Year(Date) & "]" m = "[" & MonthName(Month(Date)) & "]" 'This simply assigns the .addpageitem to the currMonth variable. I know the value is correct, because I will debug.pring CurrMonth and then paste the results in the procedure and it executes fine. It's like it won't take a string for this method. It produces this...AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3].[October]" currMonth = ".AddPageItem " & Chr(34) & "[PERIOD].[All PERIOD]" & "." & Y & "." & Q & "." & m & Chr(34) End Function ---Now, this is what should set the filter With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]") .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]" .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]" Module5.currMonth End With HELP PLEASE!!!!!!!!!!!! Expand AllCollapse All Manage Your Profile |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Programming
If I'm understanding this correctly, it looks like you're trying to use the
results of the curMonth function (which is a string) and wanting it to execute like it was code. There may be some way to do that, but why don't you just have curMonth return just the string: currMonth = "[PERIOD].[All PERIOD]" & "." & Y & "." & Q & "." & m & Chr(34) End Function And then down in the rest of your code call the .AddPageItem method with the string returned from curMonth: With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]") .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]" .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]" .AddPageItem Module5.currMonth End With -Erik "OrrLyfe" wrote in message ... Hi, I have a pivot table and I'm tryng to change the page data dynamically. The page data is a period dimension and based on the quarter, and month, it should filter the page area...here is my code below and I will explain where the problem is below... This is a custom function that gets the quarter...nothing wrong here Function QTRNum(ENTER_DATE) QTRNum = DatePart("q", ENTER_DATE) End Function ----------------------------------------------------------- This is the function that does the work. Function currMonth() 'Declare some stuff Dim D As String Dim Y As String Dim m As String Dim Q As String 'Set values. I'm palcing the format of the data for the for the page field Q = "[Quarter " & Module5.QTRNum(Date) & "]" Y = "[" & Year(Date) & "]" m = "[" & MonthName(Month(Date)) & "]" 'This simply assigns the .addpageitem to the currMonth variable. I know the value is correct, because I will debug.pring CurrMonth and then paste the results in the procedure and it executes fine. It's like it won't take a string for this method. It produces this...AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3].[October]" currMonth = ".AddPageItem " & Chr(34) & "[PERIOD].[All PERIOD]" & "." & Y & "." & Q & "." & m & Chr(34) End Function ---Now, this is what should set the filter With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]") .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]" .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]" Module5.currMonth End With HELP PLEASE!!!!!!!!!!!! Expand AllCollapse All Manage Your Profile |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Programming
Hi Eirk, Thanks for the reply, but now I'm getting message "Item could not be
found in OLAP cube". If I debug.print currMonth, copy and paste the results directly after .Addpageitem, it works. So that tells me that the value is correct. Any other ideas? "E Oveson" wrote: If I'm understanding this correctly, it looks like you're trying to use the results of the curMonth function (which is a string) and wanting it to execute like it was code. There may be some way to do that, but why don't you just have curMonth return just the string: currMonth = "[PERIOD].[All PERIOD]" & "." & Y & "." & Q & "." & m & Chr(34) End Function And then down in the rest of your code call the .AddPageItem method with the string returned from curMonth: With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]") .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]" .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]" .AddPageItem Module5.currMonth End With -Erik "OrrLyfe" wrote in message ... Hi, I have a pivot table and I'm tryng to change the page data dynamically. The page data is a period dimension and based on the quarter, and month, it should filter the page area...here is my code below and I will explain where the problem is below... This is a custom function that gets the quarter...nothing wrong here Function QTRNum(ENTER_DATE) QTRNum = DatePart("q", ENTER_DATE) End Function ----------------------------------------------------------- This is the function that does the work. Function currMonth() 'Declare some stuff Dim D As String Dim Y As String Dim m As String Dim Q As String 'Set values. I'm palcing the format of the data for the for the page field Q = "[Quarter " & Module5.QTRNum(Date) & "]" Y = "[" & Year(Date) & "]" m = "[" & MonthName(Month(Date)) & "]" 'This simply assigns the .addpageitem to the currMonth variable. I know the value is correct, because I will debug.pring CurrMonth and then paste the results in the procedure and it executes fine. It's like it won't take a string for this method. It produces this...AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3].[October]" currMonth = ".AddPageItem " & Chr(34) & "[PERIOD].[All PERIOD]" & "." & Y & "." & Q & "." & m & Chr(34) End Function ---Now, this is what should set the filter With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]") .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]" .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]" Module5.currMonth End With HELP PLEASE!!!!!!!!!!!! Expand AllCollapse All Manage Your Profile |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Programming
Hi,
Looking at your code more closely, it looks like you have chr(34)'s wrapping your string, which would end up with a string that has quotations inside the string. Then when you debug.print'd it returned the string with quotations which you could just paste into the code and it would run as expected. But when you ran the code, it had an extra set of quotations and so it could not find that item. This is my guess at what is going wrong... currMonth = "[PERIOD].[All PERIOD]" & "." & Y & "." & Q & "." & m 'strip away extra quotes That is probably the problem. If not, whatever's the problem, it must be that there is some small difference between what you have pasted in manually, and what currMonth is returning. -Erik "OrrLyfe" wrote in message ... Hi Eirk, Thanks for the reply, but now I'm getting message "Item could not be found in OLAP cube". If I debug.print currMonth, copy and paste the results directly after .Addpageitem, it works. So that tells me that the value is correct. Any other ideas? "E Oveson" wrote: If I'm understanding this correctly, it looks like you're trying to use the results of the curMonth function (which is a string) and wanting it to execute like it was code. There may be some way to do that, but why don't you just have curMonth return just the string: currMonth = "[PERIOD].[All PERIOD]" & "." & Y & "." & Q & "." & m & Chr(34) End Function And then down in the rest of your code call the .AddPageItem method with the string returned from curMonth: With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]") .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]" .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]" .AddPageItem Module5.currMonth End With -Erik "OrrLyfe" wrote in message ... Hi, I have a pivot table and I'm tryng to change the page data dynamically. The page data is a period dimension and based on the quarter, and month, it should filter the page area...here is my code below and I will explain where the problem is below... This is a custom function that gets the quarter...nothing wrong here Function QTRNum(ENTER_DATE) QTRNum = DatePart("q", ENTER_DATE) End Function ----------------------------------------------------------- This is the function that does the work. Function currMonth() 'Declare some stuff Dim D As String Dim Y As String Dim m As String Dim Q As String 'Set values. I'm palcing the format of the data for the for the page field Q = "[Quarter " & Module5.QTRNum(Date) & "]" Y = "[" & Year(Date) & "]" m = "[" & MonthName(Month(Date)) & "]" 'This simply assigns the .addpageitem to the currMonth variable. I know the value is correct, because I will debug.pring CurrMonth and then paste the results in the procedure and it executes fine. It's like it won't take a string for this method. It produces this...AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3].[October]" currMonth = ".AddPageItem " & Chr(34) & "[PERIOD].[All PERIOD]" & "." & Y & "." & Q & "." & m & Chr(34) End Function ---Now, this is what should set the filter With ActiveSheet.PivotTables("YTD").PivotFields("[PERIOD]") .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 1]", True .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 2]" .AddPageItem "[PERIOD].[All PERIOD].[2004].[Quarter 3]" Module5.currMonth End With HELP PLEASE!!!!!!!!!!!! Expand AllCollapse All Manage Your Profile |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Excel Food Price Table, Programming, Is This Table All Right? | Excel Discussion (Misc queries) | |||
Pivot table Programming | Excel Programming | |||
VBA Programming with Pivot Tables | Excel Programming | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |