Create a pivot table via VBA code
 

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

Create a pivot table via VBA code
 

Hi,

One way to get started is to try recording it to see the code you get...

Alternatively, if you don't like the code you get (and I definitely did NOT
!), then use the VBA 'CopyFromRecordset' method - passing your ADO Recordset
as the argument. This will dump the data from your ADO recordset on the
worksheet, where you can use it as the source for your PivotTable...

This method is very fast, and will output the entire recordset to the
worksheet starrting at the range you specify (range.CopyFromRecordset
adorecordset !)

Once you have the data in one sheet, you can call the refresh method of the
PivotTableCache object...as well as manipulate fields, columns, hide stuff,
colour it in, do whatever you want...

We built an entire application in VB to create complex reports in Excel (we
didn't like the formatting options that Crystal Reports 7 gave us for MS
Excel Output !), format, refresh Pivot Tables using the raw data as the data
source...

good luck, let us know how you get on...

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

Create a pivot table via VBA code
 

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

Create a pivot table via VBA code
 

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

Create a pivot table via VBA code
 

Pivot Table

ADO

Recordset



From the help example from xl2003:



This example creates a new PivotTable cache using an ADO connection to
Microsoft Jet, and then it creates a new PivotTable report based on the
cache, at cell A3 on the active worksheet.

Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command

' Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open "C:\perfdate\record.mdb"
End With

' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Select Speed, Pressure, Time From DynoRun"
.CommandType = adCmdText
.Execute
End With

' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand

' Create a PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache
.CreatePivotTable TableDestination:=Range("A3"), _
TableName:="Performance"
End With

With ActiveSheet.PivotTables("Performance")
.SmallGrid = False
With .PivotFields("Pressure")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Speed")
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("Time")
.Orientation = xlDataField
.Position = 1
End With
End With

' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
Set cnnConn = Nothing
-- Regards,Tom Ogilvy"MChrist" wrote in
message ...
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