Copy Excel chart to PPT that is already open
 

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

Copy Excel chart to PPT that is already open
 

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" wrote:

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

Copy Excel chart to PPT that is already open
 

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

Copy Excel chart to PPT that is already open
 

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" wrote:

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

Copy Excel chart to PPT that is already open
 

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

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

The 2nd parameter is the application.

"Tony Bender" wrote:

On Sep 25, 10:27 pm, Joel wrote:
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" wrote:
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- Hide quoted text -

- 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