使用excel中的图表和vba脚本（宏） [英] Using chart in excel with vba script(macros)

问题描述

我有一个生成报告的应用程序。这些报告是.xls文件模板，它从data.csv文件中获取数据。每当我在vb脚本中进行更改时。其中一个.xls工作簿的条形图（包含2张，工作表1（工作表1从csv获取数据）和工作表2（它包含数据的逻辑））现在我在脚本中添加了一个显示图表的函数第3页。



Sub Create_Bar_Chart（）

'使用此宏在Excel中创建条形图



ActiveSheet.Shapes.AddChart.Select

ActiveChart.SetSourceData来源：=范围（'Sheet3'！$ A $ 1：$ B $ 67）

ActiveChart.ChartType = xlBarClustered



End Sub

此脚本生成图表。



但是在第一张数据中添加...意味着作为模板的列从第1列移到第60列..第1列到第59列的值添加了新数据，但来自60列旧的数据也出现..并且背景颜色和模板也从60移动到以后。



问题是为什么第1页的值正在添加而不仅仅是更新g带有新值而不是换成新列。



我尝试过：



Sub Create_Bar_Chart（）

'使用此宏在Excel中创建条形图



ActiveSheet.Shapes.AddChart.Select

ActiveChart.SetSourceData来源：=范围（'Sheet3'！$ A $ 1：$ B $ 67）

ActiveChart.ChartType = xlBarClustered



结束子

这个脚本生成图表。



但是在第一张数据中添加了......表示作为模板的列从第1列移动到第60列..第1列到第59列的值添加了新数据，但是从60列旧数据也出现..并且背景颜色和模板也被移动从60开始等等。



问题是为什么表1的值正在添加而不是仅用新值更新而是转移到新列。

解决方案

A

1

乙

I have an application that generates reports. These reports are .xls file template that get data from data.csv file.Whenever I am making changes in the vb script. for bar chart of one of the .xls workbook(that contains 2 sheets, sheet 1(sheet 1 fetches data from csv) and sheet 2(It contains logic for the data) )Now Iam adding a function in the script that displays chart in sheet 3.

Sub Create_Bar_Chart()
'create a bar chart in excel with this macro

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet3'!$A$1:$B$67")
ActiveChart.ChartType = xlBarClustered

End Sub
This script is generating chart.

But In sheet one data gets added... means the columns that were taken as template are moved from column 1 to column 60 .. and column 1 to column 59 values are added with new data,but from 60 columns old data also present ..and the backgroud colors and template is also moved from 60 to so onward.

Question is that why sheet 1 values are adding instead of just updating with new values rather shifting to new columns.

What I have tried:

Sub Create_Bar_Chart()
'create a bar chart in excel with this macro

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet3'!$A$1:$B$67")
ActiveChart.ChartType = xlBarClustered

End Sub
This script is generating chart.

But In sheet one data gets added... means the columns that were taken as template are moved from column 1 to column 60 .. and column 1 to column 59 values are added with new data,but from 60 columns old data also present ..and the backgroud colors and template is also moved from 60 to so onward.

Question is that why sheet 1 values are adding instead of just updating with new values rather shifting to new columns.

解决方案

A

1:

B

这篇关于使用excel中的图表和vba脚本（宏）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！