G
Guest
Requirement:
I have MS Graph(Embedded) charts on Powerpoint slides which are linked to
the Source Excel sheet. I want the charts to be updated when the figures on
the Excel sheet gets changed. I have one Button on sheet e.g. "Update PPT
Links" and i have written code behind this button to update the PPT charts
data with the data from the source Excel sheet when there is change in excel
data. We want all the links to be updated by clicking on this button so that
it will not require user go manually to the powerpoint and updating the links
from there.
How i am linking the PowerPoint chart to Excel data.
=================================
Steps:
1) Clicking on Insert menu in Powerpoint and slelect Chart
2) Right click on inserted chart and click on Chart Object->Edit. It pops
in a data sheet which will contain the source data for the chart
3) Select the Data from the source sheet from the location e.g. C:\Test and
copy it (CTRL+C)
3) Select any cell from the Data sheet which opend from the step 2 above.
4) Click on Edit->Paste Links command.
5) This way it links my chart to the source Excel data.
Code beind the "Update PPT Links" Button
==========================
Sub UpdatePPTLinks()
On Error GoTo ErrHandler
Dim strFileName As String
Dim strFilePath As String
Dim dlgFile As FileDialog
Dim oPA As PowerPoint.Application
Dim oPPT As Presentation
Dim oSlide As PowerPoint.Slide
Dim oShape As PowerPoint.Shape
Dim oGraph As Object
Application.DisplayAlerts = False 'Turn off alerts
Application.EnableEvents = False 'Turn off events
Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
With dlgFile
.Filters.Add "PowerPoint", "*.ppt"
.AllowMultiSelect = False
.Title = "Choose PowerPoint presentation to update"
If .Show Then
'Instantiate the PowerPoint application
Set oPA = New PowerPoint.Application
'Open the PPT
Set oPPT = oPA.Presentations.Open(.SelectedItems(1), , , msoFalse)
Else
'User has canceled the file selection dialog box
Exit Sub
End If
End With
' Loop through each slide in the presentation.
For Each oSlide In oPPT.Slides
' Loop through all the shapes on the current slide.
For Each oShape In oSlide.Shapes
' Check whether the shape is an Excel linked OLE object.
If oShape.Type = msoEmbeddedOLEObject Then
' Check whether the OLE object is a Chart or a Sheet object.
If oShape.OLEFormat.progID = "Excel.Sheet.8 " Or
oShape.OLEFormat.progID = "MSGraph.Chart.8" Then
' Found a Chart; obtain object reference, and then update.
Set oGraph = oShape.OLEFormat.Object
oGraph.Application.Update
End If
End If
Next oShape
Next oSlide
'Save the changes
oPPT.Save
Proiblem
======
This is working Ok If i put the Excel source file into the location e.g.
C:\Test
and all the charts are getting updated with the figures from the excel sheet.
But if i change the location of the Excel source file e.g. D:\Test the PPT
Charts do not get updated with the Excel data.
As per my knowledge there are two option we can do that
1) Embedding the Chart
2) Linking the chart to Excel data
In option 2 i think we need to create the chart in Excel and then need to
link it to Powerpoint Chart. which i dont want in this case.
So i chose first option.
Can anybody suggest am i following the write approach? If yes then where is
the problem area.
Many Thanks
I have MS Graph(Embedded) charts on Powerpoint slides which are linked to
the Source Excel sheet. I want the charts to be updated when the figures on
the Excel sheet gets changed. I have one Button on sheet e.g. "Update PPT
Links" and i have written code behind this button to update the PPT charts
data with the data from the source Excel sheet when there is change in excel
data. We want all the links to be updated by clicking on this button so that
it will not require user go manually to the powerpoint and updating the links
from there.
How i am linking the PowerPoint chart to Excel data.
=================================
Steps:
1) Clicking on Insert menu in Powerpoint and slelect Chart
2) Right click on inserted chart and click on Chart Object->Edit. It pops
in a data sheet which will contain the source data for the chart
3) Select the Data from the source sheet from the location e.g. C:\Test and
copy it (CTRL+C)
3) Select any cell from the Data sheet which opend from the step 2 above.
4) Click on Edit->Paste Links command.
5) This way it links my chart to the source Excel data.
Code beind the "Update PPT Links" Button
==========================
Sub UpdatePPTLinks()
On Error GoTo ErrHandler
Dim strFileName As String
Dim strFilePath As String
Dim dlgFile As FileDialog
Dim oPA As PowerPoint.Application
Dim oPPT As Presentation
Dim oSlide As PowerPoint.Slide
Dim oShape As PowerPoint.Shape
Dim oGraph As Object
Application.DisplayAlerts = False 'Turn off alerts
Application.EnableEvents = False 'Turn off events
Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
With dlgFile
.Filters.Add "PowerPoint", "*.ppt"
.AllowMultiSelect = False
.Title = "Choose PowerPoint presentation to update"
If .Show Then
'Instantiate the PowerPoint application
Set oPA = New PowerPoint.Application
'Open the PPT
Set oPPT = oPA.Presentations.Open(.SelectedItems(1), , , msoFalse)
Else
'User has canceled the file selection dialog box
Exit Sub
End If
End With
' Loop through each slide in the presentation.
For Each oSlide In oPPT.Slides
' Loop through all the shapes on the current slide.
For Each oShape In oSlide.Shapes
' Check whether the shape is an Excel linked OLE object.
If oShape.Type = msoEmbeddedOLEObject Then
' Check whether the OLE object is a Chart or a Sheet object.
If oShape.OLEFormat.progID = "Excel.Sheet.8 " Or
oShape.OLEFormat.progID = "MSGraph.Chart.8" Then
' Found a Chart; obtain object reference, and then update.
Set oGraph = oShape.OLEFormat.Object
oGraph.Application.Update
End If
End If
Next oShape
Next oSlide
'Save the changes
oPPT.Save
Proiblem
======
This is working Ok If i put the Excel source file into the location e.g.
C:\Test
and all the charts are getting updated with the figures from the excel sheet.
But if i change the location of the Excel source file e.g. D:\Test the PPT
Charts do not get updated with the Excel data.
As per my knowledge there are two option we can do that
1) Embedding the Chart
2) Linking the chart to Excel data
In option 2 i think we need to create the chart in Excel and then need to
link it to Powerpoint Chart. which i dont want in this case.
So i chose first option.
Can anybody suggest am i following the write approach? If yes then where is
the problem area.
Many Thanks