Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

re-directing pivotcache based on user input

  Asked By: Dale    Date: Feb 17    Category: MS Office    Views: 945
  

I need help in figuring out why below will not work, or find another
approach/solution. I have a workbook with two pivottables that use the
same pivotcache, which in turn is linked to an Access query (i.e.
OutputXXXX)

As the user opens the workbook, a userform is displayed where one can
select a year in a combobox. Depending on the selection, I want the
pivot-cache to be redirected to another output query in the Access
database.

Any thoughts on why below will not work?

Private Sub UserForm_Initialize()
Year_Combobox.List = Array("2004", "2005", "2006", "2007")
End Sub

Private Sub Year_Combobox_Change()

Dim PTCache As PivotCache
Dim pt As PivotTable
Dim NewSheet As Worksheet
Dim Constring As String
Dim DBFile As String
Dim QueryString As String
Dim Year As String
Application.ScreenUpdating = False

' Assign Value to Year
If Year_Combobox.Value = "2004" Then
QueryString = "SELECT * FROM Output2004"
ElseIf Year_Combobox.Value = "2005" Then
QueryString = "SELECT * FROM Output2005"
ElseIf Year_Combobox.Value = "2006" Then
QueryString = "SELECT * FROM Output2006"
ElseIf Year_Combobox.Value = "2007" Then
QueryString = "SELECT * FROM Output2007"
End If

'On Error Resume Next

' Refresh PivotCache in workbook with the selected Year

DBFile = ThisWorkbook.Path & "\CPHNK Måluppföljning.mdb"
Constring = "ODBC;DSN=MS Access Database; DBQ=" & DBFile

With ActiveWorkbook.PivotCaches(1)
.Connection = Constring
.CommandText = QueryString
End With

Sheets("Maluppfoljning").Activate
ActiveSheet.PivotTables("RevenuePivot").RefreshTable

Sheets("YTD_Sum_PivotTable_Sheet").Activate
ActiveSheet.PivotTables("YTD_Sum_Pivot").RefreshTable

'On Error GoTo 0

Sheets("Interface").Activate

Application.ScreenUpdating = True
End Sub

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Adelfrid Fischer     Answered On: Feb 17

Could you tell us what goes wrong? Could you remove the "'On Error Resume
Next" to see where the error is?

 
Answer #2    Answered By: Barabas Cohen     Answered On: Feb 17

Yes, I should have been more precise. :-)

It's when it reaches the .Commandtext that I get Run-time
Error '1004'. The OldQueryString and NewQueryString are
correctly "populated" with a simple SQL statement.

DBFile = ThisWorkbook.Path & "\CPHNK Måluppföljning.mdb"
Constring = "ODBC;DSN=MS Access Database; DBQ=" & DBFile

NewString = SELECT * FROM Output2007
OldString = ActiveWorkbook.PivotCaches(1).CommandText

With PTCache
.Connection = Constring
.CommandText = Replace(PTCache.CommandText, OldString,
NewString)
End With

 
Answer #3    Answered By: Stefan Thompson     Answered On: Feb 17

Try breaking it down into steps

Instead of

.CommandText = Replace(PTCache.CommandText, OldString,
NewString)

Use:

Newcommand= .CommandText
Replace(Newcommand, OldString, NewString)
.CommandText= Newcommand

Then you will find  out which part is throwing the error.

I would also put MsgBox(NewCommand) in between each of these lines

 
Didn't find what you were looking for? Find more on re-directing pivotcache based on user input Or get search suggestion and latest updates.




Tagged: