Extracting data from Powerpoint datasheet/MSgraph to Excel

  • Thread starter Thread starter mcjamyhamy
  • Start date Start date
M

mcjamyhamy

Hi,

I'm trying to use VBA to extract underlying data from charts in
powerpoint and paste it into excel, i.e from the underlying powerpoint
datasheet that feeds the chart.

I've found the macro below on the net which copies the data in each
chart/datasheet and slide in powerpoint. When you run the macro in
powerpoint and then go into excel and click on paste manually it works
(at least for the first datasheet it's copying).

Sub GetChartData1() 'copies data from sheet
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide

'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy

End If
End If
Next s
Next sl

End Sub

I've tried to adapt the macro so that it automatically pastes the data
into excel but this does not seem to work.

Hope someone can help.

Thanks,

Lucas


Sub GetChartData2() ' Adpated to paste data into excel
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide

'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy

'***** Extra code to automate pasting into excel but doesn't
work******

Workbooks("test.xls").Sheets("sheet1").Activate
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End If
End If
Next s
Next sl

End Sub
 
See comments within your code example (and thanks for posting both it
and the code you adapted it from).

When you reply, mention which version of PPT and Excel you're using.

[snipsnip]




Sub GetChartData1() 'copies data from sheet
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide
Click to expand...
'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy
Click to expand...
End If
End If
Next s
Next sl
Click to expand...
End Sub
Click to expand...
I've tried to adapt the macro so that it automatically pastes the data
into excel but this does not seem to work.
Click to expand...
Hope someone can help.


Sub GetChartData2() ' Adpated to paste data into excel
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide
Click to expand...
'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy
Click to expand...
'***** Extra code to automate pasting into excel but doesn't
work******
Click to expand...

Assuming this all runs from within PowerPoint,you can't just start
issuing Excel commands.  You need to automate Excel to open your file
and so on.  Have a look at the following pages for examples of how to
automate Excel from PPT:

Controlling Office Applications from PowerPoint (by Naresh Nichani and
Brian Reilly)http://www.pptfaq.com/FAQ00795.htm

Automate Excel from PowerPoint. Automate PowerPoint from Excel. And so
on.http://www.pptfaq.com/FAQ00368.htm

And your next assignment <g>:

Modify the code accordingly, let us know how it works out.
If you're still having trouble with it, include the code and indicate
which line(s) you get erors on and the exact text of the error message.
Workbooks("test.xls").Sheets("sheet1").Activate
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Click to expand...
End If
End If
Next s
Next sl
Click to expand...
End Sub
Click to expand...

==============================
PPT Frequently Asked Questionshttp://www.pptfaq.com/

PPTools add-ins for PowerPointhttp://www.pptools.com/- Hide quoted text -

- Show quoted text -
Click to expand...

Hi,

Thanks for the links. I've adapted the code as follows but it is still
not pasting into Excel. When you run thde code from powerpoint, it
takes you to the excel but does not paste anything. In powerpoint I
get the message "Object doesn't Support this property or method"

In terms of versons, I'm using Excel 2003 and Powerpoint 2003.

Thanks,

Lucas

Sub test1()

'Sub GetChartData2()
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide

'Check if an instance of Excel is running. If so obtain a
reference to the running Excel application
'Otherwise Create a new instance of Excel and assign the XL
application reference to oXLApp object
On Error Resume Next
Err.Number = 0
Set oXLApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Number = 0
Set oXLApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
MsgBox "Unable to start Excel.", vbInformation, "Start
Excel"
Exit Sub
End If
End If

On Error GoTo Err_Handler

'To Make Excel App visible
oXLApp.Visible = True

'To Open a New XL Workbook
If oWb Is Nothing Then
Set oWb = oXLApp.Workbooks.Add
End If

'Include necessary code here by preceding oXLApp to refer to the
Excel object
'and oWb to refer to the Excel Workbook object

'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy

oXLApp.Workbooks("test.xls").Sheets("sheet1").Activate
owb.Range("B1").Paste

End If
End If
Next s
Next sl

Exit Sub

Err_Handler:
MsgBox Err.Description, vbInformation, "Start Excel"
End Sub
 
See comments within your code example (and thanks for posting both it
and the code you adapted it from).
Click to expand...
When you reply, mention which version of PPT and Excel you're using.
[snipsnip]
Click to expand...
Sub GetChartData1() 'copies data from sheet
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide
'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy
End If
End If
Next s
Next sl
End Sub
I've tried to adapt the macro so that it automatically pastes the data
into excel but this does not seem to work.
Hope someone can help.
Thanks,
Lucas
Sub GetChartData2() ' Adpated to paste data into excel
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide
'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy
'***** Extra code to automate pasting into excel but doesn't
work******
Click to expand...
Click to expand...
Assuming this all runs from within PowerPoint,you can't just start
issuing Excel commands.  You need to automate Excel to open your file
and so on.  Have a look at the following pages for examples of how to
automate Excel from PPT:
Click to expand...
Controlling Office Applications from PowerPoint (by Naresh Nichani and
Brian Reilly)http://www.pptfaq.com/FAQ00795.htm
Click to expand...
Automate Excel from PowerPoint. Automate PowerPoint from Excel. And so
on.http://www.pptfaq.com/FAQ00368.htm
Click to expand...
And your next assignment <g>:
Click to expand...
Modify the code accordingly, let us know how it works out.
If you're still having trouble with it, include the code and indicate
which line(s) you get erors on and the exact text of the error message.
Click to expand...
==============================
PPT Frequently Asked Questionshttp://www.pptfaq.com/
Click to expand...
PPTools add-ins for PowerPointhttp://www.pptools.com/-Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Hi,

Thanks for the links. I've adapted the code as follows but it is still
not pasting into Excel. When you run thde code from powerpoint, it
takes you to the excel but does not paste anything. In powerpoint I
get  the message "Object doesn't Support this property or method"

In terms of versons, I'm using Excel 2003 and Powerpoint 2003.

Thanks,

Lucas

Sub test1()

     'Sub GetChartData2()
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide

    'Check if an instance of Excel is running. If so obtain a
reference to the running Excel application
    'Otherwise Create a new instance of Excel and assign the XL
application reference to oXLApp object
    On Error Resume Next
    Err.Number = 0
    Set oXLApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Err.Number = 0
        Set oXLApp = CreateObject("Excel.Application")
        If Err.Number <> 0 Then
            MsgBox "Unable to start Excel.", vbInformation, "Start
Excel"
            Exit Sub
        End If
    End If

    On Error GoTo Err_Handler

    'To Make Excel App visible
     oXLApp.Visible = True

    'To Open a New XL Workbook
    If oWb Is Nothing Then
        Set oWb = oXLApp.Workbooks.Add
    End If

    'Include necessary code here by preceding oXLApp to refer to the
Excel object
    'and oWb to refer to the Excel Workbook object

'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy

oXLApp.Workbooks("test.xls").Sheets("sheet1").Activate
owb.Range("B1").Paste

End If
End If
Next s
Next sl

     Exit Sub

Err_Handler:
    MsgBox Err.Description, vbInformation, "Start Excel"
End Sub- Hide quoted text -

Hi Steve,
Click to expand...

Thanks for the message. The macro is now pasting into excel. But I
have a major problem, It's always pasting into C1 and so data from
each datasheet/slide is being over written in excel as the macro loops
fthrough all the slides and charts.

Would it be easy to adapt the code so that: 1) It pastes each chart's
data in consequtive rows, 2) And there is a way to indentify from
which chart and or slide the data is extracted from. So I'm thinking
if I change the paste range to start column C, In column A, would it
be possible to have the slide number from which the data is taken and
in column B the chart/datasheet number?

I think for the first part, I could probably come up with something by
making the paste range dynamic so that it goes to the next blank row
before it pastes, however for the second point I have absoultely no
idea how I could do it.

Hope you can help,

Thanks,

Lucas
 
[email protected] said:
See comments within your code example (and thanks for posting both it
and the code you adapted it from).
When you reply, mention which version of PPT and Excel you're using..
[snipsnip]
Sub GetChartData1() 'copies data from sheet
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide
'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy
End If
End If
Next s
Next sl
End Sub
I've tried to adapt the macro so that it automatically pastes thedat a
into excel but this does not seem to work.
Hope someone can help.
Thanks,
Lucas
Sub GetChartData2() ' Adpated to paste data into excel
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide
'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy
'***** Extra code to automate pasting into excel but doesn't
work******
Assuming this all runs from within PowerPoint,you can't just start
issuing Excel commands.  You need to automate Excel to open your file
and so on.  Have a look at the following pages for examples of how to
automate Excel from PPT:
Controlling Office Applications from PowerPoint (by Naresh Nichani and
Brian Reilly)http://www.pptfaq.com/FAQ00795.htm
Automate Excel from PowerPoint. Automate PowerPoint from Excel. Andso
on.http://www.pptfaq.com/FAQ00368.htm
And your next assignment <g>:
Modify the code accordingly, let us know how it works out.
If you're still having trouble with it, include the code and indicate
which line(s) you get erors on and the exact text of the error message.
Workbooks("test.xls").Sheets("sheet1").Activate
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks  
:=False, Transpose:=False
End If
End If
Next s
Next sl
End Sub
======================= =======
PPT Frequently Asked Questionshttp://www.pptfaq.com/
PPTools add-ins for PowerPointhttp://www.pptools.com/-Hidequoted text -
Click to expand...
- Show quoted text -
Hi,
Thanks for the links. I've adapted the code as follows but it is still
not pasting into Excel. When you run thde code from powerpoint, it
takes you to the excel but does not paste anything. In powerpoint I
get  the message "Object doesn't Support this property or method"
In terms of versons, I'm using Excel 2003 and Powerpoint 2003.
Thanks,
Lucas
Sub test1()
     'Sub GetChartData2()
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide
    'Check if an instance of Excel is running. If so obtain a
reference to the running Excel application
    'Otherwise Create a new instance of Excel and assign the XL
application reference to oXLApp object
    On Error Resume Next
    Err.Number = 0
    Set oXLApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Err.Number = 0
        Set oXLApp = CreateObject("Excel.Application")
        If Err.Number <> 0 Then
            MsgBox "Unable to start Excel.", vbInformation, " Start
Excel"
            Exit Sub
        End If
    End If
    On Error GoTo Err_Handler
    'To Make Excel App visible
     oXLApp.Visible = True
    'To Open a New XL Workbook
    If oWb Is Nothing Then
        Set oWb = oXLApp.Workbooks.Add
    End If
    'Include necessary code here by preceding oXLApp to refer to the
Excel object
    'and oWb to refer to the Excel Workbook object
'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy
oXLApp.Workbooks("test.xls").Sheets("sheet1").Activate
owb.Range("B1").Paste
End If
End If
Next s
Next sl
     Exit Sub
Err_Handler:
    MsgBox Err.Description, vbInformation, "Start Excel"
End Sub- Hide quoted text -
Hi Steve,
Click to expand...
Click to expand...
Thanks for the message. The macro is now pasting into excel. But I
have a major problem, It's always pasting into C1 and so data from
each datasheet/slide is being over written in excel as the macro loops
fthrough all the slides and charts.
Click to expand...

C1?  Not B1?  

You could probably set up a loop counter variable, let's call it lCount and
instead of this:

owb.Range("B1").Paste

do

owb.Range("B" & cstr(lCount)).Paste
And there is a way to indentify from
which chart and or slide the data is extracted from. So I'm thinking
if I change the paste range to start column C, In column A, would it
be possible to have the slide number from which the data is taken and
in column B the chart/datasheet number?
Click to expand...

For the first, something like this, I think (fair warning, I'm wandering way
past the boundaries of my ClueZone <g>)

owb.Range("A" & cstr(lcount)).FormulaR1C1 = cstr(sl.slideindex)

Not sure what you mean by the chart/datasheet number for the second.
I think for the first part, I could probably come up with something by
making the paste range dynamic so that it goes to the next blank row
before it pastes, however for the second point I have absoultely no
idea how I could do it.
Click to expand...
Hope you can help,

Lucas
Click to expand...

==============================
PPT Frequently Asked Questionshttp://www.pptfaq.com/

PPTools add-ins for PowerPointhttp://www.pptools.com/- Hide quoted text -

- Show quoted text -
Click to expand...

Hello again,

Regarding the first point - pasting the data in consequtive rows:

In the Excel part of the code I've tried runing this code but it does
not paste anything saying "Object does not support this property or
method" in powerpoint:

oWB.Activate
oWB.Range("C" & CStr(lCount)).Paste

Have I got the code and format right or am I missing something?

I also tried to paste using the following code but this does not work
either
oWB.Activate
oWB.activesheet.Range("c2") = "label"
oWB.activesheet.Range("c1").End(xlDown).Offset(1,
0).Select
oXLApp.activesheet.Paste

Regarding the second point, I have not tested this section out yet. In
terms of chrat numbers, in some slides I have several charts/
datasheets, so it would be useful to know what chart the data comes
from in given slide. Unfortunatley, there are no chart titles in the
datasheet, so I was just wondering whether the charts could be
indentified using a chart/object number. But if this is not possible,
that's fine.

Thanks,

Lucas
--------------------------------------------
 
[email protected] said:
<[email protected]>,
(e-mail address removed) wrote:
On Jan 20, 4:29 pm, (e-mail address removed) wrote:
See comments within your code example (and thanks for posting both it
and the code you adapted it from).
When you reply, mention which version of PPT and Excel you're using ..
Click to expand...
[snipsnip]
Sub GetChartData1() 'copies data from sheet
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide
'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy
End If
End If
Next s
Next sl
End Sub
I've tried to adapt the macro so that it automatically pastesthe  dat
a
into excel but this does not seem to work.
Hope someone can help.
Thanks,
Lucas
Sub GetChartData2() ' Adpated to paste data into excel
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide
'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy
'***** Extra code to automate pasting into excel but doesn't
work******
Assuming this all runs from within PowerPoint,you can't just start
issuing Excel commands.  You need to automate Excel to open your file
and so on.  Have a look at the following pages for examples of ho w to
automate Excel from PPT:
Controlling Office Applications from PowerPoint (by Naresh Nichani and
Brian Reilly)http://www.pptfaq.com/FAQ00795.htm
Automate Excel from PowerPoint. Automate PowerPoint from Excel.And  so
on.http://www.pptfaq.com/FAQ00368.htm
And your next assignment <g>:
Modify the code accordingly, let us know how it works out.
If you're still having trouble with it, include the code and indica te
which line(s) you get erors on and the exact text of the error mess age.
Click to expand...
Workbooks("test.xls").Sheets("sheet1").Activate
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNon e,
SkipBlanks  
:=False, Transpose:=False
End If
End If
Next s
Next sl
End Sub
====================== =
=======
PPT Frequently Asked Questionshttp://www.pptfaq.com/
PPTools add-ins for PowerPointhttp://www.pptools.com/-Hidequotedte xt
-
- Show quoted text -
Hi,
Thanks for the links. I've adapted the code as follows but it is stil l
not pasting into Excel. When you run thde code from powerpoint, it
takes you to the excel but does not paste anything. In powerpointI
get  the message "Object doesn't Support this property or method"
In terms of versons, I'm using Excel 2003 and Powerpoint 2003.
Thanks,
Lucas
Sub test1()
     'Sub GetChartData2()
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide
    'Check if an instance of Excel is running. If so obtain a
reference to the running Excel application
    'Otherwise Create a new instance of Excel and assign the XL
application reference to oXLApp object
    On Error Resume Next
    Err.Number = 0
    Set oXLApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Err.Number = 0
        Set oXLApp = CreateObject("Excel.Application")
        If Err.Number <> 0 Then
            MsgBox "Unable to start Excel.", vbInformatio n, "
Start
Excel"
            Exit Sub
        End If
    End If
    On Error GoTo Err_Handler
    'To Make Excel App visible
     oXLApp.Visible = True
    'To Open a New XL Workbook
    If oWb Is Nothing Then
        Set oWb = oXLApp.Workbooks.Add
    End If
    'Include necessary code here by preceding oXLApp to referto the
Excel object
    'and oWb to refer to the Excel Workbook object
'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy
oXLApp.Workbooks("test.xls").Sheets("sheet1").Activate
owb.Range("B1").Paste
End If
End If
Next s
Next sl
     Exit Sub
Err_Handler:
    MsgBox Err.Description, vbInformation, "Start Excel"
End Sub- Hide quoted text -
Hi Steve,
Thanks for the message. The macro is now pasting into excel. But I
have a major problem, It's always pasting into C1 and so data from
each datasheet/slide is being over written in excel as the macro loops
fthrough all the slides and charts.
C1?  Not B1?  
You could probably set up a loop counter variable, let's call it lCount a nd
instead of this:
owb.Range("B1").Paste
do
owb.Range("B" & cstr(lCount)).Paste
And there is a way to indentify from
which chart and or slide the data is extracted from. So I'm thinking
if I change the paste range to start column C, In column A, would it
be possible to have the slide number from which the data is taken and
in column B the chart/datasheet number?
For the first, something like this, I think (fair warning, I'm wandering way
past the boundaries of my ClueZone <g>)
owb.Range("A" & cstr(lcount)).FormulaR1C1 = cstr(sl.slideindex)
Not sure what you mean by the chart/datasheet number for the second.
I think for the first part, I could probably come up with somethingby
making the paste range dynamic so that it goes to the next blank row
before it pastes, however for the second point I have absoultely no
idea how I could do it.
Hope you can help,
Thanks,
Lucas
======================== ======
PPT Frequently Asked Questionshttp://www.pptfaq.com/
PPTools add-ins for PowerPointhttp://www.pptools.com/-Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Hello again,
Click to expand...
Regarding the first point - pasting the data in consequtive rows:
Click to expand...
In the Excel part of the code I've tried runing this code but it does
not paste anything saying "Object does not support this property or
method" in powerpoint:
Click to expand...
                    oWB.Activate
                  oWB.Range("C" & CStr(lCount)).Paste
Click to expand...
Have I got the code and format right
Click to expand...

No, I don't think so.  See above, where it does this before the paste:

oXLApp.Workbooks("test.xls").Sheets("sheet1").Activate
Regarding the second point, I have not tested this section out yet. In
terms of chrat numbers, in some slides I have several charts/
datasheets, so it would be useful to know what chart the data comes
from in given slide.
Click to expand...

I'm still not clear on this.  You have a chart on a slide.  Do you want to
know where the data comes from?  

Or do you want to look at a given bit of data (in Excel?) and find out what
chart or charts it feeds to?
Lucas
--------------------------------------------
Click to expand...

==============================
PPT Frequently Asked Questionshttp://www.pptfaq.com/

PPTools add-ins for PowerPointhttp://www.pptools.com/- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Steve,

Sorry I didn't understand what you were saying regarding the reference
to as I've not actually needed to using that line in the code as when
you run the macro, a new excel workbook is opened automatically to
which it pastes the data to.

oXLApp.Workbooks("test.xls").Sheets("sheet1").Activate .

Below is the entire copy and paste section of the code. The old code
used the following VBA, this worked, i.e the data was pasted but it
was being overwritten.

oWB.Activate
oWB.activesheet.Range("C1").Select
oXLApp.activesheet.Paste

The following new new code does not work.
Does not work
oWB.Activate
oWB.Range("c" & CStr(lCount)).Paste



For Each s In sl.Shapes 'does each chart on slide
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're
using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy

'Excel Paste bit

'works
'oWB.Activate
'oWB.activesheet.Range("C1").Select
'oXLApp.activesheet.Paste

'Does not work
oWB.Activate
oWB.Range("c" & CStr(lCount)).Paste

' oWB.activesheet.Range("c2") = "label"
' oWB.activesheet.Range("c1").End(xlDown).Offset(1,
0).Select
' oXLApp.activesheet.Paste

End If
End If
Next s
Next sl
Exit Sub


In terms of the chart number question. I have several charts on one
slide with seprate data sheets as sources. I therefore need to know
which datasheet it chart comes from. But from my perspective, it would
be easier to indentify the source of the data in terns of chart if I
now which chart the data is coming from. So for example, assume slide
3 as 6 charts on it, 3 arranged at the top and three at the bottom.
The headings for each chart is based on a text box in powerpoint. So
just pulling the data will not tell me to which each dataset belongs
to. So instead in the above case, depending on how charts get numbered
in excel it would be useful to have some kind of identifer or chart
number next to the data. So for example, if the data comes from the
top third chart, it would say number 3, if it comes from the first
bottom chart it would say number 4. However this is all academic as I
don't know how Powerpoint treats multiple charts on the same slide in
terms of numbering. I hope this makes it at least a little bit clearer
in terms of what I'm asking of whether it cone be done.

Thanks,

LUcas
 
I'm a little puzzled.  You say it's not doing what you want it to do when you
don't use the code I suggested but you say that you don't NEED that line of
code.  How exactly did you determine that?

Back when I first started working on cars, my dad taught me to make sure
there were no pieces left over when I put it all back together.  If I didn't
know what a part did ... well, that didn't mean that I didn't need it.  ;-)

I can't really help you solve the problem if you're gonna toss out some of
the parts.


















==============================
PPT Frequently Asked Questionshttp://www.pptfaq.com/

PPTools add-ins for PowerPointhttp://www.pptools.com/- Hide quoted text -

- Show quoted text -
Click to expand...
---------------------------------------------------
Hello Steve,

Sorry for any confusion. I really do appreciate your much needed help
and I wasn't seeking to ignore your instructions. I just thought that
that line of code (oXLApp.Workbooks("test.xls").Sheets
("sheet1").Activate) wasn't needed simply because even when it was
included, the macro ignored that particular workbook, opened a new one
up and pasted the data in a new workbook.

Anyway, I have put that line back in but it makes no difference, still
getting the message: "Object doesbn't support this property or
method". The total code I am using is below.

Also in relation to the the chart numbering question, I have found
that when I go into each chart, powerpoint refers to it as an object
as follows:

.SlideRange.Shapes("Object 67").Select 'First Chart
..SlideRange.Shapes("Object 68").Select 'Second chart

So I guess, listing the object number will help me identify the chart
from which the data is extracted.

Lucas

-------------
Sub latest()

Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide
' As a rule, ALWAYS dimension your variables
Dim oXLApp As Object
Dim oWB As Object

'Check if an instance of Excel is running. If so obtain a
'reference to the running Excel application
'Otherwise Create a new instance of Excel and assign the XL
'application reference to oXLApp object
On Error Resume Next
Err.Number = 0
Set oXLApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Number = 0
Set oXLApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
MsgBox "Unable to start Excel.", vbInformation, "Start
Excel """
Exit Sub
End If
End If
On Error GoTo Err_Handler

'To Make Excel App visible
oXLApp.Visible = True

'To Open a New XL Workbook
If oWB Is Nothing Then
Set oWB = oXLApp.Workbooks.Add
End If

'Include necessary code here by preceding oXLApp to refer to the
' Excel Object and oWb to refer to the Excel Workbook object

'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides 'does each slide
For Each s In sl.Shapes 'does each chart on slide
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're
using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy

' Here's your first problem:
' You haven't (necessarily) opened test.xls
oXLApp.Workbooks("test.xls").Sheets
("sheet1").Activate
oWB.Activate
' And some changes here, and it works:
'oWB.activesheet.Range("c1").Select 'orginal code
'oXLApp.activesheet.Paste 'orginal code

oWB.Range("C" & CStr(lCount)).Paste 'new line of
code in order to paste in different rows

End If
End If
Next s
Next sl


Exit Sub
Err_Handler:
MsgBox Err.Description, vbInformation, "Start Excel"
End Sub
 
OK, let's start clean.
Toss out whatever you've got and begin with this.
Follow the suggestions in comments as well.

Option Explicit
' Put Option Explicit at the top of each module
' Choose Tools, Options, put a check next to
' "Require variable declaration"
' and this will happen automatically from now on

Sub latest()

    Dim s As Shape 'gr As Graph.Chart
    Dim gr As Object
    Dim sl As Slide
    ' As a rule, ALWAYS dimension your variables
    Dim oXLApp As Object
    Dim oWB As Object   ' Workbook
    Dim oWS As Object   ' Worksheet
    Dim lGraphRowCount As Long
    Dim x As Long

    ' 28 Jan
    ' repeat:  ALWAYS dim your variables.  ALL of them.
    Dim lCount As Long
    lCount = 1 ' or whatever row you want to start pasting data into

    'Check if an instance of Excel is running. If so obtain a
    'reference to the running Excel application
    'Otherwise Create a new instance of Excel and assign the XL
    'application reference to oXLApp object
    On Error Resume Next
    Err.Number = 0
    Set oXLApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Err.Number = 0
        Set oXLApp = CreateObject("Excel.Application")
        If Err.Number <> 0 Then
            MsgBox "Unable to start Excel."
            Exit Sub
        End If
    End If
    On Error GoTo Err_Handler

    'To Make Excel App visible
     oXLApp.Visible = True

    ' 28 Jan
    ' you don't WANT to open a new work book,
    ' you want to open test.xls
    ' so change this
    If oWB Is Nothing Then
        Set oWB = oXLApp.Workbooks.Open("c:\temp\test.xls")
    End If

    'Copies data from datasheet in powerpoint
    For Each sl In ActivePresentation.Slides 'does each slide
        For Each s In sl.Shapes 'does each chart on slide
            If s.Type = msoEmbeddedOLEObject Then
            'we have found an OLE object
            'check if it's a graph
                If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
                    'this might vary depending on what version you're using
                    'now get a handle on the graph object itself
                    Set gr = s.OLEFormat.Object

                    ' Get a count of the number of rows in the graph
                    x = 1
                    While Len(gr.Application.datasheet.Cells(x, 2)) > 0
                        x = x + 1
                    Wend
                    lGraphRowCount = x - 1

                    gr.Application.datasheet.Cells.Copy

                    ' assuming there's a sheet named "sheet1" ...
                    Set oWS = oWB.sheets("Sheet1")
                    oWS.Activate
                    'lCount = 1
                    With oWS
                        .Range("C" & CStr(lCount)).Select
                        .Paste
                        ' offset range by number of cells pasted in
                        ' leave one row between groups
                        lCount = lCount + lGraphRowCount + 1
                    End With

                End If
            End If
        Next s
    Next sl

    ' Note: this leaves Excel open.
    ' You'll want to save and quit manually
    ' or add further code to do that here

Exit Sub
Err_Handler:
    MsgBox Err.Description, vbInformation, "Start Excel"
End Sub

==============================
PPT Frequently Asked Questionshttp://www.pptfaq.com/

PPTools add-ins for PowerPointhttp://www.pptools.com/
Click to expand...
----------------------------------------------------------------------------------------------------
Hi Steve,

Many thanks for sorting the code out. It works great now. Just on the
last couple of elements. In the code below I've included the following
line to determine which slide each data block is from and this works
fine:

'ADDITIONAL LINE
.Range("A" & CStr(lCount)).FormulaR1C1 = CStr
(sl.SlideIndex) 'extraline

I just now need to include a similar line of code to determine the
object chart (number) from which the data has come from. I want this
to be populated in Column B.

Would be great if you could suggest what that syntax should look like.


Thanks,

Lucas
------------
CODE
With oWS
.Range("C" & CStr(lCount)).Select
.Paste
' offset range by number of cells pasted in
' leave one row between groups
lCount = lCount + lGraphRowCount + 1
.Range("A" & CStr(lCount)).FormulaR1C1 = CStr
(sl.SlideIndex) 'extraline
End With
 
You've already worked out how to add code to pop text into any cell, so I
don't need to show you that, I think.  So:

Declare a new variable near the beginning of the subroutine:

Dim lShapeID as Long

Then above or below this line:

Set gr = s.OLEFormat.Object

add:

lShapeId = s.Id

Then use Cstr(lShapeID) as the text to insert into the worksheet whereveryou
like.





==============================
Click to expand...

Hi Steve,

I've done what you suggested but I'm slightly confused. Whilst the
macro is inserting a number in column B based on the codeline below it
is not the type of number I was expecting.

Range("B" & CStr(lCount)).FormulaR1C1 = CStr(lShapeID) 'extraline for
copying chart object number

For example, for the first chart it pastes the number "3075" in column
B for a chart but when you use the macro recorder to go into the same
chart in Powerpoint, it says it's Object 5:
ActiveWindow.Selection.SlideRange.Shapes("Object 5").Select

Have I made a mistake in referencing or is the shape ID something
different from the object ID?

The full code I'm using is below:

Thanks,

Lucas


Sub latest()

'Declare variables

Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide
' As a rule, ALWAYS dimension your variables
Dim oXLApp As Object
Dim oWB As Object ' Workbook
Dim oWS As Object ' Worksheet
Dim lGraphRowCount As Long
Dim x As Long
Dim lShapeID As Long

'How Chart objects are referenced
' ActiveWindow.Selection.SlideRange.Shapes("Object 3").Select




' 28 Jan
' repeat: ALWAYS dim your variables. ALL of them.
Dim lCount As Long
lCount = 2 ' or whatever row you want to start pasting data into


'Check if an instance of Excel is running. If so obtain a
'reference to the running Excel application
'Otherwise Create a new instance of Excel and assign the XL
'application reference to oXLApp object
On Error Resume Next
Err.Number = 0
Set oXLApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Number = 0
Set oXLApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
MsgBox "Unable to start Excel."
Exit Sub
End If
End If
On Error GoTo Err_Handler

'To Make Excel App visible
oXLApp.Visible = True


' 28 Jan
' you don't WANT to open a new work book,
' you want to open test.xls
' so change this
If oWB Is Nothing Then
Set oWB = oXLApp.Workbooks.Open("C:\Documents and Settings
\bulsarah\test.xls")

End If

'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides 'does each slide
For Each s In sl.Shapes 'does each chart on slide
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're
using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
lShapeID = s.Id 'Additional Code to pull chart/
object number

' Get a count of the number of rows in the graph
x = 1
While Len(gr.Application.datasheet.Cells(x, 2)) >
0
x = x + 1
Wend
lGraphRowCount = x - 1
gr.Application.datasheet.Cells.Copy


' assuming there's a sheet named "sheet1" ...
Set oWS = oWB.sheets("Sheet1")
oWS.Activate
'lCount = 1
With oWS
.Range("C" & CStr(lCount)).Select
.Paste
' offset range by number of cells pasted in
' leave one row between groups
lCount = lCount + lGraphRowCount + 1
.Range("A" & CStr(lCount)).FormulaR1C1 = CStr
(sl.SlideIndex) 'extraline for copying slide number
.Range("B" & CStr(lCount)).FormulaR1C1 = CStr
(lShapeID) 'extraline for copying chart object number


End With

End If
End If
Next s
Next sl


' Note: this leaves Excel open.
' You'll want to save and quit manually
' or add further code to do that here

Exit Sub
Err_Handler:
MsgBox Err.Description, vbInformation, "Start Excel"
End Sub
 
Sort of to the latter.  Each shape has a name and an ID.  

If you want the shape's name:

Create a new variable near the top:

Dim sShapeName as String

Instead of
lShapeId = s.Id
use
sShapeName = s.Name

and instead of
Range("B" & CStr(lCount)).FormulaR1C1 = CStr(lShapeID)
use
Range("B" & CStr(lCount)).FormulaR1C1 = sShapeName























==============================
PPT Frequently Asked Questionshttp://www.pptfaq.com/

PPTools add-ins for PowerPointhttp://www.pptools.com/- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Steve,

Just wanted to say thanks very much for your help and. I've got the
macro populating what I need now so I can now start using it.

Thanks,

Lucas
 
Back
Top