Excel Output to a PPT in a Controllable Manner

I have a Workbook that was written by EE some time ago that allows for a graphic to be selected from a "source" and then I have a macro that was written by EE Professional broro183:- "Rob" that allows me to export a selected Graphic on the active Worksheet, to a Powerpoint slide.  If I change the graphic selection and export again, it creates another PPT.  This causes extra work.  Here's what I'm trying to do.  When "OUTPUT TO PPT" is selected, I want it to output "a range of "Images -- based on the Image names in a particular defined range" to the same PPT on different Charts (same PPT); or specific "Graphs" based on either the graphic name or the graphic location (within a particular cell).  I've attached the Worksheet so you can see the output and the code.  This is a little harder then I'd normally ask, however, I'm hoping an EE Professional will see the pragmatic use of such a macro/workbook.


Test-Case-for-EE-for-Exporting-I.xlsm
Bright01Asked:
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.

dlmilleCommented:
App modification as follows.  Created new sub from old, in Module 4.  Sub called ChartToPPT_vDLMILLE()

set source_ws to "Source" tab

changed logic to look for an active powerpoint presentation, and if none, then creates one on the fly

identified a range called rImages which looks at N2:N??? where ??? is the bottom most cell with content.  In your sample, that would be N2:N5.

The app loops through column N, from N2, testing each for an existing range name.  If that returns a valid range, then that range is copied.  Changed the .Shape.Paste to .Shape.PasteSpecial with enhanced metafile as the type of paste.  The title used on the destination slide leverages the text in column M of your image list, left of the range name.  I used the rest of the code as appropriate.

Here's the code:
   
 
Const l_ppPasteEnhancedMetafile = 3
Sub ChartToPPT_vDLMILLE()
'The "..._v3" version of the code copies pictures from excel whereas the "..._v2" version copies chart objects.
'NO LONGER NEED TO "Set a VBE reference to Microsoft PowerPoint Object Library"
'RB: changed to late binding by changing the powerpoint related declarations to "as object"  (& declaring constants too) to prevent the need for setting a Reference (ie the previous line is no longer necessary).
Const ppViewNormal As Long = 9
Const ppLayoutText As Long = 2
Dim PPApp As Object 'PowerPoint.Application
Dim PPPres As Object 'PowerPoint.Presentation
Dim PPSlide As Object 'PowerPoint.Slide
Dim PresentationFileName As String
Dim SlideCount As Long
    'RB: changed to "as long" because the computer will perform an internal conversion from integer to long, so we may as well save it the extra work.
Dim iCht As Long
Dim sTitle As String
Dim rImages As Range
Dim rImage As Range
Dim myPaste As Object

    'RB: added extra variables to help ensure the correct objects are acted on.
    'Note that I have used the "Excel." prefix in case the code is ported to another application with the intent of using "early binding".
Dim Source_ws As Excel.Worksheet    '"ws" = worksheet

    With ThisWorkbook
        'change these two lines as needed...
        Set Source_ws = .Worksheets("Source")
        PresentationFileName = .Path & Excel.Application.PathSeparator & "Graphics from excel (" & Format(Now, "yyyymmdd hhnnss") & ")"
    End With

    'check for an existing instance of PowerPoint & if one doesn't exist then open one.
    On Error Resume Next
    Set PPApp = GetObject(, "Powerpoint.Application")
    On Error GoTo 0
    If PPApp Is Nothing Then
        Set PPApp = CreateObject("Powerpoint.Application")
        PPApp.Visible = msoTrue
    End If

    ''RB: I have commented out these lines which show how you could refer to a ppt file that is already open.
    '' Reference active presentation
    'Set PPPres = PPApp.ActivePresentation
    '    'OR...
    'RB: this line opens a new presentation, or
    'DLM:  Check to see if any presentations exist.  Use the existing active presentation if so, or add one if no
    If PPApp.Presentations.Count = 0 Then
        Set PPPres = PPApp.Presentations.Add(msoTrue)
    Else
        Set PPPres = PPApp.ActivePresentation
    End If
    '    'OR...
    '    'RB: or this line opens an existing presentation (change the file name as needed)
    '        Set PPPres = PPApp.Presentations.Open(Filename:="C:\Users\Robert\Downloads\Output-Graphics.ppt")

    ''RB: "activewindow" removed (can be deleted) in case you move the ".visible = true" to the end of the macro
    '    PPApp.ActiveWindow.ViewType = ppViewNormal

    Set rImages = Source_ws.Range("N2", Source_ws.Range("N" & Source_ws.Rows.Count).End(xlUp))
    
    For Each rImage In rImages 'traverse range for image range names
    
        sTitle = rImage.Offset(0, -1).Text 'get name from image list
        On Error Resume Next
        ThisWorkbook.Names(rImage.Value).RefersToRange.Copy
        If Err.Number = 0 Then 'found a valid range from the range name
            
            ' Add a new slide and paste in the chart
            With PPPres.Slides
                SlideCount = .Count
                Set PPSlide = .Add(SlideCount + 1, ppLayoutText)
            End With
    
            ''RB: "activewindow" removed (can be deleted) in case you move the ".visible = true" to the end of the macro
            'PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex

            With PPSlide
                '' paste and select the chart picture
                '.Shapes.Paste.Select
                'RB: where possible avoid using ".select" as it slows code down, therefore I have changed the above to the below...
               Set myPaste = .Shapes.PasteSpecial(l_ppPasteEnhancedMetafile)
               
                With myPaste
                    
                    ' Align the pasted range
                    .Align msoAlignCenters, True
                    .Align msoAlignMiddles, True
    
                    ' Position pasted chart
                    .Left = 100
                    .Top = 160
                End With
    
                With .Shapes.Placeholders(1)
                    .TextFrame.TextRange.Text = sTitle
                    .Top = 90
                    .Height = 60
                End With
    
                '                ''RB: I don't think this is relevant so I have commented it out.
                '            ' Set and Place Comments Box
                '            With .Shapes("Rectangle 3")
                '                .ShapeRange.Height = 170
                '                .ShapeRange.Top = 320
                '               ''something else is needed here
                '               Application.ActiveSheet.Select
                '               'RB: commented out to allow the code to compile using [alt + D + L]
                '               'Select_Comments
                '                .TextRange.PasteSpecial ppPasteText
                '            End With
            End With
        End If
    Next rImage

    'PPApp.Visible = msoTrue
    'RB: if you want to save the ppt file you may be able to modify the next line of code...
    PPPres.SaveAs Filename:=PresentationFileName

    MsgBox "done", vbOKOnly + vbMsgBoxSetForeground, "MACRO HAS FINISHED!"
    ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
    Set Source_ws = Nothing
End Sub

Open in new window

I created a new button on the "Source" sheet for calling this macro.  As a result, you can create any images you want in the workbook, and as long as there's a range definition in the cells behind the shape, and that range name is also listed on the "Source" tab in column N (contiguously), then it should find that range, copy and paste the image sitting on that range on a new slide in the active (or newly created) Powerpoint application.

See attached.

Enjoy!

Dave
Test-Case-for-EE-r1.xlsm
0
Bright01Author Commented:
Dave,

Fantastic response as always!  I'm going to test it later today and will close out the question.  I do have a question for you.  The approach that I asked for and that you provided, allows for a Workbook to output graphics or charts to a new PPT.  Have you seen an approach where you can take a existing PPT where you have all the substance already completed and the graphics are imported (or exported from Excel) so that they actually fit a specific position within the PPT?  The use of this is that I already have a lot of PPT content and I need the graphics produced from an Excel Workbook to populate an existing PPT.  This is not meant to create any work for you, I'm just curious of your thoughts about how hard that is or if you've seen anything like it?  MS has "smarttags" and I've never used them but think that may be an option.

Again, much thanks for great work.

B.
0
dlmilleCommented:
I've recently done quite a bit of work (working on a new article) using Excel and bookmarks, and in one instance, you can create a shape in Word (a dummy shape) that is bookmarked, and the app fills the shape with a picture from Excel....

Following that same thread, you might be able to create dummy shapes (or an initial presentation of shapes where you've moved/sized stuff as appropriate) and then could find and replace or fill from that.

If I had to do it in a hurry, I would paste the shape as an OLE object into powerpoint, then when Excel refreshed, the PPT would, as well.  One problem I believe with this approach is the OLE object contains the entire Excel application object - so can get hefty, if I'm right in interpreting how this is done.

When I was working on the ExcelToWord! application, I looked at SmartArt, but shortly figured it wouldn't work for me, or I couldn't figure it out, anyway :)

Anyway, bookmarks in Powerpoint refer to slides (re: via Hyperlinks), and not particular positions (as with Word), so I'm currently at a loss as to how one might do that.  If you could, it would be a simple thing to go to a bookmark and replace a shape with another, I think.

Here's a possible start, but it looks a bit complex on first glance: http://stackoverflow.com/questions/6740484/powerpoint-2011-vba-image-replacement

Dave
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

dlmilleCommented:
PS - if you are indifferent as to your output engine (e.g., if the output could be in WORD just as easily - re: its not going to be presented, its going to be sent via email or printed, or something) then we can talk bookmarks and the new tool I'm working.  Let me know.
0
Bright01Author Commented:
Dave,

Thanks for the reply.  I have a challenge.  The sample I gave you only included images.  Some of my "images" are graphics are actually a combination of more then one chart that are the result of variables (as in a simple bar chart graphic and pie chart) and may also have overlay cell formatting.  Is there a simple way to reference the underlying cells and create an image instead of a graphic?  It would be like taking a picture of a chart in order to make it into a graphic.  If so, I think I could then use the code you've provided to name the new graphic.

Thank you,

B.
0
dlmilleCommented:
Actually, that's supposed to be the way it works.  Just ensure the named ranges are referencing the underlying cells and it should work.  That's the way I perceived the sample you uploaded to be.  If its not working, for some unbeknownst reason, just put what you were thinking of in the workbook and upload it so I can affect appropriate changes.

Dave
0
dlmilleCommented:
This should look familiar.  Some good pointers on Excel to other office applications, including Powerpoint:  http://peltiertech.com/Excel/XL_PPT.html

I read up a bit on tags, and looked promising, and could tag each shape with the range name from Excel, however couldn't determine how to get the shape object from the tag.  HOWEVER, you can name each shape in powerpoint, to the name from the shape being imported from Excel.  I think its just as good (or better, because you CAN enumerate through shapes to find a name - I guess you could do that with tags, too :) - and then return that shape object, which I did with the function:

Function findShape_Name(oPPTPres As Object, shapeName As String) As Object - or its sister function findShape_Tag() which does the same things, just using tags.

So when you run the macro, it searches to find the shape, and if it doesn't it creates a new slide with that shape.  If it DOES find that shape, the goal would be to paste/replace the image in exactly the same position as the prior slide....

One approach might be - See: http://www.pcreview.co.uk/forums/want-change-images-powerpoint-presentation-without-losing-animations-t915374.html - but seems tedious.

OR - we could get the parameters of the picture we're about to paste/replace, then delete the shape, paste the new shape, and apply the parameters...  This seems to work well.

Ok - try this.  Load the images - standard - to create a powerpoint.  THEN, in the Powerpoint presentation - reposition and resize the shapes EXACTLY as you want them (again, if you delete one, then Excel will add a new slide for it, as that's the default).   Go ahead and put a couple shapes on ONE slide and resize them, etc. THEN, in Excel, make changes to your shape ranges - put something else there.  Then rerun - it will find each shape and do a replace, as appropriate.

The driver here, is Excel, so it finds the first shape matching its name/tag in Powerpoint, so if you duplicate a picture in powerpoint, Excel will not see it.  The way I did ExcelToWord! (soon to be published) it was Word that was the driver (e.g., what bookmarks do I need to populate, then look at Excel for them.  This could be turned around to look for tags, then find the image in Excel matching the tag name.  Probably for another future question, yes?  Let's see if this works, first!

Here's the code:
 
Sub ChartToPPT_vDLMILLE()
Const ppViewNormal As Long = 9
Const ppLayoutText As Long = 2
Const ppPasteEnhancedMetafile = 3
Dim oPPTApp As Object 'PowerPoint.Application
Dim oPPTPres As Object 'PowerPoint.presentation
Dim oPPTSlide As Object 'PowerPoint.slide
Dim PresentationFileName As String
Dim SlideCount As Long
Dim iCht As Long
Dim sTitle As String
Dim rImages As Range
Dim rImage As Range
Dim myPaste As Object
Dim myShape As PowerPoint.Shape
Dim Source_ws As Worksheet
Dim shTop As Long
Dim shLeft As Long
Dim shHeight As Long
Dim shWidth As Long

    With ThisWorkbook
        'change these two lines as needed...
        Set Source_ws = .Worksheets("Source")
        PresentationFileName = .Path & Excel.Application.PathSeparator & "Graphics from excel (" & Format(Now, "yyyymmdd hhnnss") & ")"
    End With

    'check for an existing instance of PowerPoint & if one doesn't exist then open one.
    On Error Resume Next
    Set oPPTApp = GetObject(, "Powerpoint.Application")
    On Error GoTo 0
    If oPPTApp Is Nothing Then
        Set oPPTApp = CreateObject("Powerpoint.Application")
        oPPTApp.Visible = msoTrue
    End If

    'DLM:  Check to see if any presentations exist.  Use the existing active presentation if so, or add one if no
    If oPPTApp.Presentations.Count = 0 Then
        Set oPPTPres = oPPTApp.Presentations.Add(msoTrue)
    Else
        Set oPPTPres = oPPTApp.ActivePresentation
    End If

    Set rImages = Source_ws.Range("N2", Source_ws.Range("N" & Source_ws.Rows.Count).End(xlUp))
    
    For Each rImage In rImages 'traverse range for image range names
    
        sTitle = rImage.Offset(0, -1).Text 'get name from image list
        On Error Resume Next
        ThisWorkbook.Names(rImage.Value).RefersToRange.CopyPicture Appearance:=xlScreen, Format:=xlPicture

        If Err.Number = 0 Then 'found a valid range from the range name
            'Determine if shape exists already, in presentation
            Set myShape = findShape_Tag(oPPTPres, rImage.Value)
            If Not myShape Is Nothing Then
                'get parameters - there may be others - go to early binding and see!
                With myShape
                    shTop = myShape.Top
                    shLeft = myShape.Left
                    shHeight = myShape.Height
                    shWidth = myShape.Width
                End With

                'replace the shape

                Set oPPTSlide = myShape.Parent
                
                'myShape.Select
                myShape.Delete
                
                With oPPTSlide
                    With .Shapes.Paste
                            
                        ' Align the pasted range
                        .Top = shTop
                        .Left = shLeft
                        .Height = shHeight
                        .Width = shWidth
                        .Name = rImage.Value 'apparently you have to rename
                        .Tags.Add rImage.Value, rImage.Value 'apparently you have to retag
                    End With
                End With
            Else
                ' Add a new slide and paste in the chart
                With oPPTPres.slides
                    SlideCount = .Count
                    Set oPPTSlide = .Add(SlideCount + 1, ppLayoutText)
                End With
        
                ''RB: "activewindow" removed (can be deleted) in case you move the ".visible = true" to the end of the macro
                'oPPTApp.ActiveWindow.View.GotoSlide oPPTSlide.SlideIndex
    
                With oPPTSlide

                    With .Shapes.Paste
                        
                        ' Align the pasted range
                        .Align msoAlignCenters, True
                        .Align msoAlignMiddles, True
        
                        ' Position pasted chart
                        .Left = 100
                        .Top = 160
                        .Name = rImage.Value
                        .Tags.Add rImage.Value, rImage.Value
                    End With
        
                    With .Shapes.Placeholders(1)
                        .TextFrame.TextRange.Text = sTitle
                        .Top = 90
                        .Height = 60
                    End With
                End With
            End If
        End If
    Next rImage

    'oPPTApp.Visible = msoTrue
    'RB: if you want to save the ppt file you may be able to modify the next line of code...
    oPPTPres.SaveAs Filename:=PresentationFileName

    'MsgBox "done", vbOKOnly + vbMsgBoxSetForeground, "MACRO HAS FINISHED!"
    ' Clean up
    Set oPPTSlide = Nothing
    Set oPPTPres = Nothing
    Set oPPTApp = Nothing
    Set Source_ws = Nothing
End Sub
Function findShape_Name(oPPTPres As Object, shapeName As String) As Object
Dim myPPTSlide As Object 'PowerPoint.Slide
Dim myPPTShape As Object 'PowerPoint.Shape

    
    For Each myPPTSlide In oPPTPres.slides
        For Each myPPTShape In myPPTSlide.Shapes
            If UCase(myPPTShape.Name) = UCase(shapeName) Then
                Set findShape_Name = myPPTShape
                Exit Function
            End If
        Next myPPTShape
    Next myPPTSlide
End Function
Function findShape_Tag(oPPTPres As Object, shapeName As String) As Object
Dim myPPTSlide As Object 'PowerPoint.Slide
Dim myPPTShape As Object 'PowerPoint.Shape
Dim myPPTTag As Object 'PowerPoint.Tags
Dim i As Long
    
    For Each myPPTSlide In oPPTPres.slides
        For Each myPPTShape In myPPTSlide.Shapes
            With myPPTShape.Tags
            
                For i = 1 To .Count
                    If UCase(.Name(i)) = UCase(shapeName) Then 'stop with first match on a tag
                        Set findShape_Tag = myPPTShape
                        Exit Function
                    End If
                Next i
            End With
        Next myPPTShape
    Next myPPTSlide
End Function

Open in new window


See attached workbook.  Say, I wonder if this might one day be an ExcelToWord! companion article?  Hmmm....  Do you think utilities like this that are packaged well good fodder for articles, or are you looking for the quick tip/trick?  What do you think, Rob?

Dave

ExcelToPowerPoint-r1.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
broro183Commented:
hi Dave,

Thanks for sussing this one out :-)
Yes, I think they would make good fodder for articles, but it will take a good one to make us put aside the page of Jon's that we have both referenced (you in this thread & me in the earlier thread ).

Rob
0
dlmilleCommented:
@Rob - I was thinking more of building a utility from it, certainly citing some of the tips, but making it a robust utility, as opposed to some code clips.

The key ADD for me is the name association and configuring similar position/etc., on delete/paste-replace, etc.

Cheers,

Dave
0
Bright01Author Commented:
Dave and Rob,

I would be happy to help with requirements or testing on this if you pursue a utility. I have this requirement come up quite often.

Best regards,

B.
0
dlmilleCommented:
Great.  If I do, I'd model it after ExcelToWord! (watch for publ in next week or so, or early Jan) - its pretty much done, but needs an hour or two to brush up, and I'm not finding the time, so may have to wait till vacation.

B - so, any feedback on my last post?

Dave
0
dlmilleCommented:
Bright - are we done?  Did you check my last post?  After tomorrow AM I'm out of pocket till Sun night or Mon Am - traveling.
0
Bright01Author Commented:
Dave,  Thanks for following up...... I've been swamped the past two weeks so my apologies for not closing this out.  This was helpful but I'm still not exactly at where I need to be.  To answer your question; I think having a utility that would allow specific transfer of Excel images to a predetermined position within a Powerpoint Template would be very helpful.  The Template could be a standard client deliverable that gets repopulated when a Excel file is updated within an engagement.  I think this is clearly possible now with smarttags and the kind of code you just wrote.  The problem for users like me is it still is not simple or intuitive enough to be easy to design and use.

Anyway, have a wonderful holiday season and thanks for all your help in 2011.

B01
0
dlmilleCommented:
Ok Bright01 - please create a thread for Excel to Powerpoint at which point I have a prototype we can go back and forth on which might be much more user friendly.

Cheers,

Dave
0
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 Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.