Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.scripting.vbscript,microsoft.public.excel.programming
|
|||
|
|||
How can you create a Pivot Table using vbscript
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. |
#2
Posted to microsoft.public.scripting.vbscript,microsoft.public.excel.programming
|
|||
|
|||
How can you create a Pivot Table using vbscript
"jtvbs" wrote in message
... 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(PivotTo pLeft, PivotTopRight) Set MyPivotRange = Exported.Parent.Worksheets("Sheet2").Range(PivotTo p, 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(SourceT ype:=xlDatabase, SourceData:=MyPivotRangeName) MyPivotCache.CreatePivotTable TableDestination:=Exported.Parent.Worksheets("Shee t3").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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can you create a Pivot Table using vbscript
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 -- hc ----------------------------------------------------------------------- hce's Profile: http://www.excelforum.com/member.php...nfo&userid=351 View this thread: http://www.excelforum.com/showthread.php?threadid=26224 |
#4
Posted to microsoft.public.scripting.vbscript,microsoft.public.excel.programming
|
|||
|
|||
How can you create a Pivot Table using vbscript
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 "jtvbs" wrote in message ... 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. |
#5
Posted to microsoft.public.scripting.vbscript,microsoft.public.excel.programming
|
|||
|
|||
How can you create a Pivot Table using vbscript
"Stephen Wortley" wrote in message
om... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
pivot table: create worksheetsheet using pivot table | Excel Discussion (Misc queries) | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
VBScript to Change pivot table layout | Excel Programming | |||
Using VBScript to create Excel SS and sort by column. | Excel Programming |