VBA to create power point presentation title

[BabyFish2019]

Hi

I am trying to automate a powerpoint presentation and am trying to write some basic instructions so that I can use them to tweak the code below. I want to have an excel spreadsheet that is updated centrally and then the macro will write the presentation from the sheet. I have written the basic code below but need help expanding it please. I want it to use the below table which sits on Sheet2 to populate the slides and I would like it to do the following:

on "Sheet2" in the sheet there will be a table

(A1) Slide Number	(B1) Slide Format	(C1) Slide Title	(D1) Slide Text Content	If slide has a table/chart location and range of table
1	Title and Content			N/A
2	Title Chart and Content			"Sheet1" (not sure how to find or name a chart)
3	Title Table and Content			"Sheet1" - range of table is A3:E12

So based on the above I would like to create 3 slides (1 for each row in the sheet)

Column A represents what slide number is being dealt with
Column B represents the type of slide (I know this can be a number and in my below code I have used Set myslide = mypresentation.slides.Add(1, 11) where 11 is the slide type but I don't know where I can get a list of the slide types to use?) essentially if I knew the list of numbers I would want this code to replace the "11" it this example with what ever slide type number is written in column B.
Column C would label the slide title with whatever text is in that box
Column D would then fill the contents of the slide with whatever is written in that cell - is there away to use an autofit function as I have tried to use activeSlide.Shapes(2).TextFrame.TextRange.Font.Size = 10 but this doesn't work)
Column E - if there is a chart or table to be included this will be found on "Sheet1" in the relevant range. So my code below includes a table on sheet1 range A3:E12 which works but not sure how to do make it find a chart?

So basically I want the VBA to create a slide per row of information and keep going until all rows have a slide. So in this example there will be 3 rows so 3 slides but if there were 5 rows of data I would want 5 slides etc.

I am not great with VBA but understand it so if someone could show me the code I know I could manipulate it to do what I want, however I struggle to get the code to sit in the right part of the function.

I hope this makes sense and thank you in advance.

Private Sub CommandButton1_Click()

Dim r As Range
Dim powerpointapp As Object
Dim mypresentation As Object
Dim myslide As Object
Dim myshape As Object

'this is the range and location of the table you want to insert into Powerpoint
Set r = ThisWorkbook.Worksheets("Sheet1").Range("A3:e12")

On Error Resume Next

'if PowerPoint is already opened
Set powerpointapp = GetObject(Class:="PowerPoint.Application")

'if PowerPoint is not already open then this will open PowerPoint
If powerpointapp Is Nothing Then Set powerpointpp = CreateObject(Class:="PowerPoint.Application")

'This will create a new presentation
Set mypresentation = powerpointapp.presentations.Add

'the second number after the comment selects the standard slide template type from powerpoint so change based on what slide you want to create.
Set myslide = mypresentation.slides.Add(1, 11)

'"r" is what we have labelled the table range earlier and we are copying this into the presentation
r.Copy

'to paste the range into the slide - DataType number select from list below
' 0 = ppPasteBitmap
' 1 = •ppPasteDefault
' 2 = •ppPasteEnhancedMetafile
' 3 = •ppPasteHTML
' 4 = •ppPasteGIF
' 5 = •ppPasteJPG
' 6 = •ppPasteMetafilePicture
' 7 = •ppPastePNG
' 8 = •ppPasteShape

myslide.Shapes.PasteSpecial DataType:=2

'the below 3 lines show where the range should be positioned on the slide so alter the numbers to adjust distance from left and distance from top of slide
Set myshape = myslide.Shapes(myslide.Shapes.Count)
myshapes.Left = 250
myshape.Top = 150
          
                
powerpointapp.visiible = True
powerpointapp.Activate

'this code gets rid of the cutcopy mode from the clipboard
Application.CutCopyMode = False


End Sub

 

[Jon Peltier]

There are a lot of questions in your post, more implicit ones than the few explicit ones you are aware of.

Let's start with some built-in tools. A lot of us self-taught folks stumble upon them by accident, or are told by a kindly old gentleman like me in the forums.

In the Excel VBA Editor, go to Tools > References, scroll down the list, and add PowerPoint:

Now open the Object Browser from the VBA Editor View menu, and you can find all kinds of things. I searched for SlideLayout, which found PpSlideLayout, and when I clicked it in the left hand list, there were two pages of slide layouts in the right hand list.

You should be using the Object Browser for all of Excel's syntax, but by setting the reference to PowerPoint, you can use it for that syntax as well. Also, by setting the reference, you can use the named constants, instead of trying to remember that 11 means ppLayoutTitleOnly. (Of course, if you're reading from the Excel table, you'll either have to use the numerical constants, or type "Title Only" and make your VBA code translate your text into a named constant.)

Believe it or not, PowerPoint has no "ActiveSlide". It's actually missing a number of handy things that we've become accustomed to in Excel. But PowerPoint VBA is like VBA-Lite (while Word's VBA was written by developers on peyote). To get the active slide:

Dim ActiveSlide As PowerPoint.Slide
Set ActiveSlide = powerpointapp.ActiveWindow.View.Slide

Of course, I don't remember this. But Google is another good programming tool. Search for "PowerPoint VBA Active Slide", and you will get the same result.

 

[Jon Peltier]

To export each row of the table, you will need to loop.

Dim rExport As Range
Set rExport = ThisWorkbook.Worksheets("Sheet2").UsedRange
Dim iRow As Long
For iRow = 2 To rExport.Rows.Count
    ' do the export stuff here
Next

Note: Give the worksheet a more descriptive name, like "Export List", so you'll remember in 4 months what it's for.

In one of my classes, I actually include a lesson that covers this example, using a table in Excel to guide generation of a PowerPoint presentation. But I haven't done it in a while, and it's getting late, so I don't remember so much of it off the top of my head.