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

dsan999

New Member
Joined
Apr 10, 2015
Messages
2
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top