Play with Pivot Table in Lotus Notes
I finally got this job for use Pivot table in Lotus Notes. It appears that you must use numbers to set some string values of objects. For example, a field can have a pivot function (As sum, avg, min, max, etc.). In VBA, you should enter. Function.xlAverage (without quotes) to generate an average value for the field. Is necessary in LotusScript write. Function = “2″ (the third element of the list of functions availalable, the count begins with “0″). In general, if you find a property or method that has mulitple value options, check the OLE aid options and convert text into a number string that begins with “0″. You’ll need to experiment with each specific range of options to make sure they follow the pattern.
I also got a treat from the site Iris Notes does not correctly implement extended dot notation. You must explicitly define the various objects and then use some minimum point format. Below you will find that the workbook, worksheet, and the pivot table are all defined as objects rather than using a idividual WB.WS.PT. (Method or property) format. I’m back and I tried something similar with spreadsheets (1). Worksheets (1). Pivot (1). and it worked.
You can also substitute the name of the book, sheet, etc in the PivotTable control. Workbooks (1). Worksheet (“Sheet1.” Pivot (TableNameHere). It works. Simple, right? Anyway, here’s some simple code that creates a pivot table in an existing Excel spreadsheet. In real life, you’d want to export the appropriate data, then create the table. I hope this helps others stumble around trying to figure out how to write OLE calls to MS products.
Sub Click(Source As Button)
Dim filename As String
Dim xlApp As Variant
Dim xlsheet As Variant
Dim objWB As Variant
Dim objWS As Variant
Dim objPT As Variant
Dim objRF As Variant
filename = “H:\pivot.xls”
Set xlApp = CreateObject(“Excel.application”)
xlApp.Visible = True ‘user can see Excel sheet. Set to False to keep it invisible
xlApp.Workbooks.open filename
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
xlsheet.Activate
GenPivotTable:
‘ This code generates an empty Pivot Table
With xlsheet.PivotTableWizard
‘ .SourceType = “1″ Can’t find a property for source type
.SourceData = (“Sheet1!R1C1:R11C5″) ‘ sets source data range
‘ .TableRange2 = “Sheet2!R1C1:R20C3″ Can’t find a property for destination table, Excel will default pivot table to
‘ SheetMax# +1
.Name = “AvgCycleTimeByArea” ‘ sets pivot table name
End With
Set objWB = xlApp.Workbooks(1)
Set objWS = objWB.Worksheets(1) ‘number = place of tab in workbook, 1 = top sheet
Set objPT = objWS.PivotTables(1)
‘ If you build a pivot table in Excel and record the build as a macro, you’ll see a command that sets
‘ the row field. The command looks something like .AddFields.RowFields = “col1″. Won’t work in LotusScript.
‘ The following remarked section will generate a single row field. I prefer to use the more generic code
‘ listed below this remarked section
‘ objPT.AddFields(“col1″)
‘This works and sets a single row field. can’t figure out how to set mulitple row fields
‘ or other AddField values like columnField, etc.
‘ This is a more general way to set rows, columns, data, and page fields
‘ 0 = hidden, 1 = row field, 2 = column field, 3 = page field, 4 = data field
‘ This code sets the values for rows and columns fields. It could be used to set page fields as well
objPT.PivotFields(“col1″).Orientation = “1″ ‘row
objPT.PivotFields(“col3″).Orientation = “1″ ‘row
objPT.PivotFields(“col4″).Orientation = “2″ ‘column
With objPT.PivotFields(“col2″)
.Orientation = “4″ ’4 = xlDataField
.Name = “Average of Number of NumShifts”
.Function = “2″ ’2 = Avg
End With
End Sub
Viewed 34990 times by 12334 viewers
