Reorder Slides via Excel VBA

Question

I have a slide deck that I would like to reorder, based on user-defined position defined in a column within excel. The table would look something like this:

Title	Group	Year	Slide #
TestA	GroupA	2010	1
Test B	GroupB	2011	2
Test C	GroupC	2012	4
Test D	GroupD	2013	3

And, for coding purposes, we can assume this is on Sheet1.

The objective is to move the 3rd title to slide 4, assuming that the current slide order is chronological based on title. The below code achieves this change but moves "Test C" back to slide 3 in the last loop occurrence (which is technically correct, but not desired). The user could reorder any/all of the slides, so I can't simply hardcode a .MoveTo for this single change.

Is there an alternative approach to reorganizing slides via vba? Or a better use of .MoveTo with this scenario?

Sub ReOrderSlides()
    Dim PowerPointApp As PowerPoint.Application
    Dim myPresentation As PowerPoint.Presentation
    Set PowerPointApp = GetObject(, "PowerPoint.Application")
    Set myPresentation = PowerPointApp.ActivePresentation
    Dim SlideIndex As Integer
    Dim i As Integer
    
    'Set numrows = number of classes in control sheet
    NumRows = Worksheets(1).Range("A2", Range("A2").End(xlDown)).Rows.Count
    'select cell A2
    Range("A2").Select
    'Establish "For" loop to loop "numrows" number of times
    For i = 1 To NumRows
    
        'Capture user-defined slide position
        SlideIndex = Worksheets(1).Cells(i + 1, 8).Value
        Debug.Print i
        Debug.Print SlideIndex
        
        'Moves slide x to position y in active presentation
        myPresentation.Slides(i).MoveTo toPos:=SlideIndex
        
        ActiveCell.Offset(1, 0).Select
    Next i
End Sub

SlideIndex references the current position of a slide in the presentation, but that number changes when you move a slide to a new position. An alternative is to use the SlideID property. Each slide has a unique ID that does not depend on its position. Here is the Microsoft page on this property: https://learn.microsoft.com/en-us/office/vba/api/powerpoint.slide.slideid — John Korchok, Mar 28 '23 at 20:01

score 0 · Accepted Answer · answered Mar 29 '23 at 13:33

SlideIndex nor SlideID were viable options for this scenario, as neither solved for user-defined positioning. Therefore, my solution was to .Name each slide based on it's associated "Title" in the Excel table, create an array of the list of Titles, and apply a Vlookup() to find the variable's associated user-defined slide #. The result is the below code:

Sub ReOrderSlides()
    Dim PowerPointApp As PowerPoint.Application
    Dim myPresentation As PowerPoint.Presentation
    Set PowerPointApp = GetObject(, "PowerPoint.Application")
    Set myPresentation = PowerPointApp.ActivePresentation
    Dim SlideIndex As Integer
    Dim Title As Variant
    Dim TitleList As Variant
    
    'Compiles list of Titles from Excel sheet
    TitleList = ThisWorkbook.Sheets(1).Range("A2:A20").Value

    'Loops through list of Titles
    For Each Title In TiteList
        'Looks up RC and associated slide index on Control sheet
        SlideIndex = Application.VLookup(Title, Sheet1.Range("A2:D50"), 4, False)
        
        'Sets PPT slide with name = RC to user-defined slide index
        Set myslide = myPresentation.Slides(Title)
        
            myslide.MoveTo toPos:=SlideIndex
    
    
    Next class
    
    

End Sub

Reorder Slides via Excel VBA

1 Answers1