lotus script problem

[strangeloop]

Hello all, I have recently started studying lotus notes and there is so much to take in. I was hoping any kind soul could spare a couple of minutes to look at this code. I am trying to plot 2 fields, "Name" and "age" from a view onto a chart. The charts takes the information but does not plot it correctly. This is what is displayed;

http://img134.imageshack.us/my.php?image=chart10zc.png

This is the code I'm using which is behind an action button in the view. Thank you for any suggestions or advice you might have.

Regards
Stephen


Dim NS As New NotesSession
Dim WS As New NotesUIWorkspace
Dim DB As NotesDatabase
Dim col As NotesDocumentCollection
Dim doc As Notesdocument
Dim yescount As Integer,nocount As Integer
Dim tracktest As String
Dim Choice As String
Dim LVal(1) As String


Set DB = NS.CurrentDatabase
Set Col = DB.UnprocessedDocuments
If Col.Count = 0 Then
Msgbox "there are no selected documents.Programme will not run"
End If

yescount = 0
nocount = 0



'Give User Choice of chart type
LVal(0) = "Bar Chart"
LVal(1) = "Pie Chart"
Choice = WS.Prompt(Prompt_OKCancelList , "Chart Selection Box","Please Select a Chart","Pie Chart",LVal)

'Excel automation code. Written so we can display data in a Graph / pie chart.
Dim xlApp As Variant
Dim xlSheet As Variant
Dim xlob As Variant

Set xlApp = CreateObject("Excel.application")
xlApp.Visible=True
xlApp.Workbooks.add

Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
xlSheet.Range("A1").Value = "age"
xlSheet.Range("A2").Value= yescount
xlSheet.Range("B1").Value = "name"
xlSheet.Range("B2").Value = nocount


For i = 1 To Col.count
Set doc = Col.GetNthDocument(i)

tracktest = doc.track(0)

If tracktest = "Yes" Then
yescount = yescount + 1
Else
nocount = nocount +1
End If

xlsheet.cells(i+3,"A").Value = doc.age(0)
xlsheet.cells(i+3,"B").Value = doc.Name(0)

Next

'add chart information
xlApp.Charts.add
Dim ourchart As Variant
Dim Chartchoice As Integer
Set ourchart = XlApp.Charts("Chart1")
If Choice = "Pie Chart" Then
Chartchoice = -4102
Else
'ChartChoice = 51
ChartChoice = 60
End If
ourchart.chartType = Chartchoice
ourchart.setsourcedata(xlSheet.Range("$A$1:$B$"& col.Count & "2"))
With ourchart
.HasTitle = True
.ChartTitle.Characters.Text = "Results"
.HasLegend = True
.ApplyDataLabels
End With

'Close our main references to Excel
Set xlSheet = Nothing
Set xlApp =Nothing

 

[Troodos]

Try the following on the line reading
ourchart.setsourcedata(xlSheet.Range("$A$1:$B$"& col.Count & "2"))

Try to modify to:
ourchart.setsourcedata(xlSheet.Range("$A$1:$B$"& Cstr(col.Count) & "2"))

Just to make sure there is no number/text translation 'conflict' there...

Brgds,

TrooDOS