hi ,i am using the following code to get piechart and bar chart. in piechart i am getting error that “OLE-Automation Error” in the code
“ourchart.setsourcedata(xlSheet.Range(“A+cstr(i):B+cstr(i)”))”
any idea?
Sub Initialize
Dim ws As New NotesSession
Dim NS 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
Dim view As notesview
Dim view1 As notesview
’ Dim doc As notesdocument
Dim doc1 As notesdocument
Dim CompanyName1 As Variant
Dim CompanyName2 As Variant
Set db = ws.currentdatabase
Set view = db.getview(“(sbuview)”)
Set view1 = db.getview(“Master Revenue2”)
’ 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
’ 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
’ Next
'Give User Choice of chart type
LVal(0) = “Bar Chart”
LVal(1) = “Pie Chart”
Choice = NS.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(“A2”).Value= yescount
'xlSheet.Range(“B1”).Value = “Untracked Projects”
'xlSheet.Range(“B2”).Value = nocount
‘’‘’’
'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
‘’’
Dim i
Dim k
Dim p
Dim l
Set doc = view.getfirstdocument
k =0
p =0
Do Until doc Is Nothing
k = k+1
Set doc = view.getnextdocument(doc)
Loop
Redim Preserve ctr(k)
Dim collection As notesdocumentcollection
Redim Preserve ptr(k)
Redim Preserve mtr(k)
Dim tot
l = 1
Set doc = view.getfirstdocument
Do Until doc Is Nothing
ctr(l) = Cstr(doc.sbu_x(0))+" " + Cstr(doc.subsbu_x(0))+" " + Cstr(doc.revenue_n(0))
ptr(l) =Cstr(doc.sbu_x(0))+ Cstr(doc.subsbu_x(0))
Set doc = view.getnextdocument(doc)
l= l+1
Loop
For i =1 To Ubound(ctr)
xlSheet.Range(“A”+Cstr(i)).Value = ctr(i)
Set collection = view1.GetAllDocumentsByKey(Cstr(ptr(i)), False)
If collection.count = 0 Then
xlSheet.Range(“B” & Cstr(i)).Value = 0
Else
Set doc1 = collection.getfirstdocument
tot=0
Do Until doc1 Is Nothing
tot = tot+ Cint(doc1.rcombusi_x(0))
Set doc1 = collection.getnextdocument(doc1)
Loop
End If
xlSheet.Range(“B”+Cstr(i)).Value = tot
xlSheet.Range(“A”+Cstr(i)).Value = ctr(i)
ourchart.setsourcedata(xlSheet.Range(“A+cstr(i):B+cstr(i)”))
Next
With ourchart
.HasTitle = True
.ChartTitle.Characters.Text = “Tracking Chart Results”
.HasLegend = True
.ApplyDataLabels
End With
‘’‘’
'Close our main references to Excel
Set xlSheet = Nothing
Set xlApp =Nothing
End Sub