Lotus Notes and Excel Pivot table.

Atlast I was able to generate a Pivot Table in Excel from Lotus Notes. Excel is very powerful, so by using its features reporting becomes very simple. I am posting the approach and code here so that it may be of use to any one who may come across the same problem.

Lotus Client : 5.08
Excel : Version 92

Challenges

1. An error message stating cannot access the property of the class or cannot access the method. For eg. "Unable to get the PivotTableWizard property of the Worksheet class"

Approach

1. Lotus Script may not recognize the extented syntax for the OLE object, so every object has to be individually declared and accessed.
2. Sheet Name shouldnot contain spaces

Code

See the thread in Lotus Forum for I have posted the code.

Sub createPivotTable (xlSheet As Variant,datarange As String,targetsheet As

String,xlPivotTableName As String, xlFieldlist List As PivotFieldList)

Dim xlPivotField As Variant

Dim xlPivotTable As Variant

Call xlsheet.PivotTableWizard ( 1, dataRange, targetSheet,xlPivotTableName)

Set xlPivotTable = xlSheet.PivotTables(xlPivotTableName)

Forall x In xlFieldList

Set xlPivotField =xlPivotTable.PivotFields(Listtag(x))

xlPivotField.Orientation = xlFieldlist(Listtag(x)).xlOrientation

If xlFieldlist(Listtag(x)).xlFunction<>0 Then

xlPivotField.Function = xlFieldlist(Listtag(x)).xlFunction

End If

End Forall

End Sub