Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SQL Server Stored Procedure creating a Pivot Table
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
datasource from stored procedure to pivot table | Excel Discussion (Misc queries) | |||
Joining SQL Server Stored Procedure to SQL Server Table | Excel Programming | |||
SQL Server Stored Procedure | Excel Programming | |||
SQL stored procedure using values from cells into Pivot Table | Excel Programming | |||
Importing Data from a Stored Procedure (SQL SErver) | Excel Programming |