Need to copy some charts from Excel to PP slides using VBA in Excel

[automaker]

My data and formatted charts are in Excel(2002). I need to copy and paste them as pictures into Powerpoint slides. I really want to do this using VBA as it will need to be done weekly. I have figured out how to launch Powerpoint but that is about as far as I'm getting. I need to open a file (it can be existing) and then transfer the charts and save/close the file. I'm sure this can be done. Is there some documentation on doing this anywhere? Any help is appreciated.

Thanks,

 

[SkipVought]

Hi,

Check out the CopyPicture method.

Post any specific code issues with example of your code.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[automaker]

Thanks for responding Skip.
I will keep the copypicture method in mind. However, my first issue is opening a presentation. I have stumbled onto the code to launch Powerpoint but I don't know how to even open a file. I would like to open a standard set of formatted slides, copy the charts into the appropriate slides, and then save the Powerpoint presentation under a different name so I don't change the standard (blank) presentation. This way the presentation is always the same except for the new charts. I considered embedding the charts into the slides so they would be linked to the Excel data. However, the charts are not sized correctly when pasted into Powerpoint and resizing them distorts them. Therefore I need to paste them in as pictures and the pictures are not linked to the data.

Thanks for any help you can provide.

Mark

 

[SkipVought]

"...resizing them distorts them. "

Make sure that the AspectRatio is LOCKED before you resize.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[automaker]

Skip,
It seems that pasting charts into Powerpoint as picture(enhanced metafile) allows the chart to be resized to fit the slide with less distortion than pasting the chart in as an Excel chart.

 

[SkipVought]

Have you been using your macro recorder?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[automaker]

Skip,
No, I have not used the recorder. I tried but it does not record actions taken in a different application. In other words, if I record launching Powerpoint and opening a file from Excel, I get virtually nothing.

 

[SkipVought]

True, you cannot record actions in another application. But you ought to be using it in the Excel application to go as far as you can.

I still have not seen any of your code with specific questions related to it.

Within the PowerPoint application object, your main object will be the Presentation object. Within the Presentation object you will have a Slides collection.


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

Here's some code I found. Initially, run AddButton to put a COPY button on your chart. I had TWO ChartObjects in my Chart sheet. that i copied and pasted into the active presentation...

Sub CopyCharts()
    Dim i As Integer, n As Integer, w As Integer
    Dim oPP As PowerPoint.Application, oSlide As PowerPoint.Slide, oShape As PowerPoint.Shape
    Dim wb As Workbook
    
    On Error Resume Next
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
    Set oPP = GetObject(, "PowerPoint.Application")
    If Err.Number <> 0 Then
        MsgBox "Open your PowerPoint Presentation to COPY"
        Exit Sub
    End If

    DeleteButtons
    Chart1.Export ActiveWorkbook.Path & "\LoanAnalysis.gif", "GIF"
    AddButton
    
    With oPP.ActivePresentation
        n = .Slides.Count + 1
        Set oSlide = .Slides.Add(Index:=n, Layout:=ppLayoutBlank)

        With oSlide
            w = oPP.ActivePresentation.PageSetup.SlideWidth
            For i = 0 To 0
                Set oShape = .Shapes.AddPicture( _
                        Filename:=ActiveWorkbook.Path & "\LoanAnalysis.gif", _
                        LinkToFile:=msoFalse, _
                        SaveWithDocument:=msoTrue, _
                        Left:=2, _
                        Top:=137, _
                        Width:=716, _
                        Height:=500)
                With oShape
                    .Width = w - 72
                    .Left = (w - .Width) / 2
'                    .Top = oPP.ActivePresentation.PageSetup.SlideHeight - (.Height - 36) * i
                    .Top = 36 + .Height * i
                End With
            Next
        End With
    End With
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
    
    Set oSlide = Nothing
    Set oPP = Nothing
End Sub
Sub AddButton()
    Dim oBTN As Object, sName As String
    sName = "COPY"
    With Chart1
        Set oBTN = .Buttons.Add(0, 0, 50, 20)
        With oBTN
            .OnAction = "Chart1.CopyCharts"
            .Name = "btn" & sName
            .Caption = sName
        End With
    End With
        Set oBTN = Nothing
End Sub
Sub DeleteButtons()
    Dim shp As Shape
    For Each shp In Chart1.Shapes
        With shp
            Select Case .Type
                Case msoChart
                Case msoFormControl: .Delete
            End Select
        End With
    Next
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[automaker]

Thanks Skip,
The reason you haven't seen any of my code with specific questions is I don't have any code. I have code which summarizes the data as it changes and creates the charts. All in Excel. I have never dealt with Powerpoint using code and I had no idea how to even open a presentation using code. That was my original question.

 

[PHV]

I had no idea how to even open a presentation using code
When in the Powerpoint VBE feel free to play with the F1 and F2 keys.

 

[automaker]

PHV,
Thanks for the tip. Being relatively ignorant, I use the help files quite a bit. The list of libraries and other methods/properties etc. under F2 is new to me and should help a bunch. I have not been working within the Powerpoint VB because my hope is to control everything from within Excel. This weekly reporting I am expected to do needs to be automated. For one, I make plenty of mistakes and automation will eliminate that. Second, I consider it a waste of time to do the same thing over and over again when it can be automated.
Thanks again and Happy Thanksgiving to everyone.

Mark

 

[misterstick]

I have not been working within the Powerpoint VB because my hope is to control everything from within Excel.

even from excel, you'll be running powerpoint commands. the ide is pretty much the same, so record a macro in powerpoint then port it to excel.

mr s. <

 

[SkipVought]

The key is the GetObject method to open your specific presentation.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[automaker]

Thanks gentlemen,
I will give this a try and let you know. Have a good weekend.

Mark

 

[automaker]

Gentlemen,
I hope you all had a peaceful Thanksgiving.
I have tried some of your suggestions without much luck.

Excel VBA doesn't seem to like to dimension a variable as powerpoint.anything.

So to launch Powerpoint I used this statement
Application.ActivateMicrosoftApp xlMicrosoftPowerPoint
This seems to work to get Powerpoint up and running.

The next set of statements seem to work if I am in the VB editor but don't if I run them outside the editor.
Set ppapp = GetObject(, "powerpoint.application")
ppapp.presentations.Open Filename:="C:\test.ppt"
Set pppres = ppapp.activepresentation

If I run outside the editor, I get a 429 run time error. The description is "ActiveX component can't create object". I believe it is choking on opening the presentation.

I apologize for my ignorance. This shouldn't be as difficult as I am making it. Is there some documentation I could read so that when I post a question I am posting something worth your time?

Thanks for the help.

Mark

 

[SkipVought]

    Dim ppapp As PowerPoint.Application, pppres As PowerPoint.Presentation
    
    Set ppapp = CreateObject("Powerpoint.Application")
    Set pppres = GetObject("C:\test.ppt")
    ppapp.Visible = msoCTrue
    
'do stuff here in your presentation


'save/close/quit here    
'    pppres.Save
'    pppres.Close
'    ppapp.Quit

'release memory
    Set pppres = Nothing
    Set ppapp = Nothing

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[automaker]

Skip,
Thanks for the help but I know this won't work. I don't know if the problem is the version of Excel or what but I can't dim variables as powerpoint.anything. I will work on the rest.

Mark

 

[SkipVought]

Do you have a reference set to the Microsoft Powerpoint m.n Object Library?

Tools > References

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[automaker]

Skip,
Of course I didn't. I do now. I knew there was something that I was missing. I have to hit the road now. I will work on this tomorrow if I can and get back to you as soon as I can.

Mark