Domino 9 und frühere Versionen > ND6: Entwicklung

Exceldiagramm mit Notes erstellen

<< < (2/2)

Johnson:
Ich hab deien geposteten code mal in einem Agenten ausprobiert und hab ein bisschen umstellen müssen:


--- Code: ---Sub Initialize


' ### create workbook ###

' create an excel instance

Dim xlApp As Variant ' excel application object

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

' add a workbook

Dim xlWorkbooks As Variant ' workbook list object
Dim xlWorkbook As Variant ' workbook object

Set xlWorkbooks = xlApp.Workbooks
Set xlWorkbook = xlWorkbooks.Add


' ### create table ###

' get the first sheet of the new workbook

Dim xlSheets As Variant ' worksheet list object of one workbook object
Dim xlSheet As Variant ' worksheet object

Set xlSheets = xlWorkbook.Sheets
Set xlSheet = xlSheets.Item(1) ' or type Set xlSheet = xlSheets(1)

' write row titles

Dim rowtitles As Variant ' range object
Dim var_rowtitles As Variant ' values of the range object rowtitles

Set rowtitles = xlSheet.Range(xlSheet.Cells(2, 1), xlSheet.Cells(5, 1)) ' get the range "A2:A5"
var_rowtitles = rowtitles.Value
var_rowtitles(1, 1) = "Fehler1"
var_rowtitles(2, 1) = "Fehler2"
var_rowtitles(3, 1) = "Fehlersumme"
var_rowtitles(4, 1) = "Verbrauch"
rowtitles.Value = var_rowtitles

' write first column

Dim column As Variant ' range object
Dim var_column As Variant ' values of the range object column

Set column = xlSheet.Range(xlSheet.Cells(1, 2), xlSheet.Cells(5, 2)) ' get the range "B1:B5"
var_column = column.Value
var_column(1, 1) = "A"
var_column(2, 1) = 4
var_column(3, 1) = 3
var_column(4, 1) = 7
var_column(5, 1) = 70
column.Value = var_column

' write second column
Set column = xlSheet.Range(xlSheet.Cells(1, 3), xlSheet.Cells(5, 3)) ' get the range "C1:C5"
var_column = column.Value
var_column(1, 1) = "B"
var_column(2, 1) = 3
var_column(3, 1) = 2
var_column(4, 1) = 5
var_column(5, 1) = 50
column.Value = var_column


' ### create chart ###

Const xlLocationAsObject = 2
Const xlColumnClustered = 51
Const xlRows = 1

' add new chart to workbook

Dim xlCharts As Variant ' charts list object of one workbook object
Dim xlChart As Variant ' chart object
Dim sc As Variant ' series collection object

Set xlCharts = xlWorkbook.Charts ' get the chart collection of xlWorkbook
Set xlChart = xlCharts.Add ' create a new chart in xlCharts
xlChart.ChartType = xlColumnClustered
Call xlChart.SetSourceData(xlSheet.Range(rowtitles, column), xlRows) ' form row titles to last wridden column
xlChart.HasTitle = True
xlChart.ChartTitle.Characters.Text = "Vergleich: "
xlChart.HasLegend = False
xlChart.PlotArea.Interior.ColorIndex = "0"

' data table
xlChart.HasDataTable = True
xlChart.DataTable.ShowLegendKey = True
xlChart.DataTable.font.size = 8

' series properties
Set sc = xlChart.SeriesCollection ' get the speries collection of the xlChart obcect
xlChart.ChartGroups(1).GapWidth = 10
sc(1).Interior.ColorIndex = 27   
sc(2).Interior.ColorIndex = 32 
xlChart.Location xlLocationAsObject, xlSheet.Name


' ### set shape properties ###

Const msoScaleFromTopLeft = 0
Const msoFalse = 0
Dim xlShape As Variant ' shape object

Set xlShape = xlSheet.Shapes(1) ' get the first shape of xlSheet's shape collection
xlShape.ScaleWidth 1.50, msoFalse, msoScaleFromTopLeft
xlShape.ScaleHeight 1.24, msoFalse, msoScaleFromTopLeft
xlShape.IncrementLeft -183
xlShape.IncrementTop -122.25


End Sub
--- Ende Code ---

Wichtig für dich ist die Zeile:
Call xlChart.SetSourceData(xlSheet.Range(rowtitles, column), xlRows)
Der Ausdruck xlSheet.Range(rowtitles, column) gibt ein Range Objekt zurück.
Range-Objekte können einzenle Zellen sein (xlSheet.Cells(1)) oder mehrere Zellen (xlSheet.Range("A1:A2") oder xlSheet.Range(xlSheet.Cells(1), xlSheet.Cells(2)))
Der Methode SetSourceData muss ein solches Range-Objekt erhalten.

Navigation

[0] Themen-Index

[*] Vorherige Sete