Insert photo loop: How do I skip to `Next` when path is empty?

Question

I want to insert pictures in an Excel sheet next to its model name.

There is a loop to insert pictures row by row till last model.

The problem occurs when ppath is empty (blank http page). If empty the macro should go to Next.

On error resume next is crashing Excel. Error handler I tried is not working also (getting debug code on ppath).

How do I skip to Next when ppath is empty (no picture)? Here is my code:

Sub insert_foto()
Dim i As Long
Dim ppath As String
Dim lastrow As Long
Dim ws As Worksheet

Application.ScreenUpdating = False

Set ws = ThisWorkbook.Worksheets("sheet")
lastrow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

With ws
    ws.Range("A6:A" & lastrow).RowHeight = 90
End With

'On Error Resume Next - is crashing excel when ppath is empty
On Error GoTo ErrHandler    
For i = 6 To lastrow

    ' picture name in col A, ppath is where the pictures are
    ppath = "http://aa/bb/" & CStr(Cells(i, 2).Value & "-F1.jpg")
    'If ppath <> "" Then -tried this solution but not working

    With ActiveSheet.Shapes.AddPicture(Filename:=ppath, linktofile:=msoFalse, _
      savewithdocument:=msoCTrue, Left:=0, Top:=0, Width:=50, Height:=85)

        .Left = ActiveSheet.Cells(i, 1).Left + (ActiveSheet.Cells(i, 1).Width - .Width) / 2
        .Top = ActiveSheet.Cells(i, 1).Top + (ActiveSheet.Cells(i, 1).Height - .Height) / 2
        .Placement = 1

    End With
  '  End If
ErrHandler:
    'Resume Next
Next

Application.ScreenUpdating = True
End Sub

Your path is never empty which is why the check fails, so like BigBen suggests, check if the cell is empty. — Brian M Stafford
But the cells are my models list in excel and its not the case. I get the model name from CStr(Cells(i, 2).Value but some models are not uploaded to ppath (website). So i dont know if your soulution is what i mean? — Tomz
Oh... in that case this requires a different solution. Sorry, misunderstood what you meant by "when ppath is empty." — BigBen
So its when i go to website with model that is causing error i get 404 not found on page. — Tomz

FunThomas FunThomas · Accepted Answer · 2019-12-12T15:00:00

If AddPicture is asked to load an image that doesn't exist, it will throw an runtime error and of course not adding a new shape - that means it returns Nothing. To prevent the runtime error, you can insert a On Error Resume Next-statement, but please only for the one statement.

You mention that if you use On Error Resume Next, Excel crashes. With crashes, do you mean that Excel really crashes with an error message or that Excel seems to hang (no longer reacting)? If you try to access an image from the internet that doesn't exist, Excel needs to wait for a timeout and that can take some time (I had to wait for 60s for a server). If you do this multiple times, the execution may easily take a lot of time and during that, Excel will basically freeze. Adding a DoEvents will let Excel at least react after every download.

Have a look to the following code. It tries to load an image and save the result into a variable. If the loading fails, the variable will be Nothing and you know that the loading failed. As you are already using a worksheet variable, you should use that rather than ActiveSheet.

Dim sh As Shape
On Error Resume Next
Set sh = Nothing
Set sh = ws.Shapes.AddPicture(Filename:=pPath, linktofile:=msoFalse, _
        savewithdocument:=msoCTrue, Left:=0, Top:=0, Width:=50, Height:=85)
if sh is Nothing then
    ' Try an alternative image
    Set sh = ws.Shapes.AddPicture(Filename:=pPath2, linktofile:=msoFalse, _
            savewithdocument:=msoCTrue, Left:=0, Top:=0, Width:=50, Height:=85)
End If
On Error GoTo 0
DoEvents
If sh Is Nothing Then
    ws.Cells(i, 1) = "Image not found"
Else
    With sh
        .Left = ws.Cells(i, 1).Left + (ws.Cells(i, 1).Width - .Width) / 2
        .Top = ws.Cells(i, 1).Top + (ws.Cells(i, 1).Height - .Height) / 2
        .Placement = 1
    End With
End If

Insert photo loop: How do I skip to `Next` when path is empty?

1 Answers