PivotTableWizard help with VB
 

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.

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"