VBA execution stalls while inserting a pic from a URL

[PeterBunde]

Fellow sufferers

My below code stalls somewhere between

Debug.Print "Inserting pic from URL " & strShpUrl

and

Debug.Print "Inserted pic from URL " & strShpUrl

. I am new to errorhandling, I tried to read everything there is, so whats wrong with my code?

The sub should be run about 300 times to get to the end of my VBA code, but it stalls at random instances of running that sub. There is no pattern. I suppose it is the server behind the URLs used that is unstable.

What should I do to get the code to just not insert the pic if something goes wrong?

BW Peter Bunde Hansen

Sub GetShapeFromWeb(strShpUrl As String, rngTarget As Range)
    Dim Shp As Shape
    
    On Error GoTo ErrHandler
    
    Debug.Print "Inserting pic from URL " & strShpUrl
    
    If strShpUrl <> "" Then
 
    With rngTarget
        With .Parent
            .Pictures.Insert strShpUrl
            Set Shp = .Shapes(.Shapes.Count)
        End With
        Shp.Left = .Left
        Shp.Top = .Top
    End With
    
    Debug.Print "Inserted pic from URL " & strShpUrl
    
    Shp.Select
    
    FitPic
 
    Set Shp = Nothing
    
    End If
    
Abort_insert_pic:
    
Exit Sub
    
ErrHandler:
    
    
    Debug.Print "GetShapeFromWeb: " & strShpUrl & " - " & rngTarget
    
    Resume Abort_insert_pic
End Sub

 

[PeterBunde]

Update: my theory is, the server does not reply, and then my code of cause stalls. Is there a way to set a timeout or else, so that the VBA code errors instead of just stopping?

 

[John_w]

You could use the Windows API function URLDownloadToFile to download the picture file to a local folder, then specify that local file as the Pictures.Insert argument.

 

[PeterBunde]

You could use the Windows API function URLDownloadToFile to download the picture file to a local folder, then specify that local file as the Pictures.Insert argument.

Hi John thanks for answer

I would prefer to keep everything within the Excel sheet. A lot of people will be using this sheet and I dont want to save anything on their PC and I cannot assume they have access to our intranet. Possibly, I can insert it on a hidden page, then copy it into the desired position. Any other suggestion?

I did actually find a (poor) workaround to the VBA code stalling, and that was to have the code save the sheet every time it had inserted a picture. Apparnetly, saving the sheet makes it more stable. But it takes a lot of time. I had this instability problem for months now, it must be a Microsoft bug somewhere.

BW Peter Bunde Hansen

 

[John_w]

In that case download, insert and delete the file.

Edit - that probably won't work because the picture object must point to a file which exists.