Refresh a PivotTable using VBA (SQL generated Pivot)

  • Thread starter Thread starter Pepe
  • Start date Start date
P

Pepe

Hello all. I managed to find enough information to write some code
that creates a new PivotTable for me. I'd like to have the PivotTable
created and then be able to Refresh this by executing a macro at any
point desired in the future. I'm also planning on having additional
PivotTables that link to this same cache and have them all refreshed
in turn. Below is the code for
creating the PivotTable (Feel free to recommend changes as well).

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

I don't know if the way you have generated the pivottable
will make any difference to this, but ordinarily
Activeworkbook.refreshall
will update all the links and pivot tables in the workbook.

Alternatively,
MyPT.Refresh
should work for any given table.

Pete.
-----Original Message-----
Hello all. I managed to find enough information to write some code
that creates a new PivotTable for me. I'd like to have the PivotTable
created and then be able to Refresh this by executing a macro at any
point desired in the future. I'm also planning on having additional
PivotTables that link to this same cache and have them all refreshed
in turn. Below is the code for
creating the PivotTable (Feel free to recommend changes as well).

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
Click to expand...
("Insertby", "Resolution1", "Resolution2",
 
Pete, when the PivotTable is created there is no option to Refresh it
when I right click on it. The items you suggested make sense, but they
don't work the way this is set up. I was curious if I have to have this
re-connect first to get the data and then refresh the Pivot with the new
data. I guess for now I'm still stuck on this one.
 
Back
Top