skinnyl
asked on
Referencing Powerpoint graphs using VB in an Excel macro
Hey all!
I'm using the below in a macro to automatically port data from excel into powerpoint. The code lies in the excel workbook.
It works well but i have got to the stage where there are multiple graphs on one slide and i'm not sure how to reference them. at the moment the below just uses the slide reference (oPPTApp.ActivePresentatio
Question- is it possible to reference more than one graph using VB?
Cheers
Jack.
Sub UpdateGraph()
Dim oPPTApp As PowerPoint.Application
Dim oPPTShape As PowerPoint.Shape
Dim oPPTPres As PowerPoint.Presentation
Dim rngNewRange As Excel.Range
Dim oGraph As Object
'
' Set oPPTApp to PowerPoint by creating a new instance of PowerPoint.
' If PowerPoint is already open, you would instead use the GetObject
' method instead.
'
Set oPPTApp = CreateObject("PowerPoint.A
'
' Set PowerPoint to be Visible.
'
oPPTApp.Visible = msoTrue
'
' Open Presentation1.ppt from My Documents.
'
oPPTApp.Presentations.Open
'
' Set rngNewRange to the collection of cells in the active Excel
' workbook and active sheet.
'
Set rngNewRange = ActiveSheet.Range("E11:E17
'
' Select the range then copy it.
'
rngNewRange.Select
rngNewRange.Copy
'
' On slide one of Presentation1.ppt, loop through each shape.
'
With oPPTApp.ActivePresentation
For Each oPPTShape In .Shapes
'
' Check to see whether shape is an OLE object.
'
If oPPTShape.Type = msoEmbeddedOLEObject Then
'
' Check to see whether OLE object is a Graph 2000 object. The ProgID
' is case sensitive.
'
If oPPTShape.OLEFormat.ProgId
'
' Set oGraph to the Graph object on the slide.
'
Set oGraph = oPPTShape.OLEFormat.Object
'
' Paste the cell range into the upper leftmost cell of the graph
' datasheet. This position is designated "00" (two zeros). To designate
' a range to start in the second row, first column, you would use "01".
' Likewise first row, second column is "A0". This will also link the
' datasheet to the Excel Workbook cell range. If you do not want to
' link to the Workbook, just omit the word "True". The default
' choice for the Paste method is "False".
'
oGraph.Application.DataShe
End If
End If
Next oPPTShape
End With
End Sub
I'm using the below in a macro to automatically port data from excel into powerpoint. The code lies in the excel workbook.
It works well but i have got to the stage where there are multiple graphs on one slide and i'm not sure how to reference them. at the moment the below just uses the slide reference (oPPTApp.ActivePresentatio
Question- is it possible to reference more than one graph using VB?
Cheers
Jack.
Sub UpdateGraph()
Dim oPPTApp As PowerPoint.Application
Dim oPPTShape As PowerPoint.Shape
Dim oPPTPres As PowerPoint.Presentation
Dim rngNewRange As Excel.Range
Dim oGraph As Object
'
' Set oPPTApp to PowerPoint by creating a new instance of PowerPoint.
' If PowerPoint is already open, you would instead use the GetObject
' method instead.
'
Set oPPTApp = CreateObject("PowerPoint.A
'
' Set PowerPoint to be Visible.
'
oPPTApp.Visible = msoTrue
'
' Open Presentation1.ppt from My Documents.
'
oPPTApp.Presentations.Open
'
' Set rngNewRange to the collection of cells in the active Excel
' workbook and active sheet.
'
Set rngNewRange = ActiveSheet.Range("E11:E17
'
' Select the range then copy it.
'
rngNewRange.Select
rngNewRange.Copy
'
' On slide one of Presentation1.ppt, loop through each shape.
'
With oPPTApp.ActivePresentation
For Each oPPTShape In .Shapes
'
' Check to see whether shape is an OLE object.
'
If oPPTShape.Type = msoEmbeddedOLEObject Then
'
' Check to see whether OLE object is a Graph 2000 object. The ProgID
' is case sensitive.
'
If oPPTShape.OLEFormat.ProgId
'
' Set oGraph to the Graph object on the slide.
'
Set oGraph = oPPTShape.OLEFormat.Object
'
' Paste the cell range into the upper leftmost cell of the graph
' datasheet. This position is designated "00" (two zeros). To designate
' a range to start in the second row, first column, you would use "01".
' Likewise first row, second column is "A0". This will also link the
' datasheet to the Excel Workbook cell range. If you do not want to
' link to the Workbook, just omit the word "True". The default
' choice for the Paste method is "False".
'
oGraph.Application.DataShe
End If
End If
Next oPPTShape
End With
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I really just played with it:
? ActivePresentation.Slides(
? ActivePresentation.Slides(
etc.
You can also record a macro in Powerpoint where you select a chart and change something. Reading the macro will show the current name of the shape, which is just as useful as the number.
Note: don't forget to delete the module when done, or record the macro in a copy of your presentation.
Your own code can be used as well. Examine the shapes in turn with something like:
? ActivePresentation.Slides(
At least you will know it's a chart, even if you don't know which one.
Good luck!
(°v°)
? ActivePresentation.Slides(
? ActivePresentation.Slides(
etc.
You can also record a macro in Powerpoint where you select a chart and change something. Reading the macro will show the current name of the shape, which is just as useful as the number.
Note: don't forget to delete the module when done, or record the macro in a copy of your presentation.
Your own code can be used as well. Examine the shapes in turn with something like:
? ActivePresentation.Slides(
At least you will know it's a chart, even if you don't know which one.
Good luck!
(°v°)
To use VB to name shapes:
http://www.tek-tips.com/faqs.cfm?fid=1698
And this one might help, it shows how to cycles through the shapes on a slide determining which is a graph object:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;qQ267974
http://www.tek-tips.com/faqs.cfm?fid=1698
And this one might help, it shows how to cycles through the shapes on a slide determining which is a graph object:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;qQ267974
ASKER
Sorry about the delay, got a bit sidetracked!
Cheers!
Cheers!
ASKER
Yes, I'm not trying to create any new charts, just update a 'template' if you will.
I see what your saying about the names, make good sense!
How to i discover the unique number of the shape (which i assume is a chart?) in order to assign it with a name? Also, i'm doing this from Excel in a macro. Powerpoint doesn't get a look in apart from being told what to do by Excel.
I understand that 'Shapes(4)' refers to the chart, just wonder where 4 comes from?
Thanks again
Jack.