CODEWITHSUNDEEP
excelvbapowerpoint
2020db9
2020db9

Reputation: 307

Reorder Slides via Excel VBA

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

Upvotes: 1

Views: 351

Answers (1)

2020db9
2020db9

Reputation: 307

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

Upvotes: 0

Related Questions