Edit embedded excel data silently, in PowerPoint 2010

Question

I am trying to edit embedded excel data silently in PowerPoint 2010. Unfortunately when you use:

gChartData.Activate

It opens the Excel document over the presentation. Is there a way to activate the ChartData without opening Excel?

Full Code:

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

Thanks in advance.

Answer 1

Steven,

While the Activate line is necessary to get access to the underlying Workbook adding a simple gWorkBook.Close to your code (before setting it to Nothing) will close Excel again rather than leave it on top as your current code does.

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