PivotCache

[MDSKen]

I have an excel workbook (2007) that contains mutliple pivot tables. When the
user clicks a refresh button they are promted to login into a database.
Mutliple sql querys are sent to the database with date selection criteria
entered by the user in the excel sheet. The problem I am having is that when
I want to refresh the pivot caches collection of the workbook I don't know
which pivot cache is associated with which pivot table. Each worksheet has
one pivot table. How can I find out which pivot cache relates to which pivot
table. Below is a snippet of the vba code.

Private Sub RefreshData()
Dim objPivotCache As pivotcache
Dim objCommand As ADODB.Command
Dim rst As ADODB.Recordset
Dim strCriteria As String
Dim i As Integer


Set objCommand = New ADODB.Command

If Login Then
MDSBusiness.Persist.Connection.CommandTimeout = 600
objCommand.ActiveConnection = MDSBusiness.Persist.Connection
WriteResults
With ActiveWorkbook
For i = 1 To .PivotCaches.Count
.PivotCaches(i).Connection = "OLEDB;" &
MDSBusiness.Persist.ConnectionString
Select Case i
Case Sheet3.PivotTables(1).CacheIndex 'LastName
.PivotCaches(i).CommandText =
SelectApplicantCriteria & " Where Loan.DateDeleted is NULL and Surname = ''
AND " & GetDateCriteria & " AND " &
MDSBusiness.User.VirtualField("#AccessibleLoans", "Loan.") & " Order By
Broker"
Case Sheet11.PivotTables(1).CacheIndex 'FirstName
.PivotCaches(i).CommandText =
SelectApplicantCriteria & " Where Loan.DateDeleted is NULL and FirstName =
'' AND " & GetDateCriteria & " AND " &
MDSBusiness.User.VirtualField("#AccessibleLoans", "Loan.") & " Order By
Broker"
Case Sheet14.PivotTables(1).CacheIndex 'DateOfBirth
.PivotCaches(i).CommandText =
SelectApplicantCriteria & " Where Loan.DateDeleted is NULL and DateOfBirth is
NULL AND " & GetDateCriteria & " AND " &
MDSBusiness.User.VirtualField("#AccessibleLoans", "Loan.") & " Order By
Broker"
Case Sheet13.PivotTables(1).CacheIndex 'Gender'
.PivotCaches(i).CommandText =
SelectApplicantCriteria & " Where Loan.DateDeleted is NULL and Sex is NULL
AND " & GetDateCriteria & " AND " &
MDSBusiness.User.VirtualField("#AccessibleLoans", "Loan.") & " Order By
Broker"
Case Sheet5.PivotTables(1).CacheIndex 'PhoneBHPN'
.PivotCaches(i).CommandText =
SelectApplicantCriteria & " Where Loan.DateDeleted is NULL and
isNULL(PhoneBHPN,'') = '' AND " & GetDateCriteria & " AND " &
MDSBusiness.User.VirtualField("#AccessibleLoans", "Loan.") & " Order By
Broker"
Case Sheet6.PivotTables(1).CacheIndex 'PhoneMobilePN'
.PivotCaches(i).CommandText =
SelectApplicantCriteria & " Where Loan.DateDeleted is NULL and
isNULL(MobilePN,'') = '' AND " & GetDateCriteria & " AND " &
MDSBusiness.User.VirtualField("#AccessibleLoans", "Loan.") & " Order By
Broker"
Case Sheet16.PivotTables(1).CacheIndex 'Email'
.PivotCaches(i).CommandText =
SelectApplicantCriteria & " Where Loan.DateDeleted is NULL and
isNULL(Email,'') = '' AND " & GetDateCriteria & " AND " &
MDSBusiness.User.VirtualField("#AccessibleLoans", "Loan.") & " Order By
Broker"
Case Sheet23.PivotTables(1).CacheIndex
.PivotCaches(i).CommandText =
PostalAddressPivotCriteria & " AND Loan.DateDeleted is NULL and " &
GetDateCriteria & " AND " & MDSBusiness.User.VirtualField("#AccessibleLoans",
"Loan.") & " Order By Broker"
Case Sheet24.PivotTables(1).CacheIndex
.PivotCaches(i).CommandText =
PhysicalAddressPivotCriteria & " AND Loan.DateDeleted is NULL and " &
GetDateCriteria & " AND " & MDSBusiness.User.VirtualField("#AccessibleLoans",
"Loan.") & " Order By Broker"
End Select

On Error Resume Next
.PivotCaches(i).MissingItemsLimit = xlMissingItemsNone
On Error GoTo 0

.PivotCaches(i).Refresh

Next
End With
End If
End Sub