VBA Excel to create PPT: how to hide warnings in PPT
Results 1 to 3 of 3

Thread: VBA Excel to create PPT: how to hide warnings in PPT
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Excel to create PPT: how to hide warnings in PPT

    Dear all,
    I'm creating a VBA macro in excel to read data from a CSV file (containing web image link) and insert it in a PPT presentation.
    The code I wrote works. However if the image link is broken (eg. the image has been removed) I get a pop up warning in powerpoint and I need to click on "ok" to proceed with the execution of the macro.
    I would to avoid this pop up and I used "DisplayAlerts = ppAlertsNone", but it did not work.

    Here is my code, can someone help me? (I'm using office 2007)
    Thanks a lot!!!


    Sub PPImageImport()
    Dim strPresPath As String, strNewPresPath As String, strCSV As String
    Dim oPPTApp As Powerpoint.Application
    Dim oPPTShape As Powerpoint.Shape
    Dim oPPTFile As Powerpoint.Presentation
    Dim SlideNum As Integer
    Dim oPicture As Powerpoint.Shape

    Application.ScreenUpdating = False
    'some hard coded variables for this example
    strPresPath = "C:\myfolder\template.pptx"
    strCSV = "C:\myfolder\data.csv"
    strNewPresPath = "C:\myfolder\new.pptx"

    Set oPPTApp = CreateObject("PowerPoint.Application")
    oPPTApp.Visible = msoTrue
    Set oPPTFile = oPPTApp.Presentations.Open(strPresPath, msoTrue)
    SlideNum = 1
    oPPTFile.Slides(SlideNum).Select

    ' a sub to import the csv file in an hidden worksheet. Here I have hard coded everything
    ' importCSV

    oPPTApp.DisplayAlerts = ppAlertsNone

    'this image does not exist on purpose
    imgPath = "https://www.google.it/images/srpr/randomname.png"

    'the powerpoint template has a shape "IMG1" used as a placeholder
    Set oPPTShape = oPPTFile.Slides(SlideNum).Shapes("IMG1")
    On Error GoTo CantLoadPicture1

    Set oPicture = oPPTFile.Slides(SlideNum).Shapes.AddPicture(imgPath, msoFalse, msoTrue, oPPTShape.Left, oPPTShape.Top, -1, -1)


    CantLoadPicture1:
    On Error GoTo -1

    oPPTFile.SaveAs strNewPresPath
    oPPTFile.Close
    oPPTApp.Quit
    Set oPPTShape = Nothing
    Set oPPTFile = Nothing
    Set oPPTApp = Nothing

    oPPTApp.DisplayAlerts = ppAlertsAll
    End Sub

  2. #2
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,240
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Excel to create PPT: how to hide warnings in PPT

    Try validating the link before adding the picture .. something along these lines :

    Code:
    Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
            (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, _
             ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
    
    Function IsImageLinkBroken(ByVal ImgPath As String) As Boolean
    
        IsImageLinkBroken = CBool(URLDownloadToFile(0, ImgPath, vbNullString, 0, 0))
    
    End Function
    Then in your code
    Code:
    If Not IsImageLinkBroken(ImgPath)Then
        'code to add your pic goes here
    Else
        Debug.Print "Image link broken "
    End If
    Bear in mind that there seems to be a bug in excel 2007 causing the AddPicture Method to fail .. not sure about ppt
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  3. #3
    New Member
    Join Date
    Apr 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Excel to create PPT: how to hide warnings in PPT

    Thanks for the answer, but unfortunately the code you provided doesn't work. Excel crashes while processing URLDownloadToFile(0, ImgPath, vbNullString, 0, 0).
    Should I need to include a specific library??

    However if I replace vbNullString with C:\myfolder\img.jpg there is no crash, but the function IsImageLinkBroken returns always "false" (C:\myfolder\img.jpg is created even if it is not a valid image file).

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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