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