PivotCache Recordset Property

[Brad]

Good morning.

I have a Productivity report that I've formatted into a
PivotTable. The Operations personnel that are using the
PivotTable however are not familiar with the functionality
and miss the old report which was just a flat dump of
statistics.

What I would like to do is show the detail in the
PivotTable specifically for the user logged into Windows
NT. The problem is - the UserID is a hidden field in the
PivotTable, so I need to access the PivotCache to find the
PivotItem Name corresponding to the matching UserID I
return from the current windows user.

The problem I run into is setting my object variable to
the Recordset property of my PivotCache object. I get an
application or object defined error even in the Err object
description.

My source data is an Excel file, and I hope that's not the
reason the recordset won't work, that'd be disappointing.
Please take a look at the code below.

Also, please advise if you have another solution to my
problem if the above theory is correct. I suppose I could
just add the UserID field to the PivotTable, find it,
offset to get the name, then hide the UserID field again -
but I'd rather access the recordset. That functionality
would be useful in other applications.

Thanks in advance.

Private Declare Function GetUserName Lib "advapi32.dll"
Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

Sub ShowDetailForUserID()
Dim tbl As PivotTable
Dim shtPivot As Worksheet

Set shtPivot = ThisWorkbook.Worksheets("Pivot")
Set tbl = shtPivot.PivotTables("PivotTable1")

Dim cache As PivotCache
Set cache = tbl.PivotCache

Dim rs As Recordset
Set rs = cache.Recordset ' Application or object
defined error.

Dim strRacf As String
strRacf = UserName()

Dim strName As String

Do Until rs.EOF
If rs.Fields("RACF").Value = UCase(strRacf) Then
strName = rs.Fields("Name").Value
Exit Do
Else
rs.MoveNext
End If
Loop

If strName = "" Then
Exit Sub
End If

' We've identified their name, now just expand that
detail.

Dim fldName As PivotField
Set fldName = tbl.PivotFields("Name")

Dim itm As PivotItem
Set itm = fldName.PivotItems(strName)

itm.ShowDetail = True
End Sub
Function UserName() As String
Dim strID As String * 4
Dim ret As Long

ret = GetUserName(strID, 4)
If ret = 0 Then
MsgBox "Function UserName had an API failure."
UserName = ""
Exit Function
End If

UserName = strID
End Function

 

[Dick Kusleika]

Brad

Here's my understanding of how this works: Your pivot table can be based on
Excel data or external data. If it's based on external data, properties of
PivotCache like Connection, CommandText and Recordset are usable. But they
do not exist when the pivot table is based on (internal) Excel data.
However, the SourceData property is available for internal data, and you may
be able to use it.

Sub test()

Dim pt As PivotTable
Dim pc As PivotCache
Dim strName As String
Dim ShSep As Long
Dim BookSep As Long
Dim ShName As String
Dim RngAdd As String
Dim A1Source As String

Set pt = Sheet1.PivotTables(1)
Set pc = pt.PivotCache

'Source data is in form Sheet1!R1C1:R5C2, so you need to convert to
'A1 style reference
A1Source = Application.ConvertFormula(pc.SourceData, xlR1C1, xlA1)

'After convert, A1Source is in the form [Book1.xls]Sheet1!R1C1:R5C2

'Find the !, i.e. where the sheet name stops
ShSep = InStr(1, A1Source, "!", vbTextCompare)

'Find ], i.e. where the book name stops
BookSep = InStr(1, A1Source, "]", vbTextCompare)

'Isolate the sheet name and range address
ShName = Mid(A1Source, BookSep + 1, ShSep - (BookSep + 1))
RngAdd = Right(A1Source, Len(A1Source) - ShSep)

Dim cell As Range

'Loop through the cells in the first column of the SourceData
For Each cell In Sheets(ShName).Range(RngAdd).Columns(1).Cells
If cell.Value = username() Then
strName = cell.Value
Exit For
End If
Next cell

Debug.Print strName

End Sub