Convert label for plot in powert point to excel table.

I have plot in powerpoint that was taken out from excel. The linked has be deleted however I still see the point values when I hover the mouse around the points. Is there anyway I can convert the points data to excel table?

Any help would be highly appreciated.

Thank you.
AiyshaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jamie GarrochSenior Technical Consultant at BrightCarbonCommented:
If it's an embedded Excel object on the slide, you should be able to right click the chart and choose Edit Data. That will open a basic Excel window with the chart data table.
AiyshaAuthor Commented:
"The linked file isn't available. This error can occur if the linked files has been removed or hasn't been saved. Embedding the data instead of linking it can help avoid this error., but the data won't be updated automatically if it changes in the source file."

Is there any hope of getting the tabular data?
Neil FlemingConsultant and developerCommented:
Yes. You can get it using a VBA macro. The code below accesses whatever chart you have selected and returns the values from the series number you enter, returning it as an array. You can also get the xValues this way

So a=hackChart(1) will return the data from the first series on the chart. You could run this macro (with some modification) directly from Excel and return the values into an Excel range. Let me know if you need help doing that.

Function hackChart(iSeries) As Variant()
Dim ch As Chart
Dim sh As Shape
Dim ss As Series

'set shape to selected object 
Set sh = ActiveWindow.Selection.ShapeRange(1)
If sh.HasChart = False Then
MsgBox ("This shape does not have a chart")
Exit Function
End If
Set ch = sh.Chart
Set ss = ch.SeriesCollection(iSeries)
hackChart = ss.Values

End Function

Open in new window


Hope this helps
Neil FlemingConsultant and developerCommented:
In fact, just to check this works, I just built a small Excel workbook. See attached.

If you enable macros and click the button "Get Powerpoint Chart Data" while powerpoint is open and a chart is selected, Excel will read the data from the selected chart and put it into the worksheet starting at column C6, which is a named range cell called "cChartStart". This works whether the original data is "disconnected" or not.

Here is the code from the Excel workbook, which has has a "reference" to Microsoft Powerpoint added. There are two routines:
  • the main "hackChart" routine, which gets the x axis values and name, followed by all the series in the chart, and puts them in Excel.
  • A short routine -  ConnectPPT - to check that Powerpoint is open

Option Explicit
Dim pptApp As PowerPoint.Application


Sub hackChart()
Dim ch As PowerPoint.Chart
Dim sh As PowerPoint.Shape
Dim ss As PowerPoint.Series
Dim ax As PowerPoint.Axis
Dim pSel As PowerPoint.Selection
Dim iCount As Long, iSeries As Long, iRow As Long
Dim rChart As Range, rSeries As Range
Dim aSeries() As Variant, xlSeries() As Variant
On Error GoTo errortrap

If connectPPT Then
'set shape to selected object, ro abort if no chart selected
Set sh = pptApp.ActiveWindow.Selection.ShapeRange(1)
    If sh.HasChart = False Then
    MsgBox ("Selected Powerpoint shape is not a chart")
    Exit Sub
    End If

'clear previous chart data
Set rChart = [cChartStart].CurrentRegion
rChart.ClearContents
Set rChart = rChart.Resize(1, 1)

'access the chart in the shape
Set ch = sh.Chart
'get the X-axis title
Set ax = ch.Axes(xlCategory)
    If ax.HasTitle Then
    rChart = ax.AxisTitle
    Else
    rChart = "X-Axis"
    End If

'get X axis values
Set ss = ch.SeriesCollection(1)
aSeries = ss.XValues
'Convert to Excel compatible array
ReDim xlSeries(1 To UBound(aSeries), 1 To 1)
For iRow = 1 To UBound(aSeries)
xlSeries(iRow, 1) = aSeries(iRow)
Next
Set rSeries = rChart.Offset(1, 0).Resize(UBound(aSeries), 1)
'put values in range "rSeries"
rSeries = xlSeries

'get each data series
For iSeries = 1 To ch.SeriesCollection.Count
'move one column to the right in Excel
Set rChart = rChart.Offset(0, 1)
Set ss = ch.SeriesCollection(iSeries)
'add series name
rChart = ss.Name
'get series values
aSeries = ss.Values
'Convert to Excel compatible array
ReDim xlSeries(1 To UBound(aSeries), 1 To 1)
For iRow = 1 To UBound(aSeries)
xlSeries(iRow, 1) = aSeries(iRow)
Next
Set rSeries = rChart.Offset(1, 0).Resize(UBound(aSeries), 1)
'put series values in excel range
rSeries = xlSeries


Next

End If
Exit Sub
errortrap:
MsgBox "An error has occurred. Please check Powerpoint is open and a chart selected."
End Sub


Function connectPPT() As Boolean

'see if PPT is open:
Set pptApp = GetObject(, "Powerpoint.Application")
'if not
If TypeName(pptApp) = "Nothing" Then
MsgBox ("Powerpoint must be running for this routine to work")
Exit Function
End If
connectPPT = True

End Function

Open in new window


Hope this helps.
GetPPTchartData.xlsm
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft PowerPoint

From novice to tech pro — start learning today.