Sub 图表批量生成()
For r = 1 To 100 Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A" & r & ":E" & r) 'ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" '删除本句前的'可将各个图表作为对象插入sheet1中,否则各图表将单独作为chart表插入工作簿。 Next End Sub =================== Sub 图表批量生成() xx = 0 yy = 0 For r = 4 To 57 '以每位学生生成一个图表,循环产生全班每位学生的曲线图 Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("一班全图").Range("A" & r & ":U" & r), PlotBy _ :=xlRows '源数据系列产生于行 ActiveChart.Location Where:=xlLocationAsObject,Name:="一班全图" '所有图表插入同一工作表中 With ActiveChart.Axes(xlValue) '设置图表属性 如:刻度和线型 .MinimumScaleIsAuto = True .MaximumScale = 60 .MinorUnit = 1 .MajorUnit = 5 .Crosses = xlAutomatic .ReversePlotOrder = True .ScaleType = xlLinear .DisplayUnit = xlNone End With ActiveChart.Legend.Select Selection.Delete ActiveChart.SeriesCollection(1).Select With Selection.Border .ColorIndex = 3 .Weight = xlMedium .LineStyle = xlContinuous End With With Selection .MarkerBackgroundColorIndex = xlAutomatic .MarkerForegroundColorIndex = 5 .MarkerStyle = xlCircle .Smooth = False .MarkerSize = 3 .Shadow = False End With '图表属性设置结束 Set myDocument = ActiveSheet For Each S In myDocument.ChartObjects 'MsgBox (S.Name) S.Activate ActiveChart.ChartArea.Select '设置图表(即外框)大小及在工作表中的位置 S.Top = yy * 136 S.Left = xx * 274 S.Height = 132 S.Width = 270 ActiveChart.PlotArea.Select '设置绘图区大小及相对于外框的位置 Selection.Top = 9 Selection.Height = 132 Selection.Left = 0 Selection.Width = 270 xx = xx + 1 '设置计数器,让图表每三个排一行 If xx >= 3 Then xx = 0 yy = yy + 1 End If Next S Next r End Sub ================================== ActiveChart.ChartArea.Select Sub 改变图表尺寸() Set myDocument = ActiveSheet For Each S In myDocument.ChartObjects 'MsgBox (S.Name) S.Activate ActiveChart.ChartArea.Select '这部分是图表区的尺寸代码 S.Width = 200 S.Height = 200 ActiveChart.PlotArea.Select '这部分是绘图区的尺寸代码 Selection.Width = 191 Selection.Top = 9 Selection.Height = 185 Next S End Sub =================
清除图表可以用这个: Sub test() For Each r In Sheets("一班全图").ChartObjects r.Delete Next End Sub ========================= |
|