Domino Code Fragment

Code Name*
Build a Table in Excel from Lotus Notes.		Date*
06/28/2025		Source (or email address if you prefer)*
[email protected]
IP address:.172.69.7.76
Description*
Simple code that builds a pivot table in an existing Excel spreadsheet		Type*
LotusScript		Categories*
(Misc)
Implementation:
Required Client:
Server:
Limitations:
Comments:
Files/Graphics attachments (if applicable): Code:
Author: Doug Finner

Date: Friday, 9/25/98 1:23 PM EDT
Subject: Re: OLE, Excel, and Pivot Tables - Some Code

--------------------------------------------------------------------------------

I finally got this to work. Turns out that you need to use string numbers to set some of the values of objects. For instance, a pivot field can have a function
(like sum, avg, min, max, etc). In VBA, you'd enter .Function.xlAverage (no quotes) to generate an average value for the field. In LotusScript you need to
write .Function = "2" (the third item in the list of availalable functions, counting starts with "0"). In general, if you find a property or method that has mulitple
value options, check out the OLE help and convert the options from text to a string number beginning with "0". You'll need to experiment with each specific
set of options to make sure that they follow the pattern.

I also picked up a tidbit from the Iris site that Notes won't correctly implement extended dot notation. You need to explicitly define various objects and then use
some minimal dot formatting. Below you'll notice that the Workbook, worksheet, and pivot table are all defined as idividual object rather than using a
WB.WS.PT.(method or property) format. I went back and tried using something like Workbooks(1).Worksheets(1).PivotTables(1). and it worked.
You can also substitute the name of the book, sheet, pivot table etc into the command.
Workbooks(1).Worksheet("Sheet1".PivotTables("TableNameHere"). DOES work.

Simple, No? Anyway, here's some simple code that builds a pivot table in an existing Excel spreadsheet. In real life, you'd want to export the approprite data,
then build the table. I hope this helps others stumbling 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