"The Specified File wasn't Found" image error

[ndsutherland]

I'm trying to use the following code to insert images from a list of URL's

Sub URLImage()
Dim i As Long, ws As Worksheet
Set ws = ActiveSheet


Application.ScreenUpdating = False


Columns("C").ColumnWidth = 15
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    Rows(i).RowHeight = Range("C" & i).Width
    With Range("C" & i)
        ws.Shapes.AddPicture .Value, False, True, .Left, .top, .Width, .Height
    End With
Next i


Application.ScreenUpdating = True
End Sub

I can insert the images into comments from the URL's with code
I can use the URL's manually with Insert/Pictures

Is there a security setting I need to change? Below are a few of the URL's

http://shop.vhcbrands.com/22653_300px.png

http://shop.vhcbrands.com/AW_Claire_Side_300.jpg

http://shop.vhcbrands.com/21208_300px.png

<colgroup><col></colgroup><tbody>
</tbody>

 

[Domenic]

I get an error too. Although the error I get is as follows...

Run-time error:  '1004'

The specified value is out of range.

Here's an alternative. First, declare the following...

Dim oPic As Picture

Then replace...

    With Range("C" & i)
        ws.Shapes.AddPicture .Value, False, True, .Left, .top, .Width, .Height
    End With

with

With Range("C" & i)
    Set oPic = ws.Pictures.Insert(.Value)
    oPic.Left = .Left
    oPic.Top = .Top
    oPic.Width = .Width
    oPic.Height = .Height
End With

Hope this helps!

 

[ndsutherland]

Did you get this to work with my URLs? I forgot to mention that I already tried the Pictures.Insert method as well. I get the error "Unable to get the Insert property of the Pictures class."

 

[ndsutherland]

I ended up using the following:

Sub FileImage()
Dim i As Long, FileLocation As String
Dim oHTTP As Object
    Dim sDestFolder As String
    Dim sSrcUrl As String
    Dim sImageFile As String
sDestFolder = Environ$("userprofile") & "\Desktop\VHC Images\"


Application.ScreenUpdating = False


On Error GoTo ErrHandler
Columns("C").ColumnWidth = 15
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    Rows(i).RowHeight = Range("C" & i).Width
    With Range("C" & i)
        FileLocation = sDestFolder & Range("A" & i).Value
Resumei:
        On Error GoTo ErrHandler
        .Parent.Shapes.AddPicture FileLocation, False, True, .Left + 0.5, .top + 0.5, .Width - 0.5, .Height - 0.5
        .ClearContents
    End With
Next i


Application.ScreenUpdating = True
Exit Sub


ErrHandler:
If Err.Description = "The specified file wasn't found." Then
    sSrcUrl = Range("C" & i).Value
    sImageFile = Range("A" & i).Value
    
    Set oHTTP = CreateObject("msxml2.XMLHTTP")
    oHTTP.Open "GET", sSrcUrl, False
    oHTTP.send
    
    Set oStream = CreateObject("ADODB.Stream")
    Const adTypeBinary = 1
    Const adSaveCreateOverWrite = 2
    oStream.Type = adTypeBinary
    oStream.Open
    
    oStream.write oHTTP.responseBody
    oStream.savetofile sDestFolder & sImageFile, adSaveCreateOverWrite
    
    Set oStream = Nothing
    Set oHTTP = Nothing
On Error GoTo -1
GoTo Resumei
Else
    MsgBox Err.Description
    Exit Sub
End If


End Sub

This downloads the pictures to a folder on my desktop first, and it's actually quite a bit faster.

 

[Domenic]

Actually, I had tested it with an image on a remote site of mine. Now that I've tested it with one of your URL's, I get the same errors you do for Shapes.AddShape and Pictures.Insert. In testing it, though, I noticed that sometimes the StatusBar displays the address it's trying to contact. This address differs somewhat from the specified address. Maybe this has something to do with the error.