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

nkaggarwal1

New Member
Joined
Dec 9, 2018
Messages
14
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-
Code:
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.
 
Last edited by a moderator:

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,750
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:

Code:
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

New Member
Joined
Dec 9, 2018
Messages
14
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,750
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,750
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

New Member
Joined
Dec 9, 2018
Messages
14
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,750
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,750
BTW, did you try the code to insert the MrExcel logo that I posted?
Did it work?
 

nkaggarwal1

New Member
Joined
Dec 9, 2018
Messages
14
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,750
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.
 
Last edited:

Forum statistics

Threads
1,082,044
Messages
5,362,855
Members
400,696
Latest member
Kclynn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top