PPT Resuse Slides in VBA with dynamic file Name coming from Excel

gmooney

Board Regular
Joined
Oct 21, 2004
Messages
205
Office Version
  1. 365
Platform
  1. Windows
Hi folks,

I have some Excel code that contains the filename of a PPT file that needs to be inserted into an existing PPT file through the Reuse slides function.

Right now I do not know how to pass that file name from the Excel code to PPT code.

Here is the Excel code that ultimately opens a PPT file and the start of the PPT code is currently using a Hard Coded filename to use for the Reuse slides function.

Any ideas on how to replace my line 2 of the PPT code (below the Excel code) with the dynamic filename? The text of the dynamic filename can be found in cell BB107 of the Excel file.

Excel
VBA Code:
Sub FinishCategoryReview()
    
    Dim oPPApp As Object, oPPPrsn As Object, oPPSlide As Object
    Dim oPPShape As Object
    Dim PPTemplatestrName As String
    Dim XLStrName As String
    Dim URL1 As String
    Dim URL2 As String


    XLStrName = ThisWorkbook.Sheets("Report Links").Range("BB110").Value

    '   ~~> Change this to the relevant file
    PPTemplatestrName = GetDesktopPath & "Category Review Template.pptm"
  
    '   ~~> Establish an PowerPoint application object
    On Error Resume Next
    Set oPPApp = GetObject(, "PowerPoint.Application")

    If Err.Number <> 0 Then
        Set oPPApp = CreateObject("PowerPoint.Application")
    End If
    Err.Clear
    On Error GoTo 0

    oPPApp.Visible = True

    '   ~~> Open the relevant powerpoint file
    Set oPPPrsn = oPPApp.Presentations.Open(PPTemplatestrName)
    '   ~~> Change this to the relevant slide which has the shape
    Set oPPSlide = oPPPrsn.Slides(16)
    '   ~~> Change this to the relevant shape
    Set oPPShape = oPPSlide.Shapes("ADHocItemRanking")
    '    ~~> Write to the shape
    oPPShape.TextFrame.TextRange.Text = _
    ThisWorkbook.Sheets("Report Links").Range("BB104").Value
    
    '   ~~> Change this to the relevant slide which has the shape
        Set oPPSlide = oPPPrsn.Slides(16)
    '   ~~> Change this to the relevant shape
    Set oPPShape = oPPSlide.Shapes("ADHocEfficientAssortment")
    '   ~~> Write to the shape
    oPPShape.TextFrame.TextRange.Text = _
    ThisWorkbook.Sheets("Report Links").Range("BB107").Value

    AppActivate "Category Review Links.xlsm"
    
    
    
    Application.EnableEvents = False
    oPPApp.Run "Category Review Template.pptm!Module1.Finish"
    Application.EnableEvents = True
    Application.EnableEvents = False
    oPPApp.Run "Category Review Template.pptm!Module1.URL"
    Application.EnableEvents = True
  
        
    MsgBox "Congratulations! Your new Category Review has been built. You can now begin your Category Review work in the PPT file.", vbInformation
 
    AppActivate XLStrName & ".pptx"

    
End Sub

PPT:
Code:
Sub Finish()

ActivePresentation.Slides.InsertFromFile _
    "C:\Users\mogr0002\Downloads\Category Review Grand Canyon - PACKAGED BEVERAGES.pptx", 1

Dim URL1 As String
Dim URL2 As String
Dim i As Long
    
    Dim varrPos     As Variant


    varrPos = Array(34, 34, _
                    36, 36, _
                    38, 38, 38, 38, 38, 38, 38, 38, 38, _
                    40, 40, 40, 40, 40, 40, 40, _
                    43, 43, 43, 43, 43)


    With ActivePresentation
        For i = 0 To UBound(varrPos)
            .Slides(2).MoveTo toPos:=varrPos(i)
        Next i



        PPStrName = .Slides(2).Shapes("Title 1").TextFrame.TextRange.Text
        .Slides(2).Shapes("Title 1").TextFrame.TextRange.Copy
        .Slides(1).Shapes("Title 1").TextFrame.TextRange.Paste

    With ActivePresentation.Slides(1).Shapes("Title 1")

    With .TextFrame.TextRange.Font

        .Size = 40

        .Name = "Arial"

        .Bold = True


    End With

        Application.ActivePresentation.Slides(1).Shapes("Title 1") _
        .TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignCenter

    End With
    
    End With

    End Sub
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
385
Office Version
  1. 365
Platform
  1. Windows
How about creating a helper text file in the destination folder that Excel overwrites every time with the filename inside that PPT can then open to read? Always name it the same thing.
 

gmooney

Board Regular
Joined
Oct 21, 2004
Messages
205
Office Version
  1. 365
Platform
  1. Windows
How about creating a helper text file in the destination folder that Excel overwrites every time with the filename inside that PPT can then open to read? Always name it the same thing.
Hi shknbk2,

I am not sure that I follow how you are saying to do this but the actual Excel file will be a different name every time the user downloads the file from the web. The downloaded file will always start as Category Review but then Grand Canyon can be 1 of 15 geographies and Packaged Beverages can be 1 of 23 categories.
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
385
Office Version
  1. 365
Platform
  1. Windows
Excel knows the downloaded filename, right? I'm assuming so.

Have excel save a text file in "C:\Users\mogr0002\Downloads\" called something (like "Category Review ppt filename.txt") where the text file has "Category Review Grand Canyon - PACKAGED BEVERAGES.pptx" in it (or maybe the full path). Then, PPT opens the text file, reads line 1, and gets the name that way.
 
Solution

gmooney

Board Regular
Joined
Oct 21, 2004
Messages
205
Office Version
  1. 365
Platform
  1. Windows
Excel knows the downloaded filename, right? I'm assuming so.

Have excel save a text file in "C:\Users\mogr0002\Downloads\" called something (like "Category Review ppt filename.txt") where the text file has "Category Review Grand Canyon - PACKAGED BEVERAGES.pptx" in it (or maybe the full path). Then, PPT opens the text file, reads line 1, and gets the name that way.
Great idea!
 

Forum statistics

Threads
1,136,865
Messages
5,678,219
Members
419,753
Latest member
Vj3006

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top