Macro (or any other way) to export data from Certain Cells to a PPT presention

[Jiveman7]

Hi,
Not sure if what i'm asking is possible, but thought i would ask...
i'm looking for a way to export data from a certain cell group (for example A1 to D4) to a existing PPT presention. So, in theory, when i click a button on the excel table, it will copy everything from the needed cells to an empty presentation slide.
or, alternatively, if possible, the excel would put a pic file in whatever format into the empty slide.
my guess is that if it can be done at all, vba/macro is the only way...
i know i'm probably asking too much of excel, but doesen't hurt to ask...
thx in advnace
Jiveman

 

[Haluk]

Hi;

Try the following code to copy the range A1 to D4 of the active sheet, as an Excel table into a new PowerPoint slide.

Sub SendRange_to_PowerPoint()
    Dim MyMyRng As Range
    Dim appPowerPoint As Object
    Dim filePowerPoint As Object
    Dim mySlide As Object
   

    Set MyRng = ActiveSheet.Range("A1:D4")

    Set appPowerPoint = CreateObject(class:="PowerPoint.Application")
    Set filePowerPoint = appPowerPoint.Presentations.Add
    Set mySlide = filePowerPoint.Slides.Add(1, 11)

    MyRng.Copy

    mySlide.Shapes.PasteSpecial DataType:=10
    
    appPowerPoint.Visible = True
    Application.CutCopyMode = False
End Sub

 

[Haluk]

Uh, you said that you need to send the range to an existing Power Point file.....

Then, just simply change the following line;

Set appPowerPoint = CreateObject(class:="PowerPoint.Application")

to this one;

Set appPowerPoint = GetObject(class:="PowerPoint.Application")

Be aware that; the existing Power Point file must be opened by you before executing the code.

Hope this helps,

 

[Jiveman7]

Hi Haluk,

Thx for the answer, it got me allot farther with allot less code lines than any other solution i've seen!

One issue though - even after changing the line as you said, when i run the macro, with ppt open, it copies the range to a new ppt presentation instead of to the existing presentation i have open...any tips for that?

Thx!

 

[Haluk]

Hi again;

It was my fault ..... just a small modification is needed. Open the necessary PPT and then run the macro from the Excel file to insert the range (""A1:D4") to the PPT file that is already open.

Sub SendRange_to_PowerPoint()
    Dim MyMyRng As Range
    Dim appPowerPoint As Object
    Dim filePowerPoint As Object
    Dim mySlide As Object
    

    Set MyRng = ActiveSheet.Range("A1:D4")

    Set appPowerPoint = GetObject(class:="PowerPoint.Application")
    Set filePowerPoint = appPowerPoint.Presentations(1)
    Set mySlide = filePowerPoint.Slides.Add(1, 11)

    MyRng.Copy

    mySlide.Shapes.PasteSpecial DataType:=10
    
    appPowerPoint.Visible = True
    Application.CutCopyMode = False
End Sub

 

[Haluk]

Hi again;

You can also use the below code as well, where the necessary PPT file in this case is closed. The macro below will itself open the PPT file, copy the range as an Excel table, save the PPT file and quit the PPT application.

The PPT file is located as; C:\TestFolder\TestPPT.pptx and you can change the file path and file name to suit your needs in the code below, easily.

Sub SendRange_to_PowerPoint2()
    Dim MyMyRng As Range
    Dim appPowerPoint As Object
    Dim filePowerPoint As Object
    Dim mySlide As Object
    Dim strPPTfile As String

    Set MyRng = ActiveSheet.Range("A1:D4")
    
    strPPTfile = "[COLOR=#b22222][B]C:\TestFolder\TestPPT.pptx[/B][/COLOR]"

    Set appPowerPoint = CreateObject(class:="PowerPoint.Application")
    Set filePowerPoint = appPowerPoint.Presentations.Open(strPPTfile)
    Set mySlide = filePowerPoint.Slides.Add(1, 11)

    MyRng.Copy

    mySlide.Shapes.PasteSpecial DataType:=10
    appPowerPoint.Presentations(strPPTfile).Save
    appPowerPoint.Presentations(strPPTfile).Close
    appPowerPoint.Quit
    
    Application.CutCopyMode = False
End Sub

 

[Jiveman7]

Thx man, This is exactly what i was looking for!

you're a life saver!

 

[Haluk]

You are welcome