PivotTableWizard help with VB

  • Thread starter circuit_breaker
  • Start date
C

circuit_breaker

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("Names").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 HERE: *****
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("Names").AutoSort
2, "Names"

nRec has a value: (36000), xlDatabase is: 1, ActiveSheet seems okay...
Any idea why?

Thanks.
 
C

circuit_breaker

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("Names").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 HERE: *****
    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("Names").AutoSort
2, "Names"

nRec has a value: (36000), xlDatabase is: 1, ActiveSheet seems okay...
Any idea why?

Thanks.
Click to expand...

Just to add something, the error message is: "PivotTableWizard method
of Worksheet class failed"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating Pivot Table with Macro using Multiple Sheets 0
pivot table error 1004 0
Pivot Table, Variable Inputs 1
End if with block if - Pivot Table Problem 5
Please Help! Need to add a Variable Range in my Excel Macro withPivot Tables 3
Help with Sum formula 2
Pivot Table - Old remaining values for rows 3
Excel 2003 vba hiding totals for some pivot items and not others 3

Top