Excel to Powerpoint Macro

baba7vb

New Member
Joined
Jun 7, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
Hello VBA Geeks
I need your help!

Problem statement:
I have an excel sheet on my c drive.
I would like to move data that meets multiple criteria, to an existing powerpoint template in my c drive. The Powerpoint template needs
to be reused every month with new excel data.

Excel Criteria to move selected rows to various PPT slides.
1- Move rows(Column A-G), if column C equals Zero.
To powerpoint template- slide 5.
2- move rows (Column A-H), if column D equals 1.
To powerpoint template- Slide 6

Hope you can help.

I dont want to create a new presentation each time but append an existing template.

Thanks
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
welcome to the board

You don't necessarily need a macro for this, and if you do then you don't necessarily need to write the data across to Powerpoint. You can have Powerpoint link directly into your file

From Powerpoint: Insert > Object > Create from File > choose your file > tick the [link] box > OK

This embeds part of your spreadsheet directly into that slide, without requiring any VBA. If you have criteria that need to be controlled using VBA, then this is much easier being kept within the Excel file itself, i.e. just being used to control an "interface" worksheet that's configured solely for this presentation
 

baba7vb

New Member
Joined
Jun 7, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
Hi Thanks for your suggestion.
With the criteria I mentioned what would be the code to use in Powerpoint VB? If i create a link on the various slides.
 

baba7vb

New Member
Joined
Jun 7, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
Hello based on my original post, this is the code I would like to adapt based on my criteria.
Can anyone help.

Excel Criteria to move selected rows to various PPT slides.
1- Move rows(Column A-G), if column C equals Zero.
To powerpoint template- slide 5.
2- move rows (Column A-H), if column D equals 1.
To powerpoint template- Slide 6

I will look at the other suggestion by baitmaster aswell as a back up.


VBA Code:
Dim oPPTApp As PowerPoint.Application
Dim oPPTShape As PowerPoint.Shape
Dim oPPTFile As PowerPoint.Presentation
Dim SlideNum As Integer
Sub PPTableMacro()
    Dim strPresPath As String, strExcelFilePath As String, strNewPresPath As String
    strPresPath = "C:\PowerPoint\Presentation1.ppt"
    strNewPresPath = "C:\PowerPoint\new1.ppt"
    
    Set oPPTApp = CreateObject("PowerPoint.Application")
    oPPTApp.Visible = msoTrue
    Set oPPTFile = oPPTApp.Presentations.Open(strPresPath)
    SlideNum = 1
    oPPTFile.Slides(SlideNum).Select
    Set oPPTShape = oPPTFile.Slides(SlideNum).Shapes("Table1")
    

    Sheets("Sheet1").Activate
[B]'I want to amend this code based on criteria above[/B]
    oPPTShape.Table.Cell(1, 1).Shape.TextFrame.TextRange.Text = Cells(1, 1).Text
    oPPTShape.Table.Cell(1, 2).Shape.TextFrame.TextRange.Text = Cells(1, 2).Text
    oPPTShape.Table.Cell(1, 3).Shape.TextFrame.TextRange.Text = Cells(1, 3).Text
    oPPTShape.Table.Cell(2, 1).Shape.TextFrame.TextRange.Text = Cells(2, 1).Text
    oPPTShape.Table.Cell(2, 2).Shape.TextFrame.TextRange.Text = Cells(2, 2).Text
    oPPTShape.Table.Cell(2, 3).Shape.TextFrame.TextRange.Text = Cells(2, 3).Text
  
    oPPTFile.SaveAs strNewPresPath
    oPPTFile.Close
    oPPTApp.Quit
          
    Set oPPTShape = Nothing
    Set oPPTFile = Nothing
    Set oPPTApp = Nothing
    
    MsgBox "Presentation Created", vbOKOnly + vbInformation
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,073
Messages
5,545,832
Members
410,709
Latest member
Mrsamir
Top