Since a pivot table may referrence other sheets to get rid of all the sheets
is not easy. Usually when you are deleting sheets you start from the last
sheet and move towards the 1st sheet. Put the pivot tables may be on other
sheets. A twisted night-mare. See code below. I kept on going back to last
worksheet and moving towards the first sheet every time I deleted a worksheet.

Sub AllWorksheetPivots()

Dim Pt As PivotTable
Dim Ws As Worksheet
Dim strSheet As String

Application.DisplayAlerts = False
sheetCount = worksheets.count

Do while sheetCount 0
Set ws = sheets(SheetCount)
DeleteSht = False
For Each Pt In Ws.PivotTables
DeleteSht = True
With Pt
.RefreshTable
strSheet = Mid(.SourceData, 1, InStr(1, .SourceData, "!") - 1)
Worksheets(strSheet).Delete
End With
Next Pt
if DeleteSht = true then
sheetCount = worksheets.count
else
sheetCount = sheetCount - 1
end if
loop
Application.DisplayAlerts = True

End Sub



"MEG" wrote:

Joel:

Thank you for the reply.

The date portion is working correctly. However, your point about deleting
the pivot table is correct. I only one one connection and pivot table.

I want the user to open the spreadsheet, enter some dates, run the stored
procedure, and the create the pivot table.

Sub AllWorksheetPivots()

Dim Pt As PivotTable
Dim Ws As Worksheet
Dim strSheet As String

Application.DisplayAlerts = False
For Each Ws In ActiveWorkbook.Worksheets
For Each Pt In Ws.PivotTables

With Pt
.RefreshTable
strSheet = Mid(.SourceData, 1, InStr(1, .SourceData, "!") - 1)
Worksheets(strSheet).Delete
End With

Exit Sub
Next Pt
Next Ws
Application.DisplayAlerts = True

End Sub

Any other thoughts?
The user wouldn't save the file at the end of their analysis.

I tried the following code to delete pivot tables, but it didn't work.



"Joel" wrote:

I'm not sure if you are using British or USA data standards. You have 3
dates in your code. The sCommand is the entire range of dates you are taking
from the database. tTe Pivot table is filtering these dates. The pivot
table date must be inside the range of dates you are getting from the
database. Make sure your dates are properly setup. The code doesn't seem
to be deleting the old Pivot table. It looks like you are adding a new table
each time. Is that what you want?

sCommand = sCommand + sComma + sQuote + "2009-01-01" + sQuote
sCommand = sCommand + sComma + sQuote + "2009-01-09" + sQuote

ActiveSheet.PivotTables("PivotTable6").PivotFields ("DATE_ADDED").PivotItems( _
"2009-07-01").ShowDetail = True


"MEG" wrote:

As background, I have a SQL Server Stored Procedure (with date parameters)
that consolidates data from several tables. The end results is that the
stored procedure returns rows (using a select statement).

The code below creates the SQL statement and then it tries to execute the
SQL command, then create a Pivot Table from the results.

This is NOT refreshing the data. When I change the dates, it always returns
the same data (like it doesn't know I want to refresh the Pivot Table).

I'd certainly appreciate any assistance with this.




Sub PT_Patient_Reg()
Dim sWorkBookName As String
sWorkBookName = ActiveWorkbook.Name

Dim sCommand As String
Dim sQuote As String
Dim sQuotes As String
Dim sSpace As String
Dim sComma As String


sQuote = Chr(39)
sQuotes = Chr(34)
sSpace = Chr(20)
sComma = Chr(44)


' Delete Connection

On Error Resume Next ' Defer error trapping.
ActiveWorkbook.Connections("ARSYSTEM SITE_PATIENT_REGISTRATION").Delete

' Create SQL statement that Executes Stored procedures

sCommand = "SELECT * FROM ARSYSTEM.dbo.SITE_PATIENT_REGISTRATION"
sCommand = "EXECUTE [dbo].[SITE_SP_PATIENT_MATRIX] "
sCommand = sCommand + sQuote + "MAIN" + sQuote
sCommand = sCommand + sComma + sQuote + "2009-01-01" + sQuote
sCommand = sCommand + sComma + sQuote + "2009-01-09" + sQuote

'
Workbooks(sWorkBookName).Connections.Add "ARSYSTEM
SITE_PATIENT_REGISTRATION", "", _
Array(Array( _
"ODBC;DSN=Dim;Description=Dim;UID=usr;APP=2007 Microsoft Office
system;WSID=D820_XP_09;DATABASE=Medical;AutoTransl ate=No;Trusted_Conn=YES;" _
), Array("ection=Yes;QuotedId=No;AnsiNPW=No")), Array( _
sCommand), 2
ActiveWorkbook.PivotCaches.Create(SourceType:=xlEx ternal, SourceData:= _
ActiveWorkbook.Connections("ARSYSTEM SITE_PATIENT_REGISTRATION"),
Version:= _
xlPivotTableVersion12).CreatePivotTable
TableDestination:="Sheet1!R1C1", _
TableName:="PivotTable6", DefaultVersion:=xlPivotTableVersion12
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable6").PivotFields ("DATE_ADDED")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable6").PivotFields ("ACCOUNT")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable6").PivotFields ("ACTION_TYPE")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable6").PivotFields ("USERID")
.Orientation = xlColumnField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable6").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable6").PivotFields("MASTER_FILE"), "Sum of Patient
Registration", xlSum
Range("A4").Select

ActiveSheet.PivotTables("PivotTable6").PivotFields ("DATE_ADDED").ShowDetail =
_
False

ActiveSheet.PivotTables("PivotTable6").PivotFields ("DATE_ADDED").PivotItems( _
"2009-07-01").ShowDetail = True
Range("B2").Select

ActiveSheet.PivotTables("PivotTable6").PivotFields ("ACTION_TYPE").PivotItems(
_
"1 NEW").ShowDetail = False
Range("C2").Select

ActiveSheet.PivotTables("PivotTable6").PivotFields ("ACTION_TYPE").PivotItems(
_
"2 EDIT").ShowDetail = False
Range("D2").Select

ActiveSheet.PivotTables("PivotTable6").PivotFields ("ACTION_TYPE").ShowDetail
= _
False
Range("A4").Select

ActiveSheet.PivotTables("PivotTable6").PivotFields ("DATE_ADDED").ShowDetail =
_
False
End Sub