RE: Create a pivot table via VBA code

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Code:
1) get the data from an ADO recordset into an Excel sheet:

Range.copyfromrecordset adorecordset

2) refresh the pivottable to use that data:

PivotTable.RefreshTable

In my last reply, I said use "Pivotcache.refresh" but of course, this causes
the query to be re-run if you have a dynamic query, wheras it seems you will
have static data in a table (after using the CopyFromrecordset method), thus
no need for that, you can just use RefreshTable ...

HTH

Philip


"MChrist" wrote:

> I have a stored procedure on an MS SQL Server that I can retrieve records
> from that I would like to place in the pivot table I have. So far, using ADO
> code and the copyfromrecordset method, I've been able to paste the records
> into a tab in the workbook and update the pivot table, but a couple people
> suggested I create/update the pivot table directly without pasting the
> records to the spreadsheet.
>
> I've tried looking in help for Pivot Table/Pivot Cache, and I get bits and
> pieces, but things like ADO connections I don't get results even if I have
> the reference attached.
>
> Could someone please point me to an example of how to use the recordset
> directly by a pivot table. I appreciate any help you can provide.
>
> Thank you
>
> Mark
.

Relevant Pages

  • Re: Refreshing Pivots in multiple tabs
    ... If Tools>options>Calculation>is set to Automatic, then there should be no need for any pause, as the calculations will have been performed by the time the next refresh occurs. ... understandably, the main pivot will refresh again. ... >>> problem comes when a user would deletes or even hides a tab or copy ...
    (microsoft.public.excel.misc)
  • Re: Macro to Auto-refresh a pivot table
    ... table to change when my worksheet changes automatically without having ... to hit the refresh button. ... an existing pivot table. ... Lastly, turn the macro recorder off, and look at the code. ...
    (microsoft.public.excel.programming)
  • RE: Code to Refresh inbound data sheets, then Refresh Pivot Tables
    ... It only updates the data sheets w/new & the Pivots w/old data. ... Then refresh all of the end user's custom pivot tables. ... MsgBox "All Data Sheets are updated, click ok to update Pivot tables" ...
    (microsoft.public.excel.programming)
  • Re: Pivot table hanging on to deleted values
    ... Debra has a great addin for Pivot Tables, ... "Roger Govier" wrote: ... then do a Refresh of the PT before you try to ...
    (microsoft.public.excel.misc)
  • RE: refreshing pivot tables with a macro
    ... Sub PTRefresh() ... 'Refresh all pivot tables in this workbook one at a time ... my macro to refresh the pivot tables if possible - currently it does not. ...
    (microsoft.public.excel.misc)