Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Noy able to update the PowerPoint charts with the data from ExcelS
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 ----- VKS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why won't my pie charts update with changing data? | Charts and Charting in Excel | |||
Excel spreadsheets with data, Powerpoint charts... and VBA! | Excel Discussion (Misc queries) | |||
Charts don't update when data is changed, why not? | Charts and Charting in Excel | |||
Can 1 create a excel dahboard charts that pulls from other Excels | Charts and Charting in Excel | |||
Charts don't update when data changes | Charts and Charting in Excel |