Consulting

Results 1 to 11 of 11

Thread: Excel VBA to Run a PPT Sub

  1. #1

    Excel VBA to Run a PPT Sub

    I have a user who is getting an error when this line of code is trying to run:

    PPTApp.Run MyDesktopPath & "" & "Category Review Template.pptm!Module1.BuildPPT"

    PPTApp is set as a String and the filepath of MyDesktopPath and the rest of that line are exactly what they should be.

    Code runs fine on my PC but not on hers.

    The error she gets is Application.Run : Invalid Request. Sub or Function not defined.

    Thoughts?

  2. #2
    copy your PPTA function.

  3. #3
    Quote Originally Posted by arnelgp View Post
    copy your PPTA function.
    I don’t think there is a PPTA function

  4. #4
    then how did you instantiate PPTA?
    my understanding is that it stands for
    powerpoint.application?

  5. #5
    Quote Originally Posted by arnelgp View Post
    then how did you instantiate PPTA?
    my understanding is that it stands for
    powerpoint.application?
    So I declared:

    Dim PPTApp As Object (Sorry earlier I said String)

    Then I set PPTApp = CreateObject ("PowerPoint.Application")

    Then Application.EnableEvents = False

  6. #6
    Quote Originally Posted by arnelgp View Post
    then how did you instantiate PPTA?
    my understanding is that it stands for
    powerpoint.application?
    @arnelgp. Did you see my last post? And yes it does stand for PowerPoint.application

  7. #7
    you need to Open the .pptm file first, the you cal Run the macro.

    Dim PPTApp As Object, PPTFile As Object
    Set PPTApp = CreateObject ("PowerPoint.Application")
    Last edited by arnelgp; 08-25-2021 at 06:55 PM.

  8. #8
    Quote Originally Posted by arnelgp View Post
    you need to Open the .pptm file first, the you cal Run the macro.

    Dim PPTApp As Object, PPTFile As Object
    Set PPTApp = CreateObject ("PowerPoint.Application")
    Set PPTFile = PPTApp.Presentations.Open("C:\FolderName\Category Review Template.pptm")
    PPTFile.BuildPPT
    Hi, my question is this code runs fine for me but not for her.

    The only difference is file location between both of us. She is in a OneDrive environment where here downloads and desktop folder are under a OneDrive folder and mine are the standard lcoation.

    But my code first checks to see if it is a OneDrive machine and then looks for files in the repspective lcoations.

  9. #9
    Quote Originally Posted by arnelgp View Post
    you need to Open the .pptm file first, the you cal Run the macro.

    Dim PPTApp As Object, PPTFile As Object
    Set PPTApp = CreateObject ("PowerPoint.Application")
    Here is the sub in Excel where the error message happens. I bolded the line of code where it is erroring out and again, for me the code runs fine.

    Sub BuildMyCategoryReview()
    
        Dim PPTCreatedFileName As String
        Dim PPTTemplateName As String
        Dim PPTApp  As Object
        Dim PPTPrsn As Object
        Dim PPTSlide As Object
        Dim PPTShape As Object
        Dim URL1   As String
        Dim URL2   As String
    
    
        IsThisOneDriveMachine
        
        PPTCreatedFileName = ThisWorkbook.Sheets("Category Review").Range("AQ122").Value
        
        'Change this to the relevant file
        PPTTemplateName = MyDesktopPath & "\" & "Category Review Template.pptm"
        
        'Establish an PowerPoint application object
        On Error Resume Next
        Set PPTApp = GetObject(, "PowerPoint.Application")
        
        If Err.Number <> 0 Then
            Set PPTApp = CreateObject("PowerPoint.Application")
        End If
        Err.Clear
        On Error GoTo 0
        
        PPTApp.Visible = True
        
        'Open the relevant powerpoint file
        Set PPTPrsn = PPTApp.Presentations.Open(PPTTemplateName)
        
        
        'Change this to the relevant slide which has the shape
        Set PPTSlide = PPTPrsn.Slides(16)
        AppActivate "Category Review Builder"
        'Change this to the relevant shape
        Set PPTShape = PPTSlide.Shapes("ADHocItemRanking")
        'Write to the shape
        PPTShape.TextFrame.TextRange.Text = ThisWorkbook.Sheets("Category Review").Range("AQ110").Value
        
        'Change this to the relevant slide which has the shape
        Set PPTSlide = PPTPrsn.Slides(16)
        AppActivate "Category Review Builder"
        'Change this to the relevant shape
        Set PPTShape = PPTSlide.Shapes("ADHocEfficientAssortment")
        'Write to the shape
        PPTShape.TextFrame.TextRange.Text = ThisWorkbook.Sheets("Category Review").Range("AQ113").Value
        
        'Change this to the relevant slide which has the shape
        Set PPTSlide = PPTPrsn.Slides(21)
        AppActivate "Category Review Builder"
        'Change this to the relevant shape
        Set PPTShape = PPTSlide.Shapes("ConsumerProfile")
        'Write to the shape
        PPTShape.TextFrame.TextRange.Text = ThisWorkbook.Sheets("Category Review").Range("AQ116").Value
        
        'Change this to the relevant slide which has the shape
        Set PPTSlide = PPTPrsn.Slides(21)
        AppActivate "Category Review Builder"
        'Change this to the relevant shape
        Set PPTShape = PPTSlide.Shapes("CompetitorByChannel")
        'Write to the shape
        PPTShape.TextFrame.TextRange.Text = ThisWorkbook.Sheets("Category Review").Range("AQ119").Value
        
        'Change this to the relevant slide which has the shape
        Set PPTSlide = PPTPrsn.Slides(2)
        AppActivate "Category Review Builder"
        'Change this to the relevant shape
        Set PPTShape = PPTSlide.Shapes("Category Manager")
        'Write to the shape
        PPTShape.TextFrame.TextRange.Text = "CATEGORY MANAGER: " & ThisWorkbook.Sheets("Category Review").Range("AQ131").Value
        
        'Change this to the relevant slide which has the shape
        Set PPTSlide = PPTPrsn.Slides(2)
        AppActivate "Category Review Builder"
        'Change this to the relevant shape
        Set PPTShape = PPTSlide.Shapes("Category Role")
        'Write to the shape
        PPTShape.TextFrame.TextRange.Text = "CATEGORY ROLE: " & ThisWorkbook.Sheets("Category Review").Range("AQ134").Value
        
        'Change this to the relevant slide which has the shape
        Set PPTSlide = PPTPrsn.Slides(2)
        AppActivate "Category Review Builder"
        'Change this to the relevant shape
        Set PPTShape = PPTSlide.Shapes("Category Class")
        'Write to the shape
        PPTShape.TextFrame.TextRange.Text = "CATEGORY CLASS: " & ThisWorkbook.Sheets("Category Review").Range("AQ137").Value
        
        'Change this to the relevant slide which has the shape
        Set PPTSlide = PPTPrsn.Slides(2)
        AppActivate "Category Review Builder"
        'Change this to the relevant shape
        Set PPTShape = PPTSlide.Shapes("Category Strategy")
        'Write to the shape
        PPTShape.TextFrame.TextRange.Text = "CATEGORY STRATEGY: " & ThisWorkbook.Sheets("Category Review").Range("AQ140").Value
        
        'Change this to the relevant slide which has the shape
        Set PPTSlide = PPTPrsn.Slides(2)
        AppActivate "Category Review Builder"
        'Change this to the relevant shape
        Set PPTShape = PPTSlide.Shapes("Definition")
        'Write to the shape
        PPTShape.TextFrame.TextRange.Text = "DEFINITION: " & ThisWorkbook.Sheets("Category Review").Range("AQ156").Value
        
        Application.EnableEvents = False
        PPTApp.Run MyDesktopPath & "\" & "Category Review Template.pptm!Module1.BuildPPT"
        Application.EnableEvents = True
        Application.EnableEvents = False
        PPTApp.Run MyDesktopPath & "\" & "Category Review Template.pptm!Module1.AddURLs"
        Application.EnableEvents = True
        
        AppActivate "Category Review Builder"
        Sheets("Category Review").Activate
        
        PPTCreatedFileName = ThisWorkbook.Sheets("Category Review").Range("AQ123").Value
        
        ThatWasEasy
        
        AppActivate "Circle K Category Review"
        
    End Sub

  10. #10
    try downloading it on her Local drive and running it there.
    if it runs, you know the problem is that it cannot be
    run on OneDrive.

    since it runs on Local drive, add a Code to Download the ppmt
    file from OneDrive to Local Drive and run it on local drive.

    this is somewhat same as you cannot have an MS Access db (backend)
    on OneDrive / Dropbox.

  11. #11
    Quote Originally Posted by arnelgp View Post
    try downloading it on her Local drive and running it there.
    if it runs, you know the problem is that it cannot be
    run on OneDrive.

    since it runs on Local drive, add a Code to Download the ppmt
    file from OneDrive to Local Drive and run it on local drive.

    this is somewhat same as you cannot have an MS Access db (backend)
    on OneDrive / Dropbox.
    Hey great idea! Thanks, I had thought about coding to where all files went into a common folder right off the C Drive. This might prove it out for me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •