PivotTableWizard help with VB
On Jul 7, 8:46*am, circuit_breaker wrote:
Hi,
Using a .vbs script, the following works:
--------------------------
* * Set xl = CreateObject("Excel.Application")
* * xl.Workbooks.Add : xl.Visible = TRUE : xl.Sheets.Add.Name = "acc3"
* * xl.Sheets.Add.Name = "Stats"
* * xl.Sheets("acc3").Select
* * xl.ActiveSheet.PivotTableWizard SourceType = xlDatabase,xl.Range
("B1:B"&nRec),"Stats!R2C1","acc3"
* * xl.ActiveSheet.PivotTables("acc3").PivotFields _
* * ("Names").Orientation = 1
* * xl.ActiveSheet.PivotTables("acc3").PivotFields _
* * ("Names").Orientation = 4
* * xl.ActiveSheet.PivotTables("acc3").PivotFields("Na mes").AutoSort
2,"Names"
--------------------------
Now, I want to place that code in a commandButton on the Excel (2002)
itself. *Everything works up to that point:
* * Set xl = Application * * * *'New code
* * xl.Sheets("acc3").Select
* * xl.Sheets("acc3").Select
* * ' ***** FAILS FROM HE *****
* * xl.ActiveSheet.PivotTableWizard SourceType = xlDatabase, xl.Range
("B1:B" & nRec), "Stats!R2C1", "acc3"
* * xl.ActiveSheet.PivotTables("acc3").PivotFields _
* * ("Names").Orientation = 1
* * xl.ActiveSheet.PivotTables("acc3").PivotFields _
* * ("Names").Orientation = 4
* * xl.ActiveSheet.PivotTables("acc3").PivotFields("Na mes").AutoSort
2, "Names"
nRec has a value: (36000), xlDatabase is: 1, ActiveSheet seems okay...
Any idea why?
Thanks.
Just to add something, the error message is: "PivotTableWizard method
of Worksheet class failed"
|