LotusScript根据数据在Excel表中生成图表

原文链接
不过文章那关于图表设置说得有些模糊，同时我自己在模仿代码的时候也遇到一些问题，所以还是来说一说。
1、插入图表，如下图：

2、选择数据源：如下图：


3、生成图表，保存Excel表格。如下图：

4、接下来就是代码了。先来初始函数：

Sub Initialize
	On Error GoTo errhandle
	
	Dim countArr(5) As Integer
	Dim session As New NotesSession
	Dim ws As New NotesUIWorkspace
	Dim db As NotesDatabase
	Dim dc As NotesDocumentCollection
	Dim doc As NotesDocument
	Dim num As Integer
	Set db = session.Currentdatabase
	Set dc = db.Unprocesseddocuments
	If dc.Count = 0 Then
		Exit sub
	End If
	For num = 1 To dc.Count
		If num<6 Then
			countArr(0) = countArr(0) + 1
		End If
		If num>5 And num<11 Then
			countArr(1) = countArr(1) + 1
		End If
		If num>10 And num<16 Then
			countArr(2) = countArr(2) + 1
		End If
		If num>15 And num<21 Then
			countArr(3) = countArr(3) + 1
		End If
		If num>20 And num<31 Then
			countArr(4) = countArr(4) + 1
		End If
		If num>30 And num<51 Then
			countArr(5) = countArr(5) + 1
		End If
	Next
	
	'生成图像
	Call generateExcelChart(Countarr, "F:\test1.xls")
	
	Dim uidoc As NotesUIDocument
	Set uidoc = ws.Composedocument("", "", "Chart")
	uidoc.Gotofield("Body")
	Call uidoc.Paste()
	
	Exit Sub
errhandle:
	MsgBox Erl & Error
	Exit sub
End Sub

5、赋值到excel表，并拷贝图表函数，但是拷贝图表的时候报错。希望大家可以教教我。

Function generateExcelChart(countArr As Variant,excelFileName As String)
	On Error GoTo errhandle
	
	Dim excelAppliction As Variant
	Dim excelWorkbook As Variant
	Dim excelSheet As Variant
	
	'创建excel对象
	Set excelAppliction = CreateObject("Excel.Application")
	
	'将excel程序设为不可见
	excelAppliction.visible = False
	
	'打开模板文件
	Set excelWorkbook = excelAppliction.Workbooks.Open(excelFileName)
	Set excelSheet = excelWorkbook.Worksheets("Sheet1")
	
	'为图标填充数据
	excelSheet.Cells(2,2) = countArr(0)
	excelSheet.Cells(2,3) = countArr(1)
	excelSheet.Cells(2,4) = countArr(2)
	excelSheet.Cells(2,5) = countArr(3)
	excelSheet.Cells(2,6) = countArr(4)
	excelSheet.Cells(2,7) = countArr(5)
	
	'设置数据源
	'Call excelSheet.ChartObjects("Chart 1").Chart.SetSourceData(excelSheet.Range(excelSheet.Cells(1,1),excelSheet.Cells(2,7)),1)
	
	'****将生成的图表复制到剪贴板,这个还有问题，会报错***
	excelSheet.ChartObjects(1).Chart.ChartArea.Copy
	
	'不保存退出程序
	excelWorkbook.close false
	excelAppliction.Quit
	
	Exit function
errhandle:
	MsgBox "generateExcelChart函数第" & Erl & "行报错：" & Error
	Exit function
End Function

6、最终只是在Excel表生成了一个柱状图，如下图：