How can you create a Pivot Table using vbscript

[Guest]

I've tried recording a macro but that doesn't seem to translate into
vbscript. I think it has something to do with the wizard. Just wondering if
anyone has any experience with creating a Pivot Table from within a vbscript.

 

[Alan]

I've tried recording a macro but that doesn't seem to translate into
vbscript. I think it has something to do with the wizard. Just
wondering if anyone has any experience with creating a Pivot Table
from within a vbscript.

Hi,

I struggled to do this, but found a way (see below).

Note that this may not work properly for you, and there is probably a
better way, but it may give you a starting point.

This is quite a dangerous area, since it guess it is possible for the
table to 'look' okay, but actually return incorrect results so I
suggest you test thoroughly before using it.

Any comments from others as to improvements are most welcome!

Alan.

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

NB: Exported is a worksheet already at this point



' Set the range to be pivoted to be called PivotRange

Set PivotTopLeft =
Exported.Parent.Worksheets("Sheet2").Range("A2")
Set PivotTopRight = PivotTopLeft.End(xlToRight)
Set PivotTop =
Exported.Parent.Worksheets("Sheet2").Range(PivotTopLeft,
PivotTopRight)
Set MyPivotRange =
Exported.Parent.Worksheets("Sheet2").Range(PivotTop,
PivotTop.End(xlDown))


' Create the pivot table

Application.CutCopyMode = False

Exported.Parent.Worksheets.Add

MyPivotRangeName = MyPivotRange.Parent.Name & "!" &
MyPivotRange.Address(ReferenceStyle:=xlR1C1)


Set MyPivotCache =
MyPivotRange.Parent.Parent.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=MyPivotRangeName)


MyPivotCache.CreatePivotTable
TableDestination:=Exported.Parent.Worksheets("Sheet3").Range("A1"), _
TableName:="PivotTable1"


Exported.Parent.Worksheets("Sheet3").PivotTables("PivotTable1").PivotT
ableWizard


Exported.Parent.Worksheets("Sheet3").PivotTables("PivotTable1").SmallG
rid = False


Exported.Parent.Worksheets("Sheet3").PivotTables("PivotTable1").AddFie
lds _
RowFields:="Type", _
ColumnFields:="Site Owner", _
PageFields:="Location"

With
Exported.Parent.Worksheets("Sheet3").PivotTables("PivotTable1"). _
PivotFields("Net Book Value" & Chr(10) & "($)")

.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"

End With

 

[hce]

hi

i've tried recording a macro to generate a pivot table n it worked fin
except you should note that it will have problems if you have u
selected a field when u r recording the macro... because the next time
run the macro with a new set of data n this field is no longe
available, an error message would then pop up because excel cannot fin
that field to un select... hope this helps... in addition, the macr
might not work in different version especially if u r using 2003..
some macros recorded in 2003 will not work in older versions.... hop
this helps..

cheer

 

[Stephen Wortley]

As a slight digression on this query, do people find that they stick
to using Pivot Tables whilst coding in VBA, or is using
Array(s)/Collection(s) also considered/acceptable?

I have always had difficulty working Pivot Tables, so when coding I
tend to use multidimensional arrays.

Are Arrays quicker - processing-wise - than Pivot Tables? Is there a
good reason why I should not use Arrays?

TIA

Stephen Wortley

 

[Alan]

As a slight digression on this query, do people find that they stick
to using Pivot Tables whilst coding in VBA, or is using
Array(s)/Collection(s) also considered/acceptable?

I have always had difficulty working Pivot Tables, so when coding I
tend to use multidimensional arrays.

Are Arrays quicker - processing-wise - than Pivot Tables? Is there a
good reason why I should not use Arrays?

TIA

Stephen Wortley

I can only answer specifically for the one occasion I had to do this,
but in that situation the end users wanted the ability to drill down,
in the pivot table in the output worksheet, to see the underlying data
for a given result.

They wanted a pivot table so that is what they got. I guess that
using arrays / collections would not result in an output that normal
users could 'play' with. Whether that is a good or bad thing probably
depends on the situation!

HTH,

Alan.