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