在程序中操作excel的图表的问题,50分求救!

[dragon_little]

我要在我的程序中显示一个曲线图,图已经能够显示了,但图中X轴刻度标志的字体无法改变.代码如下,望各位大虾指点一下,谢谢:

Dim xlsapp As New Excel.Application
Dim xlsbook As Excel.Workbook
Dim xlssheet As Excel.Worksheet

Set xlsbook = xlsapp.Workbooks.Add
Set xlssheet = xlsbook.Worksheets("sheet1")

'测试数据
X(1) = 0.1
X(2) = 0.2
X(3) = 0.3
X(4) = 0.4
Y(1) = 10
Y(2) = 20
Y(3) = 30
Y(4) = 40

For i = 1 To 4 Step 1
xlssheet.Cells(i, 1) = X(i)
xlssheet.Cells(i, 2) = Y(i)
Next

'插入图表
xlssheet.Range("A1:B4").Select
xlsapp.Charts.Add

xlsapp.ActiveChart.ChartType = xlXYScatterLines
xlsapp.ActiveChart.SetSourceData Source:=xlssheet.Range("A1:B4"), PlotBy _
:=xlColumns
xlsapp.ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
xlsapp.ActiveChart.HasLegend = False
xlsapp.ActiveChart.HasPivotFields = False
xlsapp.ActiveChart.HasDataTable = False
xlsapp.ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False

With xlsapp.ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.Axes(xlCategory).MajorUnit = 0.5
.ChartArea.Select
.ChartArea.Copy
End With

'问题在下面这一块,不加这一段代码,图就能显示出来,但字体不能改变.加了这块代码,连图都不能显示了,但这块代码主要是用来修改字体的,为什么出这样的问题,百思不得其解.

'********************************************
xlsapp.ActiveChart.Axes(xlCategory).Select
xlsapp.Selection.TickLabels.AutoScaleFont = True
With xlsapp.Selection.TickLabels.Font
.Name = "宋体"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
'*******************************************************

xlsapp.DisplayAlerts = False
xlsbook.Close
xlsapp.Quit
Set xlssheet = Nothing
Set xlsapp = Nothing

'粘贴图表
Image1.Picture = Clipboard.GetData

[xinliangyu]

看一下我在上３楼的代码，就没有使用什么selection

[cxianfa]

学习

[dragon_little]

to xinliangyu(yxl):
你说的上三楼的代码在哪里?能不能就在这里发给我看一下,如果确实可以,立即给分

[dragon_little]

我刚才把你的那段代码也试过了,还是不行,图显示不出来.

[dragon_little]

各位大虾帮帮忙呀

[faysky2]

呵呵，估计是字体设置会破坏剪切板吧，你在设置完字体后，在复制图就好了，代码改为这样就OK:

Dim xlsapp As New Excel.Application
Dim xlsbook As Excel.Workbook
Dim xlssheet As Excel.Worksheet

Set xlsbook = xlsapp.Workbooks.Add
Set xlssheet = xlsbook.Worksheets("sheet1")

'测试数据
x(1) = 0.1
x(2) = 0.2
x(3) = 0.3
x(4) = 0.4
y(1) = 10
y(2) = 20
y(3) = 30
y(4) = 40

For i = 1 To 4 Step 1
xlssheet.Cells(i, 1) = x(i)
xlssheet.Cells(i, 2) = y(i)
Next

'插入图表
xlssheet.Range("A1:B4").Select
xlsapp.Charts.Add

xlsapp.ActiveChart.ChartType = xlXYScatterLines
xlsapp.ActiveChart.SetSourceData Source:=xlssheet.Range("A1:B4"), PlotBy _
:=xlColumns
xlsapp.ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
xlsapp.ActiveChart.HasLegend = False
xlsapp.ActiveChart.HasPivotFields = False
xlsapp.ActiveChart.HasDataTable = False
xlsapp.ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False

With xlsapp.ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.Axes(xlCategory).MajorUnit = 0.5
.ChartArea.Select
'.ChartArea.Copy '<------------------------------------这句可以注释掉
End With

'问题在下面这一块,不加这一段代码,图就能显示出来,但字体不能改变.加了这块代码,连图都不能显示了,但这块代码主要是用来修改字体的,为什么出这样的问题,百思不得其解.

'********************************************
xlsapp.ActiveChart.Axes(xlCategory).Select
xlsapp.Selection.TickLabels.AutoScaleFont = True
With xlsapp.Selection.TickLabels.Font
.Name = "宋体"
.FontStyle = "常规"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
'*******************************************************
xlsapp.ActiveChart.ChartArea.Copy '<------------------------------------------设置完字体后，在复制图就好了

xlsapp.DisplayAlerts = False
xlsbook.Close
xlsapp.Quit
Set xlssheet = Nothing
Set xlsapp = Nothing

'粘贴图表
Image1.Picture = Clipboard.GetData

[xinliangyu]

从代码上看不出明显的问题．把font的一些默认属性行去掉看行不，比如改成：
With xlsapp.ActiveChart.Axes(xlCategory).TickLabels.Font
.Name = "宋体"
.Size = 10
End With
如能成，则一次一项地添加属性赋值语句，也能找到问题所在？

[dragon_little]

试过不行.如果把里面的属性全去掉,程序不出错图表也能显示出来.只是字体不能改,比如:
xlsapp.ActiveChart.Axes(xlCategory).Select
xlsapp.Selection.TickLabels.AutoScaleFont = True
With xlsapp.Selection.TickLabels.Font
'里面内容全去掉
End With
with....end with中加入一个属性就不可以显示了.

[dragon_little]

我试了一下,发现使用了selection这个对象就不行了.大虾们,应该如何正确的使用selection这个对象呀

[dragon_little]

to faysky2():
你说的是对的，太感谢了，立刻加分：）

[xinliangyu]

//看一下我在上３楼的代码，就没有使用什么selection
就是这段：
　　With xlsapp.ActiveChart.Axes(xlCategory).TickLabels.Font
.Name = "宋体"
.Size = 10
　　　　……
End With