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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SQL Server Stored Procedure creating a Pivot Table
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SQL Server Stored Procedure creating a Pivot Table
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SQL Server Stored Procedure creating a Pivot Table
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SQL Server Stored Procedure creating a Pivot Table
Joel:
Thanks for the quick response. I tried the code. When it drops into theFor Each PT in Ws.PIVOTTABLES loop and hits the .RefreshTable statement, the code jumps back to the main procedure. It never executes the .DELETE command. This seems strange to me. It jumps out of the subroutine and back to the line after the subroutine call. "Joel" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SQL Server Stored Procedure creating a Pivot Table
The sourcedata filed of a pivot table is R1C1 format. Couldn't figure an
easy way of using the R1C1 cell reference in VBA. I don't think your want to delete the worksheet. You really wanted to clear the pivot table and the source range. Try this. 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 ShtName = Left(.SourceData, InStr(.SourceData, "!") - 1) 'remove sheet and first r RangeName = Mid(.SourceData, InStr(.SourceData, "!") + 2) StartRow = Val(RangeName) 'remove past 1st C RangeName = Mid(RangeName, InStr(RangeName, "C") + 1) StartCol = Val(RangeName) 'remove past 2nd R RangeName = Mid(RangeName, InStr(RangeName, "R") + 1) LastRow = Val(RangeName) 'remove past 2nd C RangeName = Mid(RangeName, InStr(RangeName, "C") + 1) LastCol = Val(RangeName) With Worksheets(ShtName) Set SourceRange = .Range(.Cells(StartRow, StartCol), _ .Cells(LastRow, LastCol)) End With SourceRange.Clear .Clear End With Next Pt sheetCount = sheetCount - 1 Loop Application.DisplayAlerts = True End Sub "MEG" wrote: Joel: Thanks for the quick response. I tried the code. When it drops into theFor Each PT in Ws.PIVOTTABLES loop and hits the .RefreshTable statement, the code jumps back to the main procedure. It never executes the .DELETE command. This seems strange to me. It jumps out of the subroutine and back to the line after the subroutine call. "Joel" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SQL Server Stored Procedure creating a Pivot Table
Joel:
Thanks again for the code: On the following line: shtName = Left(.SourceData, InStr(.SourceData, "!") - 1) I get a TYPE MISMATCH Is there something that I need to define or add-in (like a reference) to clear-up this error? I'm sure that you tested the code and it worked for you. Thanks, MEG "Joel" wrote: The sourcedata filed of a pivot table is R1C1 format. Couldn't figure an easy way of using the R1C1 cell reference in VBA. I don't think your want to delete the worksheet. You really wanted to clear the pivot table and the source range. Try this. 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 ShtName = Left(.SourceData, InStr(.SourceData, "!") - 1) 'remove sheet and first r RangeName = Mid(.SourceData, InStr(.SourceData, "!") + 2) StartRow = Val(RangeName) 'remove past 1st C RangeName = Mid(RangeName, InStr(RangeName, "C") + 1) StartCol = Val(RangeName) 'remove past 2nd R RangeName = Mid(RangeName, InStr(RangeName, "R") + 1) LastRow = Val(RangeName) 'remove past 2nd C RangeName = Mid(RangeName, InStr(RangeName, "C") + 1) LastCol = Val(RangeName) With Worksheets(ShtName) Set SourceRange = .Range(.Cells(StartRow, StartCol), _ .Cells(LastRow, LastCol)) End With SourceRange.Clear .Clear End With Next Pt sheetCount = sheetCount - 1 Loop Application.DisplayAlerts = True End Sub "MEG" wrote: Joel: Thanks for the quick response. I tried the code. When it drops into theFor Each PT in Ws.PIVOTTABLES loop and hits the .RefreshTable statement, the code jumps back to the main procedure. It never executes the .DELETE command. This seems strange to me. It jumps out of the subroutine and back to the line after the subroutine call. "Joel" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SQL Server Stored Procedure creating a Pivot Table
I tested the code with the source data being on a different worksheet than
the Pivot Table. For you to be getting the error the source data doesn't have a ! (ie Sheet1!R1C1:R5C7) which means the surce data is on the same worksheet as the pivot table. I slightly modified the code to handle both situations. 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 If InStr(.SourceData, "!") 0 Then ShtName = Left(.SourceData, InStr(.SourceData, "!") - 1) 'remove sheet and first r RangeName = Mid(.SourceData, InStr(.SourceData, "!") + 2) Else 'move past 1st R ShtName = Ws.Name RangeName = Mid(.SourceData, 2) End If StartRow = Val(RangeName) 'remove past 1st C RangeName = Mid(RangeName, InStr(RangeName, "C") + 1) StartCol = Val(RangeName) 'remove past 2nd R RangeName = Mid(RangeName, InStr(RangeName, "R") + 1) LastRow = Val(RangeName) 'remove past 2nd C RangeName = Mid(RangeName, InStr(RangeName, "C") + 1) LastCol = Val(RangeName) With Worksheets(ShtName) Set SourceRange = .Range(.Cells(StartRow, StartCol), _ .Cells(LastRow, LastCol)) End With SourceRange.Clear .Clear End With Next Pt sheetCount = sheetCount - 1 Loop Application.DisplayAlerts = True End Sub "MEG" wrote: Joel: Thanks again for the code: On the following line: shtName = Left(.SourceData, InStr(.SourceData, "!") - 1) I get a TYPE MISMATCH Is there something that I need to define or add-in (like a reference) to clear-up this error? I'm sure that you tested the code and it worked for you. Thanks, MEG "Joel" wrote: The sourcedata filed of a pivot table is R1C1 format. Couldn't figure an easy way of using the R1C1 cell reference in VBA. I don't think your want to delete the worksheet. You really wanted to clear the pivot table and the source range. Try this. 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 ShtName = Left(.SourceData, InStr(.SourceData, "!") - 1) 'remove sheet and first r RangeName = Mid(.SourceData, InStr(.SourceData, "!") + 2) StartRow = Val(RangeName) 'remove past 1st C RangeName = Mid(RangeName, InStr(RangeName, "C") + 1) StartCol = Val(RangeName) 'remove past 2nd R RangeName = Mid(RangeName, InStr(RangeName, "R") + 1) LastRow = Val(RangeName) 'remove past 2nd C RangeName = Mid(RangeName, InStr(RangeName, "C") + 1) LastCol = Val(RangeName) With Worksheets(ShtName) Set SourceRange = .Range(.Cells(StartRow, StartCol), _ .Cells(LastRow, LastCol)) End With SourceRange.Clear .Clear End With Next Pt sheetCount = sheetCount - 1 Loop Application.DisplayAlerts = True End Sub "MEG" wrote: Joel: Thanks for the quick response. I tried the code. When it drops into theFor Each PT in Ws.PIVOTTABLES loop and hits the .RefreshTable statement, the code jumps back to the main procedure. It never executes the .DELETE command. This seems strange to me. It jumps out of the subroutine and back to the line after the subroutine call. "Joel" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA SQL Server Stored Procedure creating a Pivot Table
Joel:
I certainly appreciate your assistance and hate to keep having issues. I'm still getting a type mismatch. It is occuring on statement: If InStr(.SourceData, "!") 0 Then I've tried to find a solution to this but everyone of them was concerning setting the .SOURCEDATA. I tried to qualify it with pt.sourcedata, but that doesn't work. Could it be some reference or declaration that is missing? Again, I appreciate your time and efforts. MEG "Joel" wrote: I tested the code with the source data being on a different worksheet than the Pivot Table. For you to be getting the error the source data doesn't have a ! (ie Sheet1!R1C1:R5C7) which means the surce data is on the same worksheet as the pivot table. I slightly modified the code to handle both situations. 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 If InStr(.SourceData, "!") 0 Then ShtName = Left(.SourceData, InStr(.SourceData, "!") - 1) 'remove sheet and first r RangeName = Mid(.SourceData, InStr(.SourceData, "!") + 2) Else 'move past 1st R ShtName = Ws.Name RangeName = Mid(.SourceData, 2) End If StartRow = Val(RangeName) 'remove past 1st C RangeName = Mid(RangeName, InStr(RangeName, "C") + 1) StartCol = Val(RangeName) 'remove past 2nd R RangeName = Mid(RangeName, InStr(RangeName, "R") + 1) LastRow = Val(RangeName) 'remove past 2nd C RangeName = Mid(RangeName, InStr(RangeName, "C") + 1) LastCol = Val(RangeName) With Worksheets(ShtName) Set SourceRange = .Range(.Cells(StartRow, StartCol), _ .Cells(LastRow, LastCol)) End With SourceRange.Clear .Clear End With Next Pt sheetCount = sheetCount - 1 Loop Application.DisplayAlerts = True End Sub "MEG" wrote: Joel: Thanks again for the code: On the following line: shtName = Left(.SourceData, InStr(.SourceData, "!") - 1) I get a TYPE MISMATCH Is there something that I need to define or add-in (like a reference) to clear-up this error? I'm sure that you tested the code and it worked for you. Thanks, MEG "Joel" wrote: The sourcedata filed of a pivot table is R1C1 format. Couldn't figure an easy way of using the R1C1 cell reference in VBA. I don't think your want to delete the worksheet. You really wanted to clear the pivot table and the source range. Try this. 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 ShtName = Left(.SourceData, InStr(.SourceData, "!") - 1) 'remove sheet and first r RangeName = Mid(.SourceData, InStr(.SourceData, "!") + 2) StartRow = Val(RangeName) 'remove past 1st C RangeName = Mid(RangeName, InStr(RangeName, "C") + 1) StartCol = Val(RangeName) 'remove past 2nd R RangeName = Mid(RangeName, InStr(RangeName, "R") + 1) LastRow = Val(RangeName) 'remove past 2nd C RangeName = Mid(RangeName, InStr(RangeName, "C") + 1) LastCol = Val(RangeName) With Worksheets(ShtName) Set SourceRange = .Range(.Cells(StartRow, StartCol), _ .Cells(LastRow, LastCol)) End With SourceRange.Clear .Clear End With Next Pt sheetCount = sheetCount - 1 Loop Application.DisplayAlerts = True End Sub "MEG" wrote: Joel: Thanks for the quick response. I tried the code. When it drops into theFor Each PT in Ws.PIVOTTABLES loop and hits the .RefreshTable statement, the code jumps back to the main procedure. It never executes the .DELETE command. This seems strange to me. It jumps out of the subroutine and back to the line after the subroutine call. "Joel" wrote: 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 |
Reply |
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 |