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: 1946

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.

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

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



'On Error GoTo 0


Application.ScreenUpdating = True
End Sub



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,
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,


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.