在PowerPoint 2010中静默编辑嵌入的Excel数据

Question

我在尝试在PowerPoint 2010中静默编辑嵌入的Excel数据。不幸的是，当您使用以下命令时：

gChartData.Activate

它打开了演示文稿上的Excel文档。是否有一种方法可以在不打开Excel的情况下激活ChartData？

完整代码：

Private Sub CommandButton1_Click()

Dim myChart As Chart
Dim gChartData As ChartData
Dim gWorkBook As Excel.Workbook
Dim gWorkSheet As Excel.Worksheet

Set myChart = ActivePresentation.Slides(1).Shapes(1).Chart
Set gChartData = myChart.ChartData

gChartData.Activate

Set gWorkBook = gChartData.Workbook

Set gWorkSheet = gWorkBook.Worksheets(1)

gWorkSheet.Range("B2").Value = 1

Set gWorkSheet = Nothing
Set gWorkBook = Nothing
Set gChartData = Nothing
Set myChart = Nothing


End Sub

提前感谢您。

- Steven

---

你为什么需要使用 gChartData.Activate？ - DontFretBrett

Answer 1

Steven,

在您的代码中，Activate行是必要的以获取对基础工作簿的访问权限。但是添加一个简单的 gWorkBook.Close（在将其设置为Nothing之前）将关闭Excel而不是像您的当前代码那样使其保持在顶部。

Private Sub CommandButton1_Click()

    Dim myChart As Chart
    Dim myChartData As ChartData
    Dim gWorkBook As Excel.Workbook
    Dim gWorkSheet As Excel.Worksheet

    Set myChart = ActivePresentation.Slides(1).Shapes(1).Chart
    Set myChartData = myChart.ChartData
    myChartData.Activate

    Set gWorkBook = myChart.ChartData.Workbook
    Set gWorkSheet = gWorkBook.Worksheets(1)
    gWorkSheet.Range("B2").Value = 1

    gWorkBook.Close
    Set gWorkSheet = Nothing
    Set gWorkBook = Nothing
    Set gChartData = Nothing
    Set myChart = Nothing
End Sub

- brettdj

---

虽然需要使用“Activate”命令才能访问底层工作簿......我已经忍受了这个愚蠢的要求长达两年，我仍然无法相信他们要求这样做。 - David Zemens

@davidzemens同意。PowerPoint的逻辑有时候...简直是个矛盾修辞法。 - brettdj