使用Excel2010,我正在尝试使用几个不同表中的部分数据在Sheet1上创建图表。在下面的示例中,我只是尝试从一个表中提取数据作为开始。我似乎很难在此表中设置此数据的范围。
我最终将遍历每个表大约8个不同长度的数据列。21张桌子。所以任何自动化的帮助都是很棒的
这就是我目前所知道的..。
Sub createChart()
Dim rng As Range
Dim chrt As Object
'Table is named BMS_01_01_Module_01 and the column Name I want data from is Cell 1 Voltage
Set chrt = Sheet1.Shapes.AddChart2
chrt.ChartArea.SetSourceData Source:=activesheetlistobjects("BMS_01_01_Module_01").ListColumns("Cell 1 Voltage").Range
chrt.ChartArea.ChartType = x1Line
End Sub
发布于 2018-10-11 21:21:05
我最终对我的代码做了一些修改。我只是希望有一种方法可以引用该表,而不必首先激活该表所在的工作表。注意:图表和表格不在同一张纸上。
这就是最终在一个范围内有效的方法:
Sub createChart()
Dim rng As Range
Dim cht As Object
Dim Ws As Worksheet
Set cht = Sheet1.Shapes.addChart
Worksheets("BMS_01_Module_01").Activate
Set Ws = ActiveSheet
Ws.ListObjects("BMS_01_Module_01").ListColumns("Cell 1 Voltage").Range.Select
cht.Chart.SetSourceData Source:=Selection
cht.Chart.ChartType = xlLine
End Sub
发布于 2018-10-11 09:43:36
尝尝这个。
Sub createChart()
Dim rng As Range
Dim obj As ChartObject
Dim chrt As Chart
Dim Lst As ListObject
Dim cm As ListColumn
Dim Ws As Worksheet
Set Ws = ActiveSheet
Set Lst = Ws.ListObjects("BMS_01_01_Module_01")
Set cm = Lst.ListColumns("Cell 1 Voltage")
'Set cm = Lst.ListColumns(1)
Set rng = cm.DataBodyRange
'Table is named BMS_01_01_Module_01 and the column Name I want data from is Cell 1 Voltage
Set obj = Sheet1.ChartObjects.Add(200, 200, 400, 400)
With obj.Chart
'chrt.ChartArea.SetSourceData Source:=ActiveSheet.ListObjects("BMS_01_01_Module_01").ListColumns("Cell 1 Voltage").Range
.SetSourceData Source:=rng
.ChartType = xlLine
End With
End Sub
https://stackoverflow.com/questions/52748120
复制相似问题