Can't Refresh PivotTable

[Pepe]

Hi, I'm having a terrible time trying to figure out how to enable the
Refresh Option for my PivotTable. I'm going to list my code below
(sorry it's so long).

Basically, I'm connecting to a SQL Server and using that recordset to
create a pivot. I want to be able to refresh that PivotTable each day
rather than having to re-create the Pivot each time. I will also have
other PivotTables based on the same PivotCache and will need to have
those refreshed in turn.

I think the trouble might be in the way I'm handling the Recordset or
PivotCache, but truthfully I don't know. I've put a couple options in
to EnableRefresh, but neither seem to do any good.

Please, Please come back with ANY thoughts on this.

Here is my Code: (again, sorry for the full post of it)
------------------------------------------------------------------------------
Option Explicit

Private cnn As ADODB.Connection
------------------------------------------
Private Sub OpenConnection()
Set cnn = New ADODB.Connection
cnn.CommandTimeout = 900
cnn.ConnectionTimeout = 900
cnn.Mode = adModeShareDenyNone
cnn.Open "Driver={SQL
Server};Server=xxxxxx;DB=xxxxxx;UID=xxxxxx;PWD=xxxxxx"
End Sub
------------------------------------------
Private Sub CloseConnection()
cnn.Close
Set cnn = Nothing
End Sub
------------------------------------------
Public Sub GetData()

Dim DataSheet As Worksheet
Dim rst As New ADODB.Recordset
Dim Statement As String
Dim StartDate As Date
Dim MyPC As PivotCache
Dim MyPT As PivotTable

Set DataSheet = ThisWorkbook.Worksheets("Data")

StartDate = DataSheet.Range("F1")

'Get Data From SQL View
OpenConnection

Statement = "SELECT UpdateDate, Insertby, Campaign, Resolution1, "
& _
"Resolution2, Resolution3, Stage, MonthlyCost, " & _
"ProspectId, UpdateWeekRange, UpdateMonthShrt " & _
"FROM vStageCountDNIS " & _
"WHERE UpdateDate > '" & Format(StartDate,
"mm/dd/yyyy") & "'" & ";"

'Open Recordset
rst.Open Statement, cnn, adOpenForwardOnly, adLockReadOnly

'Create PivotCache
Set MyPC = ThisWorkbook.PivotCaches.Add(xlExternal)
Set MyPC.Recordset = rst
MyPC.EnableRefresh = True

'Create PivotTable
Set MyPT = DataSheet.PivotTables.Add(MyPC, Cells(4, 1))
MyPT.Name = "Master"
MyPT.PivotCache.EnableRefresh = True

MyPT.AddFields RowFields:="Campaign", _
PageFields:=Array("Insertby", "Resolution1", "Resolution2",
"Resolution3", _
"Stage", "MonthlyCost", "UpdateMonthShrt", "UpdateWeekRange")

With MyPT.PivotFields("ProspectId")
.Orientation = xlDataField
.Caption = "Count"
.Function = xlCount
End With

rst.Close
Set rst = Nothing
CloseConnection

Set DataSheet = Nothing

End Sub
------------------------------------------
------------------------------------------------------------------------------

Again, thanks for ANY suggestions here. I've been stuck on this one
for a while and really need some sort of a solution.

Thanks

Paul

 

[Dick Kusleika]

Paul

Ok, you asked for ANY thoughts, so this may be way off base. In your
message, you say you don't want to recreate the pivot table each time. But
in your code, it appears that's what you're doing. If you just want to
refresh existing pivot tables, it seems that you could use a macro like this

Sub test()

Dim pc As PivotCache
Dim pt As PivotTable
Dim sh As Worksheet

For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc

For Each sh In ThisWorkbook.Worksheets
For Each pt In sh.PivotTables
pt.RefreshTable
Next pt
Next sh

End Sub

 

[Paul LaPlant]

Dick,

Thanks for the thought, unfortunately when creating the PivotTable it
loses the ability to refresh it with the current code I am using. I
have actually tried what you suggested before, but it won't work because
even when I right click on the PivotTable and try to refresh it, the
option is grayed out. Does that make sense? Something within the
creation code needs to change in order for me to be able to Refresh the
PivotTable AFTER it is created.

I DO appreciate the thought though.

Paul

 

[Dick Kusleika]

Paul

OK, I get the question now. Have you tried creating the table manually
(through the user interface), and if so, does it allow you to refresh that?

My guess would be that it won't, but if it does let you refresh, you should
create the table manually with the macro recorder on and see if any
properties catch your eye.

If the manual method doesn't work, I'd just bet it has something to do with
permissions on the database side. This is all pure speculation, though.