How to Refresh with VBA(SQL) created Pivot

[Pepe]

Hi. I posted about this before and have had a few responses, but none
were able to address what I was hoping to accomplish.

I created a PivotTable using the code below. Now the Pivot is on my
DataSheet, but I cannot refresh it. I want to have the Pivot created
(which I've been able to do), and then be able to refresh the code
when I want to in the future. I also want to be able to create other
PivotTables using the same source. I'm having some problems being
able to refresh the pivot after it's been created. There is no option
to right click and refresh.

Can someone help me with the code to;

1. enable me to refresh the PivotTable and
2. actually refresh the table and
3. do the same for other tables as necessary.

************************************************************************
Code starts here:
************************************************************************

Option Explicit

Sub CreatePTfromADORecordset()

'Declare variables
Dim CNN As New ADODB.Connection
Dim MyRS As New ADODB.Recordset
Dim MyPC As PivotCache
Dim MyPT As PivotTable

Dim StartDate As Date
Dim Statement As String

Dim SetupSheet As Worksheet
Dim DataSheet As Worksheet

Set SetupSheet = ThisWorkbook.Worksheets("Setup")
Set DataSheet = ThisWorkbook.Worksheets("Data")

StartDate = SetupSheet.Range("H1")

'Open Connection
CNN.Open "Driver={SQL
Server};Server=xxxxxxx;DB=xxxxxxx;UID=xxxxxx;PWD=xxxxxx"

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

'Open Recordset
MyRS.Open Statement, CNN, adOpenForwardOnly, adLockReadOnly,
adCmdText

'Create PivotCache
Set MyPC = ThisWorkbook.PivotCaches.Add(xlExternal)
Set MyPC.Recordset = MyRS

'Create PivotTable
Set MyPT = DataSheet.PivotTables.Add(MyPC, Cells(4, 1))
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

'Close the ADO recordset/connection
MyRS.Close
CNN.Close

End Sub

************************************************************************
Code ends here:
************************************************************************

All help and comments are GREATLY appreciated.

Thanks in Advance.

Paul

 

[Charles]

Paul check your post on Feb 6. I inserted some commands to that posting.

Charles

 

[Paul LaPlant]

Charles, I looked at that and I don't think that will solve my problem.
I understand that will recreate the sheet and the pivottable, but I
don't want to recreate it each time, I just want to be able to refresh
it so that other pivottables that use the same source can be refreshed
as well. I don't think this method will address that at all.

Thanks