Attempting to edit Excel chart data in PowerPoint

**DPWM** · 06-23-2013, 09:47 PM

Hi all. I'm trying to edit the data of a chart in a PowerPoint presentation that is linked to Excel. Here is a code I found that looks similar to what I want to do

Sub test ()

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

Set myChart = ActivePresentation.Slides(2).Shapes("Chart7").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

When I run it, it says "User-defined type not defined" though. Here's what I have:

This macro is in PowerPoint, not Excel.
The chart is on slide 2 of the PowerPoint presentation and is named "Chart7".
The Excel workbook is named Workbook1 and the first sheet on it that has the chart data is called Sheet1. It will already be open at the time that the macro is run.
The macro is designed to be run by clicking a button while the PowerPoint presentation is running.

What I ideally want to do is be able to edit the excel sheet in any way I want (setting B2 to 1 is just an example). If there's any way I can be more specific, please let me know. Any help would be greatly appreciated.

**Andy Pope** · 06-24-2013, 03:14 AM

Did you include a reference to the excel library within the powerpoint project?

In VBE use menu Tools > References.

**DPWM** · 06-24-2013, 09:12 AM

Good call! Thank you very much!

I've got it working ALMOST exactly how I want it now. There's one more thing I'd like it to do, but I'm not sure it's possible. First, here's the current code now:

Sub test()

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

Set myChart = ActivePresentation.Slides(2).Shapes("Chart7").Chart
Set myChartData = myChart.ChartData
myChartData.Activate

Set gWorkBook = myChart.ChartData.Workbook
Set gWorkSheet = gWorkBook.Worksheets(1)
gWorkSheet.Range("I36").Value = 0.01
gWorkSheet.Range("I37").Value = -0.2
gWorkSheet.Range("I36").Value = -0.2
gWorkSheet.Range("I36").Value = 0
gWorkSheet.Range("I36").Value = -0.05

gWorkBook.Save
gWorkBook.Close
Set gWorkSheet = Nothing
Set gWorkBook = Nothing
Set gChartData = Nothing
Set myChart = Nothing
ActivePresentation.Application.Activate
ActivePresentation.SlideShowSettings.Run

End Sub

So, now if I'm in the presentation and click the button the macro is assigned to, it'll open Excel, change some data, save it, close Excel, and resume the presentation. However, it does all of this in the foreground. Is there a way to make it so that the PowerPoint presentation is always the screen that people see when all of this is running? Basically, I don't want the audience to see that all of this opening, editing, and closing is going on behind the scenes.

Edit: I did find this thread which is similar: http://www.excelforum.com/excel-prog...oreground.html

and tried using this code above the sub

Private Declare Function SetParent Lib "user32" (ByVal hWndChild As Long, ByVal hWndNewParent As Long) As Long

and this code within the sub

PowerPoint.Application.Visible = True
SetParent PowerPoint.Application.Hwnd, Application.Hwnd

but the macro does nothing at all when I run it.

I've also tried variations of

ActivePresentation.Windows(1).Visible = True

which include using PowerPoint and Excel instead of ActivePresentation and making them true or false.

**Andy Pope** · 06-25-2013, 04:00 AM

I don't believe you can automate the PPT chart without it being activated.

Why not simply have 2 slides, instead of updating on the fly?

**DPWM** · 06-25-2013, 08:20 AM

Unfortunately, I'd need much more than 2 slides because I'd like to eventually have at least 6 scenarios (6 buttons) that the user can cycle through so splitting it up between slides won't work here.

Alternatively, instead of hiding Excel, is there a way to "freeze" PowerPoint as what's viewable or always keep it on top?

**DPWM** · 06-25-2013, 08:57 AM

Actually, sometimes the most simple solution is the best. I just realized that I can minimize make the size of the Excel window the smallest it can be and drag it to a corner before I start the PowerPoint Presentation. That way, it'll still flash, but it won't really be noticed.