Edit embedded excel data silently, in PowerPoint 2010
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.
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