Refresh Pivot after Creating (VBA and SQL)

[Pepe]

I'm still pretty new to a lot of this, but 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

 

[Charles]

I'm still pretty new to a lot of this, but 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
''''''''''''''''''''If you are tryng to refresh the same sheet maybe you should try deleting it first then adding it back with the new information ''''''''''''''''''''''''''

'Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("SkuReport").Delete''''This should be changed to your sheet name''
On Error GoTo 0
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

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

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Add new worksheet
Worksheets.Add
ActiveSheet.Name = "SkuReport"'''Change this to the name of your worksheet
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'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 added some code to yours. HTH with part of your question.


Charles