VBA SQL Server Stored Procedure creating a Pivot Table

M

MEG

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;AutoTranslate=No;Trusted_Conn=YES;" _
), Array("ection=Yes;QuotedId=No;AnsiNPW=No")), Array( _
sCommand), 2
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, 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").AddDataField
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
 
J

Joel

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 said:
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;AutoTranslate=No;Trusted_Conn=YES;" _
), Array("ection=Yes;QuotedId=No;AnsiNPW=No")), Array( _
sCommand), 2
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, 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").AddDataField
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
Click to expand...
 
J

Joel

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 said:
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 said:
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 said:
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;AutoTranslate=No;Trusted_Conn=YES;" _
), Array("ection=Yes;QuotedId=No;AnsiNPW=No")), Array( _
sCommand), 2
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, 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").AddDataField
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
Click to expand...
Click to expand...
Click to expand...
 
M

MEG

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 said:
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 said:
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 said:
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


:

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;AutoTranslate=No;Trusted_Conn=YES;" _
), Array("ection=Yes;QuotedId=No;AnsiNPW=No")), Array( _
sCommand), 2
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, 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").AddDataField
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
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

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 said:
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 said:
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 said:
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.



:

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


:

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;AutoTranslate=No;Trusted_Conn=YES;" _
), Array("ection=Yes;QuotedId=No;AnsiNPW=No")), Array( _
sCommand), 2
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, 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").AddDataField
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
Click to expand...
Click to expand...
Click to expand...
 
M

MEG

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 said:
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 said:
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 said:
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



:

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.



:

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


:

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;AutoTranslate=No;Trusted_Conn=YES;" _
), Array("ection=Yes;QuotedId=No;AnsiNPW=No")), Array( _
sCommand), 2
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, 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").AddDataField
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
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

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 said:
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 said:
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 said:
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.


:

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



:

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.



:

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


:

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;AutoTranslate=No;Trusted_Conn=YES;" _
), Array("ection=Yes;QuotedId=No;AnsiNPW=No")), Array( _
sCommand), 2
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, 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").AddDataField
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
Click to expand...
Click to expand...
Click to expand...
 
M

MEG

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 said:
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 said:
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 said:
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



:

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.


:

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



:

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.



:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Pivot Table Error 1
syntax in VBA to create a pivot table 2
End if with block if - Pivot Table Problem 5
Pivot table sum data 1
Pivot Table type mismatch 1
Creating Excel file from VB 4
Creating reports per Pivot Table PivotItem 1
Recording a Macro to create a Pivot Table 1

Top