Tip

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.