Excel VBA - How to extract the image from url and embed in excel?

[nkaggarwal1]

I am trying to extract the image from url and embed in excel 2007.
My Excel is simple , it contains 2 columns , Column 1 has the image url and in other column i want the embed the image. i am using the attached code , it is working perfectly fine for first row where i have saved the image from url in my local machine and given the path but fails when extracting from url.
Error - Run time 1004 , unable to get the insert property for the picture class.


Code Used-

Sub Button1_Click()
Dim Pshp As Shape

Dim xRg As Range

Dim xCol As Long

'On Error Resume Next

Application.ScreenUpdating = False

Set Rng = ActiveSheet.Range("A2:A3")

MsgBox "Step1"

For Each cell In Rng

    filenam = cell

    MsgBox "Step2" & cell

    ActiveSheet.Pictures.Insert(filenam).Select

    MsgBox "Step3"

    Set Pshp = Selection.ShapeRange.Item(1)

    'MsgBox "Step4" & Pshp

    If Pshp Is Nothing Then GoTo lab

    xCol = cell.Column + 1

    Set xRg = Cells(cell.Row, xCol)

    With Pshp

        .LockAspectRatio = msoFalse

        .Width = 80

       .Height = 80

        .Top = xRg.Top + (xRg.Height - .Height) / 2

        .Left = xRg.Left + (xRg.Width - .Width) / 2

    End With
Set Pshp = Nothing

Range("A2").Select

Next

Application.ScreenUpdating = True
End Sub

Can this be problem with the excel vba references.

 

[nkaggarwal1]

Hi, the sheet is not protected, I tried to save the url pic at my local drive and gave the location in code and that worked. It has something to do when I am using Url and it tries to insert pic directly from url. I suppose this has something to do with Excel 2007 or vba reference parameters.

 

[pgc01]

I'm sorry, I don't know why you have that error.

I've tried the code that I posted in post #2 in excel 2010 and excel 2013 and had no problem.

I have these references set:

Visual Basic for Applications
Microsoft Excel 15.0 Object Library
OLE Automation
Microsoft Office 15.0 Object Library.

The difference is that I have the version 15 of the libraries whereas you have the version 12.

I can't test in excel 2007.

I suggest you take your file to a system with a version after 2007 and test it.

 

[nkaggarwal1]

Sure, thanks, I will give a try.