Excel 3D Charts In Notes (Lotusscript + Visualbasic)

To construct 3D Pie, 3D Column, 3DBar Charts in Notes with MS_Excel use the
following 3DChart Agent code:
To construct 3D Pie, 3D Column, 3DBar Charts in Notes with MS_Excel use the
following 3DChart Agent code:

Sub Initialize
Dim session As New notessession
Dim workspace As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Dim doc As notesdocument
Set uidoc = workspace.CurrentDocument
Set doc = uidoc.Document
Dim rtitem As NotesRichTextItem
Dim object As NotesEmbeddedObject
Dim xlApp As Variant
Dim oWorkbook As Variant

Dim db As NotesDatabase
Dim view As NotesView
Dim entry As NotesViewEntry
Dim CatName(1 To 50) As String 'Names of docs
categories
Dim NumInCat(1 To 100) As Integer 'Number of docs in
category
Dim category As String
Dim skaits As Integer
Dim cellEnd As String
Dim sForm As String 'Get view name from
field

uidoc.EditMode = True
ChartName$ = "Documents in MyWork database"
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False 'set to invisible
Set oworkbook = xlApp.Workbooks 'handle to Workbook
oworkbook.Add

' BEGIN OF DATA COLLECTION
sForm = doc.SearchFormat(0) 'get a view name to searc in
from form field
Set db = session.CurrentDatabase
Set view = db.GetView(sForm)
Set vc = view.AllEntries
Set entry = vc.GetFirstEntry()

'Collecting names of Categories and Number of documents in a category
k = 1
m = 0
n = 1
CatName(k) = entry.ColumnValues(1)
While Not ( entry Is Nothing )
k = k + 1
CatName(k) = entry.ColumnValues(1)
If ( CatName(k) = CatName(k-1) ) Then
m = m + 1
NumInCat(n) = m
Else
n = n + 1
CatName(n) = entry.ColumnValues(1)
m = 1 'restart counting
NumInCat(n) = m
End If
Set entry = vc.GetNextEntry(entry)
Wend
cellEnd = "A1:B"+n 'puts the data rows
range

'Retreeving data into MS_XL worksheet cells
For i = 1 To 2
For j = 1 To n
If (i = 1) Then
xlApp.cells(j,i) = CatName(j)
Else
xlApp.cells(j,i) = NumInCat(j)
End If
Next
Next

' END OF DATA COLLECTION

xlApp.Range(cellEnd).Select 'data rows selection to
create the chart
xlApp.Charts.Add
If(doc.ChartType(0) = "3D Pie Chart") Then 'get type of
chart from form
xlChartType = -4102 'code of 3DPie chart
Elseif(doc.ChartType(0) = "3D Column Chart") Then
xlChartType = 54 'code of 3DColumnClastered
Chart
Else
xlChartType = 60 'code of 3DBar Chart
End If
xlLocationAsObject = 2
With xlApp.ActiveWorkbook.ActiveChart
.Name = ChartName$
.HasTitle = False 'chart title
If(xlChartType = -4102) Then
.HasLegend = False'True
Else
.HasLegend = False
End If
'.ChartTitle.Text = "Documents in MyWork database"
.ChartType = xlChartType
.PlotArea.Interior.ColorIndex = "0"
.Location xlLocationAsObject, ChartName$ 'move chart to worksheet
End With

If(xlChartType = 54) Then
xlApp.ActiveWorkbook.ActiveChart.Perspective=1'set RightAngleAxes
Elseif(xlChartType = -4102) Then
xlApp.ActiveWorkbook.ActiveChart.SeriesCollection(1).ApplyDataLabels(5)
'labels of sectors
End If

xlApp.ActiveSheet.ChartObjects("Chart 1").Activate
xlApp.ActiveChart.ChartArea.Select 'select chart
xlApp.ActiveChart.ChartArea.Copy 'copy to clipboard
xlApp.ActiveSheet.Shapes("Chart 1").ScaleWidth 0.79, msoFalse,
msoScaleFromTopLeft 'change chart dimensions
xlApp.ActiveSheet.Shapes("Chart 1").ScaleHeight 1.00, msoFalse,
msoScaleFromTopLeft

This was first published in November 2000

Dig Deeper

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchWindowsServer

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchEnterpriseLinux

SearchDataCenter

SearchExchange

SearchContentManagement

Close