Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|