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.

 

[pgc01]

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]

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 lineSet pic = ActiveSheet.Pictures.Insert(sAddr)

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

Kindly advise.

Thanks,

Nishant.

 

[pgc01]

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]

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]

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]

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]

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

 

[nkaggarwal1]

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]

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.