Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivotcache recordcount (Excel 2003)

    Hi everybody:

    I seem to be stuck on when / how to get the actual number of records returned in my pivotcache. I'm developing an Excel report generator that allows users to input various parameters for the SQL string that gets data from an Access database. So, I need to be able to test whether their parameters generate data before creating the pivot table. The code below gives me a recordcount of 0 no matter how many records are actually returned. If I try getting the record count after creating the pivtot table, the pivotcache object seems to go out of scope. Please tell me how to get this to work!

    Thank you, in advance, for your help.

    Set PC = ThisWorkbook.PivotCaches.Add(xlExternal)

    SQL_str = "SELECT qODBC_std.* FROM qODBC_std" & strWHERE & ";"
    With PC
    .Connection = "ODBC;DSN=MS Access Database;" & _
    "DBQ=H:KJRBalances_be.mdb; DefaultDir=H:KJR ;DriverID=25;" _
    & "FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
    .CommandType = xlCmdSql
    .CommandText = SQL_str
    lngRecs = .RecordCount 'always returns0!
    if lngRecs = 0 then
    msgbox "Sorry, your selections do not return data. Please try again."
    goto exit_sub
    end if
    End With

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivotcache recordcount (Excel 2003)

    I seem to recall you can test for EOF.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivotcache recordcount (Excel 2003)

    Figured it out. The trick is not to call the recordcount property from the pivot cache directly, but from the pivot table after it is created, thus:

    Set PT = oSheet.CreatePivotTable(.Range("A3"), "pvtReport")
    PT.Refresh
    lngRecs = PT.PivotCache.RecordCount

    Apparently, the recordcount only counts records that are displayed, not that are returned by the sql.

    Everything is easy when you know how!

  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivotcache recordcount (Excel 2003)

    Sorry, I meant

    set PT = PC.CreatePivotTable

    not osheet.CreatePivotTable

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivotcache recordcount (Excel 2003)

    For the future: you can edit your own posts to correct errors or add information, by clicking the Edit Post button <IMG SRC=http://www.wopr.com/w3timages/edit.gif>.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •