Copy Excel chart to PPT that is already open

[Tony Bender]

I have an Excel application where the user can view tables and charts
in Excel and have the option of exporting the table and pie chart into
a PowerPoint presentation. Here is my code that opens PPT and
launches a macro I have in PPT that copies the Excel pie chart and
table and pastes onto a new slide in the existing PPT file. This
works fine, except when I return to the Excel workbook and find other
tables I want to export into the PPT deck, this code opens a second
(read-only) version of the original. What is the code to simply copy
onto a PPT that is already open?

Sub OpenPPT()
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation

Set PPApp = CreateObject("PowerPoint.Application")
PPApp.Visible = msoTrue

Set PPPres = PPApp.Presentations.Open("C:\data\TestPPT.ppt")

'******Runs macro in PPT that copies data from excel and pastes
onto PPT slides
PPApp.Run (PPPres.Name & "!AddPieChart")

End Sub


I tried replacing
Set PPApp = CreateObject("PowerPoint.Application")
with
Set PPApp = GetObject("PowerPoint.Application")

but that doesn't work.

Can anyone help me with this?

Thank you,

TB

 

[Joel]

try this

Sub OpenPPT()
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation


'Get existing instance of PP if it's open; otherwise create a new one

On Error Resume Next

Set PPApp = GetObject("PowerPoint.Application")
If Err Then
Set PPApp = New PowerPoint.Application
Set PPPres = PPApp.Presentations.Open("C:\data\TestPPT.ppt")
Else
Set PPPres = PPApp.Presentations("TestPPT.ppt")
End If

On Error GoTo 0

'******Runs macro in PPT that copies data from excel and pastes onto PPT
slides
PPApp.Run (PPPres.Name & "!AddPieChart")

End Sub

 

[Tony Bender]

Joel,

Thanks for this, but it didn't work.
I got a Run-time error 91 "Object variable or With Block variable not
set" and when I clicked Debug it highlighted this line:

PPApp.Run (PPPres.Name & "!AddPieChart")

When I remove this line the macro doesn't do anything.
TB

 

[Joel]

I made a couple of changes to get the code to run. I not sure what you are
trying to do. I went to the power point VBA help and didn't find a RUN
method. I did find a RUNS method and here is an example of that instruction.
I didn't know the slide number or object that yhou are using.

With Application.ActivePresentation.Slides(1).Shapes(2) _
.TextFrame.TextRange
With .Runs(2).Font
If .Italic Then
.Bold = True
End If
End With
End With



Sub OpenPPT()
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation


'Get existing instance of PP if it's open; otherwise create a new one

On Error Resume Next

Set PPApp = GetObject(, "PowerPoint.Application") '<added comma
If Err Then
Set PPApp = New PowerPoint.Application
PPApp.Visible = msoTrue '<made application visible
Set PPPres = PPApp.Presentations.Open("C:\temp\slide 1.ppt")
Else

Set PPPres = PPApp.Presentations("slide 1.ppt")

End If

On Error GoTo 0

'******Runs macro in PPT that copies data from excel and
'pastes onto PPT slides
PPApp.Run (1)

End Sub

 

[Tony Bender]

I made a couple of changes to get the code to run.  I not sure what youare
trying to do.  I went to the power point VBA help and didn't find a RUN
method.  I did find a RUNS method and here is an example of that instruction.
 I didn't know the slide number or object that yhou are using.

With Application.ActivePresentation.Slides(1).Shapes(2) _
        .TextFrame.TextRange
    With .Runs(2).Font
        If .Italic Then
            .Bold = True
        End If
    End With
End With

Sub OpenPPT()
    Dim PPApp As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation

    'Get existing instance of PP if it's open; otherwise create a newone

   On Error Resume Next

   Set PPApp = GetObject(, "PowerPoint.Application")  '<added comma
   If Err Then
       Set PPApp = New PowerPoint.Application
       PPApp.Visible = msoTrue     '<made application visible
       Set PPPres = PPApp.Presentations.Open("C:\temp\slide 1.ppt")
   Else

       Set PPPres = PPApp.Presentations("slide 1.ppt")

   End If

   On Error GoTo 0

    '******Runs macro in PPT that copies data from excel and
    'pastes onto PPT slides
    PPApp.Run (1)

End Sub








- Show quoted text -

Joel,

Thank you so much for your help with this. It works fine now.

I am curious though how adding the comma made the difference.

Set PPApp = GetObject(, "PowerPoint.Application") '<added comma

Thanks again for your help.

Tony

 

[Joel]

Usually get object if a filename which is the 1st parameter

set PPApp = getobject("c:\temp\abc.ppt")

The 2nd parameter is the application.