I am looking for some sample code by which i get an idia that how to generates reports in excel using lotus notes. Reports are in graphical format. Thanks.
Subject: I want to generate graphical reports in excel using lotus notes
Some suggestions:Search the forum - lots of stuff of interest on this topic.
Use the FAQ of FAQs - great links to great stuff on how to interface with excel and how to figure out how to make things work.
Sandbox
Doug
Subject: I want to generate graphical reports in excel using lotus notes
Hi,
I recentley worked on a project where graphical reports were required which I pulled from Excel using LotusScript.
Unfortunatly this was a project which I was working on in my spare time and do not have the code with me at present (im in work!). I can have the code for you tomorrow if you wish as it sounds exactly the same as you need (queries data in Notes, outputs to Excel and returns a graph to a RTF on thhe form). Please let me know if you still require it and I will post it here tomorrow.
Regards
Subject: RE: I want to generate graphical reports in excel using lotus notes
Tom - could you post that regardless, might be of use for others. Thanks
Subject: RE: I want to generate graphical reports in excel using lotus notes
Will do.
Subject: RE: I want to generate graphical reports in excel using lotus notes
Hi Tom,I have a request like once you get some time or You feel free please send me that code i will be very thankful to you for your great help.
Great Regards,
Mudit Sharma
Subject: RE: I want to generate graphical reports in excel using lotus notes
Hi Tom,I am very thankful to you for ur response, actully we r finalizing the format of the reports yet, how they look like. ya that is same like i need to generate some reports to get the data from lotus notes and generate the reports in excel format. i will be very thankful to you if You please sugest me some code some agents by whch i can do the same. i have’t any idia on this topic only i know we can use create object for that same but i do not know which methods we can use. even i will expect you to sugest me some website by which i can study about that same topic.
Waiting for your positive reply…
Thanks & Regards
Mudit Sharma
Subject: I want to generate graphical reports in excel using lotus notes
Here are excerpts to give you an idea. Let me know if you need more…
This code is from a database that holds consultant time. There is additional code that loops through various document collections and adds up things like number of days worked, holiday etc for each consultant and assigns them to variables.
In order to add a chart, you have to add the data table first that the chart will run from. The best way to learn which chart type is best is to experiment in Excel first to get the chart looking the way you want it to.
I have not included all variable declarations
Dim xlApp As Variant
Dim xlActiveWorkBook As Variant
Dim xlSheets As Variant
'Constants for charts
Const xlPie=5
Const xlLocationAsObject = 2
Const xlColumns=2
'Borders
Const xlEdgeLeft=7
Const xlEdgeTop=8
Const xlEdgeBottom=9
Const xlEdgeRight=10
Const xlInsideVertical=11
Const xlInsideHorizontal=12
'Alginment
Const xlRight=-4152
Const xlLeft=-4131
Const xlCenter=-4108
Const xlBottom=-4107
'Colours
Const cWhite=2
Const cRed=3
Const cGreen=4
Const cBlue=5
Const cYellow1=6
Const cBrickRed=9
Const cDarkBlue=11
Const cGrey=15
Const cPaleYellow=19
Const cOrange=23 'This may in fact be dark blue!
Const cBlueGrey=24
Const cPaleBlue=34
Const cBlueGreen=35
Const cBrightYellow=36
Const cPink=40
Const cYellow=44 'This is more like orange
Const xlAutomatic=-4105
'Border Constants
'Line styles
Const xlContinuous=1
Const xlDot=-4118
'Weights
Const xlThin=2
Const xlMedium=-4138
Const xlSolid=1
Const xlHairline=1
Const xlNone=-4142
'Create a spreadsheet and set variables to the main bits we need
Set xlApp=CreateObject(“Excel.Application”)
xlApp.Workbooks.Add
Set xlActiveWorkbook=xlApp.ActiveWorkbook
Set xlsheets=xlActiveWorkbook.Worksheets
With xlSheets(1)
'Set the titles of my data table
.Range(“B4”).value=“Days”
.Range(“C4”).value=“(Mins)”
.Range(“B4:C4”).font.bold=True
.Range(“A5”).value=“Total Working days”
.Range(“B5”).value=workingdays
With .Range(“A5:C5”).Borders(xlEdgeTop)
.LineStyle =xlContinuous
.Weight =xlThin
.ColorIndex = xlAutomatic
End With
With .Range(“A5:C5”).Borders(xlEdgeBottom)
.LineStyle =xlContinuous
.Weight =xlThin
.ColorIndex = xlAutomatic
End With
.Range(“A6”).value=“Charge”
.Range(“A7”).value=“Deduct”
.Range(“A8”).value=“Free”
.Range(“A9”).value=“Phone Support”
.Range(“A10”).value=“Training”
.Range(“A11”).value=“Holiday”
.Range(“A12”).value=“Sick”
.Range(“A13”).value=“Tasks”
.Range(“A14”).value=“Other Work days”
.Range(“A15”).value=“Unallocated”
'Now set the values based on the totals we have added up from looping through various document collections
.Range(“B6”).value=dblCharge
.Range(“B7”).value=dblDeduct
.Range(“B8”).value=dblFree
If dblSupportMins>0 Then
.Range(“B9”).value=“=” & Cstr(dblSupportMins) & “/(8*60)”
Else
.Range(“B9”).value=0
End If
.Range(“C9”).value=dblSupportMins
.Range(“B10”).value=dblTraining
.Range(“B11”).value=holcount
.Range(“B12”).value=dblSick
.Range(“B13”).value=dblTask
.Range(“B14”).value=dblOther
.Range(“B15”).value=“=R[-10]C-SUM(R[-9]C:R[-1]C)” 'this should be a formula to calculate what is left
'get ready for the chart as the name of the series has to be defined in a specific way
For i=6 To 15
If .Range(“B” & Cstr(i)).value<>0 Then
If strSeries=“” Then
strSeries=“=(” & consInitial(0) & “!R” & Cstr(i) & “C2”
strXvalues=“=(” & consInitial(0) & “!R” & Cstr(i) & “C1”
Else
strSeries=strSeries & “,” & consInitial(0) & “!R” & Cstr(i) & “C2”
strXvalues=strXvalues & “,” & consInitial(0) & “!R” & Cstr(i) & “C1”
End If
End If
Next
strSeries=strSeries & “)”
strXValues=strXvalues & “)”
End With
'Now add the chart
xlActiveWorkbook.Charts.Add
With xlActiveWorkbook.Charts(1)
.ChartType = xlPie
.HasTitle = True
.ChartTitle.Text = strInterval & "ly Support Breakdown for " & nnCons.Common & " w/c " & Format$(dtStart.LsLocalTime,“dd/mm/yyyy”)
.SeriesCollection(1).Values = strSeries
.SeriesCollection(1).XValues=strXValues
.Legend.clear
.PlotArea.Height=400
.PlotArea.Width=400
.PlotArea.Left = 120
.PlotArea.Top = 40
Call .SeriesCollection(1).ApplyDataLabels( ,False,True,True, False, True, False, True, False,Chr(10))
Call .Location(xlLocationAsObject,consInitial(0))
xlSheets(1).Shapes(“Chart 1”).IncrementLeft -173.4
xlSheets(1).Shapes(“Chart 1”).IncrementTop 111
End With
'Ta -da! show the user your lovely chart
xlApp.visible=True
=========
BTW you need to add an error trap that kills the Excel application, otherwise when you get it wrong you will end up with lots of EXCEL.EXE processes on your PC until it runs out of memory:
Add this to your error trap…
If Not (xlApp Is Nothing) Then
xlActiveWorkbook.saved=True 'stops app from prompting user to save
xlApp.Quit
End If
Subject: I want to generate graphical reports in excel using lotus notes
This is not a sample application , but you might get an idea or two.http://notessidan.se/A55B53/blogg.nsf/plink/TADN-6UAFBL
TThomas