替换“来自单元格的价值”；使用VBA在图表数据标签中 [英] Replace "Value From Cells" in chart data labels using VBA

问题描述

我已经将几个图表从一个工作簿复制到另一个工作簿，并设法使用vba更改了数据系列。

I have copied several charts from a workbook to another and I managed to change the data series with vba.

这些图表的某些数据标签可以获取数据 来自单元格，但是此范围仍在引用第一个工作簿，因此我需要对其进行更改以引用新工作簿中的新工作表。

Some of the data labels of these charts, get data "From Cells" but this range is still referencing the the first workbook and I need to change it to reference the new sheet in the new workbook.

我能够获得引用来自单元格的公式。

I am being able to get the formula that references the "From Cells".

这是我拥有的代码

Sub xtDataLabels_FromCells()
    Dim oChart As ChartObject
    Dim OldString As String, NewString As String
    Dim mySrs As Variant

    OldString = "'[Daily Report]SP'!$P$11:$P$20"
    NewString = "SP!$P$11:$P$20"

    For Each oChart In ActiveSheet.ChartObjects
        For Each mySrs In oChart.Chart.SeriesCollection
            ' From a recorded macro
            'ActiveChart.SeriesCollection(7).DataLabels.Format.TextFrame2.TextRange. _
                InsertChartField msoChartFieldRange, "=SP!$P$11:$P$20", 0
        Next
    Next
End Sub

单元格的值

问候，
Elio Fernandes

Regards, Elio Fernandes

推荐答案

我不认为这正是您想要的。您的代码遍历所有图表的所有系列，并且显然希望对标签使用相同的范围。这样做：

I don't think this is exactly what you want. Your code loops through all series of all charts and apparently wants to use the same range for labels. This will do it:

Sub xtDataLabels_FromCells()
    Dim oChart As ChartObject
    Dim OldString As String, NewString As String
    Dim mySrs As Variant

    OldString = "'[Daily Report]SP'!$P$11:$P$20" ' irrelevant
    NewString = "SP!$P$11:$P$20"

    For Each oChart In ActiveSheet.ChartObjects
        For Each mySrs In oChart.Chart.SeriesCollection
            ' From a recorded macro
            mySrs.DataLabels.Format.TextFrame2.TextRange. _
                InsertChartField msoChartFieldRange, "=" & NewString, 0
        Next
    Next
End Sub

如果想要进行查找和查找替换现有的图表字段，恐怕您不走运。您可以使用VBA通过 InsertChartField 设置图表字段，但不能使用VBA 读取

If you want to do a Find & Replace of the existing chart field, I'm afraid you're out of luck. You can use VBA to set the chart field using InsertChartField, but you can't use VBA to read what is in an existing chart field.

Excel图表的VBA对象模型的另一个缺点。

Just another shortcoming of the VBA Object Model for Excel charts.

这篇关于替换“来自单元格的价值”；使用VBA在图表数据标签中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！