| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table using VB Script
Hi
I am trying to automate MS office on the client side. I wish to create a pivot table on my local machine using VB Script. I have the data in an excel file stored on my hard drive with the data as shown below (Stored as C:\MyExcelVB.xls). I have a simple HTML page with VB script on it. The VB Script uses a method called PivotTableWizard. The first time I executed the code, it ran successfully. Thereafter every time I executed the code (after recreating the excel file), it showed an error saying "PivotTableWizard method of WorkSheet Class Failed". I also tried to execute the code on my neighbour's PC... but it showed the same. The code is provided below. Any pointers or help in this matter... Please help me. CODE: <HTML <BODY <INPUT id=button1 name=button1 type=button value=Button <SCRIPT LANGUAGE="VBScript" sub button1_onclick() Dim xlObj 'Set xlObj = GetObject(, "Excel.Application") 'Set xlObj = new ActiveXObject("Excel.Application") Set xlObj = createobject("Excel.Application") xlObj.Visible = True xlObj.Workbooks.Open "C:\myExcelVB.xls" With xlObj .Range("A1").Select .ActiveSheet.PivotTableWizard SourceType=xlDatabase,_ SourceData="Sheet1!R1C1:R5C3",TableDestination="", _ TableName="PivotTable1" .ActiveSheet.PivotTables("PivotTable1").AddFields _ RowFields="Office", ColumnFields="Region" .ActiveSheet.PivotTables("PivotTable1"). _ PivotFields("Sales").Orientation = xlDataField End With xlObj.ActiveSheet.UsedRange.Select Documents.Add Set xlObj = Nothing End Sub </SCRIPT </BODY </HTML EXCEL DATA: (C:\MyExcelVB.xls) Region Office Sales North Alpha 100 East Beta 120 West Alpha 130 North Beta 100 East Beta 140 West Alpha 110 Thank you, Regards, Sovon |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table using VB Script
I am not 100% certain, but I believe that the Docments.Add should be
associated with the xlObj... Otherwise, vbs probably doesn't know what to do with it. Again though, I could be mistaken. On Mon, 28 Jul 2003 03:40:25 -0700, "Sovon Chatterjee" wrote: Hi I am trying to automate MS office on the client side. I wish to create a pivot table on my local machine using VB Script. I have the data in an excel file stored on my hard drive with the data as shown below (Stored as C:\MyExcelVB.xls). I have a simple HTML page with VB script on it. The VB Script uses a method called PivotTableWizard. The first time I executed the code, it ran successfully. Thereafter every time I executed the code (after recreating the excel file), it showed an error saying "PivotTableWizard method of WorkSheet Class Failed". I also tried to execute the code on my neighbour's PC... but it showed the same. The code is provided below. Any pointers or help in this matter... Please help me. CODE: <HTML <BODY <INPUT id=button1 name=button1 type=button value=Button <SCRIPT LANGUAGE="VBScript" sub button1_onclick() Dim xlObj 'Set xlObj = GetObject(, "Excel.Application") 'Set xlObj = new ActiveXObject("Excel.Application") Set xlObj = createobject("Excel.Application") xlObj.Visible = True xlObj.Workbooks.Open "C:\myExcelVB.xls" With xlObj .Range("A1").Select .ActiveSheet.PivotTableWizard SourceType=xlDatabase,_ SourceData="Sheet1!R1C1:R5C3",TableDestination="", _ TableName="PivotTable1" .ActiveSheet.PivotTables("PivotTable1").AddFields _ RowFields="Office", ColumnFields="Region" .ActiveSheet.PivotTables("PivotTable1"). _ PivotFields("Sales").Orientation = xlDataField End With xlObj.ActiveSheet.UsedRange.Select Documents.Add Set xlObj = Nothing End Sub </SCRIPT </BODY </HTML EXCEL DATA: (C:\MyExcelVB.xls) Region Office Sales North Alpha 100 East Beta 120 West Alpha 130 North Beta 100 East Beta 140 West Alpha 110 Thank you, Regards, Sovon |
| 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) | |||
| Print Pivot Table headers on pages with Pivot Table | Excel Worksheet Functions | |||
| Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
| Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
| How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
Linear Mode
