Results 1 to 11 of 11
  1. #1
    david_grimmer
    Guest

    macro to copy graphs form Excel to powerpoint (excel 2000)

    Hi, i was wondering if anyone can help. I have a number of excel workbooks, each with a lot of graphs in them, I want to copy and paste these graphs into a Powerpoint presentation, one graph per page. its is easy to do this manuly, but as there is a large number of them it would take quite a long time. I have tried to record a macro in excel to do this, but it dosen't work, only the portion of the commands in excel get recorded, the commands when i switch to powerpoint are lost

    can anyone help me

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: macro to copy graphs form Excel to powerpoint (excel 2000)

    Just a comment - instead of copying the graph to PPT why not copy the data and use PPT's charting function?
    I find this way I get better looking charts that use the colours of the presentation.

    Of course, if your charts are really complex ....
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro to copy graphs form Excel to powerpoint (excel 2000)

    I did it this way because it was so much easier to figure out, but you are correct: copying the data is a much better solution Have you done any PP charting in VBA? If so, post a snippit, and maybe we can integrate a solution. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: macro to copy graphs form Excel to powerpoint (excel 2000)

    Hah! I said it was a good idea - not that I've done it. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I offer the following code snippet from the help file.
    -------------------------
    FileImport Method Example

    This example imports data from the range A25 on the worksheet named "MySheet" in the Microsoft Excel workbook named "mynums.xls."
    <pre>With myChart.Application
    .FileImport FileName:="C:mynums.xls" _
    ImportRange:="A25" WorksheetName:="MySheet" _
    OverwriteCells:=False
    End With
    </pre>

    ------------------
    I was trying to integrate the above into making a chart using this:
    <pre> Set MyGraph = ActiveWindow.Selection.SlideRange.Shapes.AddOLEObj ect _
    (Left:=120#, Top:=110#, Width:=480#, Height:=320#, _
    ClassName:="MSGraph.Chart", link:=msoFalse)
    </pre>

    However, I could not call the data into the chart.

    I was able to make this work:
    <pre> ActiveWindow.Selection.SlideRange.Shapes.AddOLEObj ect(Left:=120#, _
    Top:=110#, Width:=480#, Height:=320#, _
    FileName:="d:tempdeleteme.xls", link:=True).Select
    </pre>

    It pulled the existing chart from the Excel file quite nicely. But that wouldn't be the solution I would want.
    Amazing how you can get the solutions you don't want to work <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>.

    Good night!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    david_grimmer
    Guest

    Re: macro to copy graphs form Excel to powerpoint (excel 2000)

    Thank to all for the prompt reply to my post, sams solution worked a treat after some small debugging.
    the macro stopped at the following line

    ppApp.Visible = True
    giving an error of "runtime error 91"

    on replacing the line with

    Set ppApp = GetObject(, "powerpoint.application")

    the macro ran with no problems as long as Powerpoint was open


    Thanks for your help in pointing me it the right direction, you saved me valuable time

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro to copy graphs form Excel to powerpoint (excel 2000)

    David, assuming that each of your charts are a worksheet, then this macro should get you started. If you have embedded charts, then we will have to add another loop, but try this first. Just copy the macro, paste it into Word, copy it again (gets the CR/LF's correct), and paste it into a macro module in your Excel workbook (or personal.xls). After pasting the code, you will need to use the Tools, References menu to add the Microsoft Powerpoint Object Library. This macro starts up PowerPoint itself, so you don't need to. HTH --Sam
    <pre>Option Explicit
    Sub Charts2Powerpoint()
    Dim ppApp As New PowerPoint.Application
    Dim ppPres As PowerPoint.Presentation
    Dim ppSlide As PowerPoint.Slide
    Dim ppRange As PowerPoint.ShapeRange
    Dim c As Excel.Chart
    ppApp.Visible = True
    Set ppPres = ppApp.Presentations.Add
    For Each c In ActiveWorkbook.Charts
    c.CopyPicture
    Set ppSlide = ppPres.Slides.Add(1, ppLayoutTitleOnly)
    Set ppRange = ppSlide.Shapes.Paste
    ppRange.Left = 20
    ppRange.Top = 60
    Next c
    Set ppRange = Nothing
    Set ppSlide = Nothing
    Set ppPres = Nothing
    Set ppApp = Nothing
    End Sub</pre>

    Edited to fix code: inserted NEW keyword in line three
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro to copy graphs form Excel to powerpoint (excel 2000)

    Thanks, David, for pointing out my mistake. ppApp should have been declared as NEW, so that it is created automatically when first used. Leave in "ppApp.Visible = True", otherwise you have an invisible copy of powerpoint lurking in your computer. I've edited the original post so that it is correct. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro to copy graphs form Excel to powerpoint (excel 2000)

    Well, Catharine, I think your code got me hooked, but already I'm <IMG SRC=http://www.mtl.com/sam/images/headthrob.gif> <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>
    Five questions:
    1) In the FileImport example, how is myChart dim'med?
    2) I think we discussed this before, but in my code, we only have a reference to a chart, but FileImport wants the cells with the original data, so how do we get that?
    3) Where do you find documation on FileImport and the OLE object associated with it?
    4) What OLE object were you using?
    5) How do you get help files for MSChart?

    Just answer whichever ones you can. Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro to copy graphs form Excel to powerpoint (excel 2000)

    Already made some progress: I added a reference to the MSGraph library. <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Now, I might make some progress.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: macro to copy graphs form Excel to powerpoint (excel 2000)

    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> - We need an evil code smilie!

    1. the sample didn't show the mychart being dimmed (this is where I find the OLH - not as helpful as one could wish). But surely it has to be declared somehow.
    2. Not clear on your question.
    3. I added the MSGraph reference - which you have done now. I used the Object Browser to call up the VBA help for same.
    4. Ummmm?
    5. you've got it.

    Don't tear too much <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>!

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro to copy graphs form Excel to powerpoint (excel 2000)

    Here's some excellent help via Shyam Pillai, MS Powerpoint MVP and VBA wizard:

    VBA: How to Paste New Excel Data into an Existing Graph
    <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/q267/9/74.asp>http://support.microsoft.com/support/kb/ar...s/q267/9/74.asp</A>

    Sample VBA Code to Create a Graph
    <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q176/4/43.asp>http://support.microsoft.com/support/kb/ar...s/Q176/4/43.asp</A>

    The second article uses a text file as source data, however the example is useful to understand how to manipulate the components of MS graph. You can
    expand it to pick up the data from Excel.

    Image Importer Wizard
    <A target="_blank" HREF=http://www.mvps.org/skp/iiw.htm>http://www.mvps.org/skp/iiw.htm</A>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •