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