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



## nkaggarwal1 (Dec 9, 2018)

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.


----------



## pgc01 (Dec 9, 2018)

Hi

Maybe you can post one of your url's so that we can try.

This is a simple example you can try with MrExcel logo:


```
Sub Test()
Dim sAddr As String
Dim pic As Picture

sAddr = "https://www.mrexcel.com/forum/images/misc/mrexcelvb_logo.png"
Set pic = ActiveSheet.Pictures.Insert(sAddr)

With pic.ShapeRange
    .LockAspectRatio = msoFalse
    .Width = 80
    .Height = 80
End With

End Sub
```


----------



## nkaggarwal1 (Dec 10, 2018)

Thanks PGC , I tried this in fresh xls , but got the same error 

*Error - Run time 1004 , unable to get the insert property for the picture class. 

**The error occured at below line*Set pic = ActiveSheet.Pictures.Insert(sAddr)

Can this be problem with the VB refrences?. My current references are-







Kindly advise.

Thanks,

Nishant.


----------



## pgc01 (Dec 10, 2018)

Hi

Did you read your post?

1 - You cannot see the references
2 - even if you could see them, it would be a picture. How do you get the references out of a picture?

Please post the references so that we can try.


----------



## pgc01 (Dec 10, 2018)

P. S.

Maybe you can see the picture because your pc has access to the file?

C:\Users\gzb\Desktop\Monti\Final\Finalest\Reference.png

We, however, don't.

Please post the references as text.


----------



## nkaggarwal1 (Dec 10, 2018)

Sorry , Please find the details below-

Excel version - 2007

References in order-

Visual Basic for Applications
Microsoft Excel 12.0 Object Library
Microsoft Internet Controls
Microsoft HTML Object Library
OLE Automation
Microsoft Office 12.0 Object Library.

Kindly Advise,

Thanks!!

Nishant.


----------



## pgc01 (Dec 10, 2018)

I'm sorry, this was a misunderstanding.

I mean the url reference to one of your images, so that we can try the code to insert it in the worksheet.


----------



## pgc01 (Dec 10, 2018)

BTW, did you try the code to insert the MrExcel logo that I posted?
Did it work?


----------



## nkaggarwal1 (Dec 10, 2018)

Hi PGC,  I tried your code only in fresh new excel and faced the same issue.  If that works I hope my url will also work.  Kindly advise.


----------



## pgc01 (Dec 10, 2018)

nkaggarwal1 said:


> Error - Run time 1004 , unable to get the insert property for the picture class.



Are you sure that the worksheet is not protected?
This would be the error displayed if your try to insert the picture in a protected worksheet.


----------



## nkaggarwal1 (Dec 9, 2018)

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 (Dec 10, 2018)

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 (Dec 10, 2018)

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 (Dec 12, 2018)

Sure,  thanks,  I will give a try.


----------

