RE: Create a pivot table via VBA code

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

Philip,

Thank you for your response. I was looking for a way to avoid pasting the
records to a spreadsheet by manipulating the recordset directly. While I can
create a pivot table (quite a coding pain!), I wasn't able to figure out how
to remove the row or column subtotals, or to apply the recordset to the
existing table.

Basically, I had to go back to what I had. If anyone can point me to an
example that doesn't require rebuilding the entire pivot table, I would
greatly appreciate it.

Thanks again though.

Mark

"Philip" wrote:

> 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: using DSUM in formulae instead of SUMIF
    ... I have a spreadsheet that I enter the pertinent customer contact data into ... Is it possible to somehow sort as the above example illustrates, ... Will the SUMIF function accept IF types of arguments ... > If the pivot table source data will change size frequently, ...
    (microsoft.public.excel.worksheet.functions)
  • Dynamically generating a spreadsheet with custom pivot table including data
    ... I'm building an ASP.NET application that needs to generate an Excel ... spreadsheet containing a pivot table. ... Build an XML spreadsheet containing the data, ...
    (microsoft.public.excel.programming)
  • Re: split sheet into tabs in EXCEL
    ... I'm just not fond of pivot tables. ... have the complete data of the category in a new worksheet. ... Tab and page are sometimes used when referring to sheets but IF you want ... > is called a "spreadsheet", and each individual page is a "tab". ...
    (microsoft.public.excel.misc)
  • Forms/New/Pivot Table Wizard question
    ... This creates an unbound form with an embedded OLE object containing a Pivot table in Excel. ... Each time I open the Excel spreadsheet or dbl-click the Spreadsheet, I need to "refresh" the data. ...
    (comp.databases.ms-access)
  • Re: Multiple variables-SOS
    ... Step1 screen: Excel Data, Pivot table ... tell Excel where you want the pivot table ... Spreadsheet B contains date, department name, hours worked, and dollars by ... I want to summarize by day in my report: ...
    (microsoft.public.excel.worksheet.functions)