Solved: Problems with Excel charts in PPT 2007

[Cosmo]

This post is related to a thread I posted here: http://www.theofficeexperts.com/forum/showthread.php?t=10360

I'm having problems editing excel charts/graphs in PPT 2007; the charts don't retain the same size upon opening/closing. In some cases, the charts have been compressed so badly that they are ineligible.

I'm not sure if it's related to the fact that the charts were created in older versions of PPT (I get the message to convert them to 2007 when opening). The charts are updated in the field by my client's sales force via VBA in another presentation, so I need to make sure that the charts don't change when they edit them. I was going to try to record a macro while opening the charts to see if I could use code to convert the charts in the program, but found out that 2007 is unrecordable.:banghead:

I was thinking of checking the size of the graph before editing, and resetting it after editing, but I'm not sure if I can figure out the proper code to do this; does anyone have any suggestions on how I can keep the charts from changing, or restoring their attributes in my VBA code? Tis a massive amount of code in the presentation that does the updating, so I can't post the complete code, but all I'm doing to the charts is setting the data with this code:
With oShape
If TypeName(.OLEFormat.Object) = "Chart" Then
.OLEFormat.Object.Application.datasheet.Range(mColumn & mRow).Value = thePresentationValue
ElseIf TypeName(.OLEFormat.Object) = "Workbook" Then
.OLEFormat.Object.Sheets("Sheet1").Range(mColumn & mRow).Value = thePresentationValue
End If
Next oShape
and opening and closing the charts with this line when all of the data has been set (I found that if the charts weren't opened, the data wouldn't be retained after the presentation was saved and closed)
oShape.OLEFormat.DoVerb Index:=1

[Cosmo]

Ok, I can successfully resize the PlotArea of a MS chart in PPT, but my attempts to find out how to do the same for Excel charts have been unsuccessful.

With oChart.OLEFormat.Object
If chartTypeName = "Chart" Then
.PlotArea.Top = .PlotArea.Top - increment
.PlotArea.Height = .PlotArea.Height + increment
.PlotArea.Left = .PlotArea.Left - increment
.PlotArea.Width = .PlotArea.Width + increment
ElseIf chartTypeName = "Workbook" Then
.???.PlotArea.Top = .???.PlotArea.Top - increment
.???.PlotArea.Height = .???.PlotArea.Height + increment
.???.PlotArea.Left = .???.PlotArea.Left - increment
.???.PlotArea.Width = .???.PlotArea.Width + increment
End If
End With

I have tried looking through the object model in Excel, and have tried the following to get to the PlotArea:
Debug.Print "Chart PlotArea Top: " & oChart.OLEFormat.Object.ChartObject.PlotArea.Top

But I don't know the Excel model, and I haven't been able to find exactly how to access it from PowerPoint.

Can anyone help me determine the path for the PlotArea of an excel chart? I believe I will be able to solve my problem if I can only find this small detail.

[Paul_Hossler]

If you don't need to edit the chart in PP, maybe you could save/copy the chart from Excel as a picture

Paul

[Cosmo]

If you don't need to edit the chart in PP, maybe you could save/copy the chart from Excel as a picture

Paul
The chart does need to be edited; the files are sent with empty data in the charts and tables to our client, and their sales people use another PPT file which has the VBA code to fill the data into the presentation for their specific region.

[Cosmo]

Ok, I finally got to get back on this, and I found how to access the PlotArea for an Excel chart in PPT.
oChart.OLEFormat.Object.charts(1).PlotArea
I could have sworn that I had tried that before, but apparently I must not have.:dunno

I have gotten closer to where I need to be; I can capture the specs for the PlotArea when I start the program and restore them afterwards, and, although it's still changing some of the charts a little, it's at least not destroying them. I'll mark this one solved, although if there are any better solutions, I'd still be happy to hear any suggestions.