Creating Charts With Ms-Excel

The following code sample puts some sample data into an EXCEL Sheet and creates a chart in the currently opened...

Notes document. The doc must have a field ("Chart"), richtext The DECLARATION SECTION holds some constants for different ChartStyles. Sub Click(Source As Button) 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 uidoc.EditMode = True ChartName$ = "Test" Set xlApp = CreateObject("Excel.Application") xlApp.Visible = False 'set to invisible Set oworkbook = xlApp.Workbooks 'handle to Workbook oworkbook.Add '/* BEGIN OF SAMPLE DATA xlApp.cells(1,2) = "100" xlApp.cells(2,2) = "140" xlApp.cells(3,2) = "1000" xlApp.cells(4,2) = "100" xlApp.cells(5,2) = "1900" xlApp.cells(6,2) = "1340" xlApp.cells(1,1) = "Uli" xlApp.cells(2,1) = "Kathleen" xlApp.cells(3,1) = "Freitz" xlApp.cells(4,1) = "Wombat" xlApp.cells(5,1) = "Eddi" xlApp.cells(6,1) = "Hannelore" '/* END OF SAMPLE DATA xlApp.Range("A1:B6").Select 'select the data rows and create a chart xlApp.Charts.Add xlChartType = xl3DColumn 'set chart type; see DECLARATION SECTION xlLocationAsObject = 2 With xlApp.ActiveWorkbook.ActiveChart .Name = ChartName$ .HasTitle = True .HasLegend = False .ChartTitle.Text = "Test: " .ChartType = xlChartType .PlotArea.Interior.ColorIndex = "0" .Location xlLocationAsObject, ChartName$ 'move chart to worksheet End With 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.85, msoFalse, msoScaleFromTopLeft 'change chart dimensions xlApp.ActiveSheet.Shapes("Chart 1").ScaleHeight 1.24, msoFalse, msoScaleFromTopLeft xlApp.ActiveChart.ChartGroups(1).GapWidth = 10 'reduce width between columns Call uidoc.GotoField( "Chart" ) 'select rt field chart in current doc Call uidoc.Paste 'paste clipboard contents into field Call doc.Save( True, True ) xlApp.ActiveWorkbook.saved = True ' closes EXCEL without prompting Call xlApp.quit End Sub '/Paste the following into the DECLARATION-SECTION Const xl3DArea=-4098 Const xl3DAreaStacked=78 Const xl3DAreaStacked100=79 Const xl3DBar=-4099 Const xl3DBarClustered=60 Const xl3DBarStacked=61 Const xlLine=4 Const xlLineMarkers=65 Const xlLineMarkersStacked=66 Const xlLineMarkersStacked100=67 Const xlLineStacked=63 Const xlLineStacked100=64 Const xl3DBarStacked100=62 Const xl3DColumn=-4100 Const xl3DColumnClustered=54 Const xl3DColumnStacked=55 Const xl3DColumnStacked100=56 Const xl3DLine=-4101 Const xl3DPie=-4102 Const xl3DPieExploded=70 Const xl3DSurface=-4103 Const xlAreaStacked=76 Const xlAreaStacked100=77 Const xlBarClustered=57 Const xlBarOfPie=71 Const xlBarStacked=58 Const xlBarStacked100=59 Const xlBubble=15 Const xlBubble3DEffect=87 Const xlColumnStacked=52 Const xlColumnStacked100=53 Const xlConeBarClustered=102 Const xlConeBarStacked=103 Const xlConeBarStacked100=104 Const xlConeCol=105 Const xlConeColClustered=99 Const xlConeColStacked=100 Const xlConeColStacked100=101 Const xlCylinderBarClustered=95 Const xlCylinderBarStacked=96 Const xlCylinderBarStacked100=97 Const xlCylinderCol=98 Const xlCylinderColClustered=92 Const xlCylinderColStacked=93 Const xlCylinderColStacked100=94

This was first published in November 2000

Dig Deeper



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:




  • iSeries tutorials'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 ...