Excel vba pivot table programming

[Guest]

I have an excel workbook with 2 sheets and a vba module. I use ADO to connect
to a sql server 2000 to collect some data. Download of data to excel is
controlled from a combobox and some buttons on sheet1. The user selects a
customer (in the combobox) and downloads from 400 to 5000 records from sql
server. The records are visible on sheet1, so the operator can browse the
data.

Om sheet2 there is a pivot table that shows data from sheet1. Unfortunately
the pivot table is not correctly updated when the operator deletes old data
and downloads some new on sheet1. This seem to happen if the pivot table was
established with 500 records on sheet1. In the next case there may be 5000
records, and only a fraction of these are shown in the pivot table.

"Refresh data" on the Excel Data menu do not fix the problem. It seems I
need to reset the data source for the pivot table, and this have to be done
without changing the structure (column-, row-, and page-fields) on the pivot
table. How can this be done in VBA-code?

Regards

Tore G.

 

[Guest]

This may help:

Sub PivotRange()

Dim rowCount As Long

Sheets("Data").Select
Range("A1").Select
rowCount = Selection.CurrentRegion.Rows.Count

Sheets("Pivot").Select
Range("A3").Select 'must be cell in pivottable
ActiveSheet.PivotTableWizard SourceType:=xlDatabase _
, SourceData:="Data!R1C1:R" & rowCount & "C3"

End Sub

The routine first goes to the data sheet and counts the number of rows. It
then selects a cell in the pivot table and makes use of the Pivot Table
Wizard to change the data range. Note my data is only 3 columns wide so you
will have to adapt the last line - & "C3" - to your situation.

Regards
Rowan

 

[OJ]

Hi,
How about using a dynamic range name for the source of the pivot
table...

something like..

=offset(Sheet1!$A$1,0,0,counta($A:$A),Counta($1:$1))
Hth,
Oli