Insert images from web to excel sheet

[gledister]

Hi guys,

Imagine we have a huge list of URLS from web like in this example:

Excel Workbook
	A
1	http://resources.saldiprivati.com/Sales/Mamaquevo/09032010/1_UQ20718%20NERO%202000.jpg
2	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Man/11032010/1_MARKD1021%20UK440BS0934%2034.jpg
3	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Man/11032010/1_RT%20MATHISG2319%20GE000BE9434%2034.jpg
4	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Man/11032010/1_RT%20MATHISG2319%20RW000NE9034%2034.jpg
5	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Man/11032010/1_RT%20MORGANG2122%20GE000VS7534%2034.jpg
6	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Man/11032010/1_WILSON%20AS6530%20GE000VI1834%2034.jpg
7	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Man/11032010/1_RT%20AUSTIN%20DJ9108%20RW000NE90.jpg
8	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Woman/12032010/1_ERIKAD1343%20OD000DMVC34%2034.jpg
9	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Woman/12032010/1_MARLYND1048%20CH651BS0830%2030.jpg
10	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Woman/12032010/1_MEGRITD1072%20UK370BF0832%2032.jpg
11	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Woman/12032010/1_MENDEL%20AG2256%20WW000BI0132%2032.jpg
12	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Woman/12032010/1_MENDEL%20AS3220%20RW001BI0032%2032.jpg
13	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Woman/12032010/1_MESHD1073%20UK422BF0834%2034.jpg
14	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Woman/12032010/1_MESHD1268%20UK521BS0834%2034.jpg
15	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Woman/12032010/1_NATASHA%20AD1120%20UK110DMBL34%2034.jpg
16	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Woman/12032010/1_NICOLED1239%20UK418BS0934%2034.jpg
17	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Woman/12032010/1_ROBIND1525%20UK484BF0834%2034.jpg
18	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Woman/12032010/1_RT%20EMILYD1182%20UK380DMBL.jpg
19	http://resources.saldiprivati.com/Sales/Meltin%20Pot%20Woman/12032010/1_RT%20MEDORID1294%20UK420BF0930%2030.jpg
URL

What we wanna do is to insert in column B the pictures from the web.

I have this code(from Dynamic) that inserts images from a specific folder on hard drive...

Sub InsertImages()
Dim strPath As String
Dim strFile As String
Dim LastRow As Long
Dim i As Long
Dim Pic As Picture

Application.ScreenUpdating = False

strPath = "C:\Users\Domenic\Documents\Test\" 'change the path accordingly

If Right(strPath, 1) <> "\" Then strPath = strPath & "\"

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To LastRow
strFile = Cells(i, "A").Value & ".jpg"
If Dir(strPath & strFile) <> "" Then
Set Pic = ActiveSheet.Pictures.Insert(strPath & strFile)
With Pic
.Left = Cells(i, "B").Left
.Top = Cells(i, "B").Top
.Height = Cells(i, "B").Height
End With
Else
Cells(i, "B").Value = "N/A"
End If
Next i

Application.ScreenUpdating = True

End Sub

Any idea on how I can improve this macro code to be useful for this specific problem?!

Kind regards and thank you in advance for you precious time.

 

[Andrew Poulsom]

This worked for me:

Sub InsertImages()
    Dim LastRow As Long
    Dim i As Long
    Dim strFile As String
    Dim Pic As Picture
    Application.ScreenUpdating = False
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To LastRow
        strFile = Cells(i, "A").Value
        Set Pic = ActiveSheet.Pictures.Insert(strFile)
        With Pic
            .Left = Cells(i, "B").Left
            .Top = Cells(i, "B").Top
            .Height = Cells(i, "B").Height
        End With
        Next i
    Application.ScreenUpdating = True
End Sub

 

[gledister]

It works great for me too!

Thank you!

 

[gledister]

Hi Andrew,

The macro works GREAT but it stops when it doesn't find a pictures.

Is there any way to let it continue working and putting an N/A in case it doesn't find it?

Thanks in advance.

Kind regards.

 

[Andrew Poulsom]

Try:

Sub InsertImages()
    Dim LastRow As Long
    Dim i As Long
    Dim strFile As String
    Dim Pic As Picture
    Application.ScreenUpdating = False
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To LastRow
        strFile = Cells(i, "A").Value
        On Error Resume Next
        Set Pic = ActiveSheet.Pictures.Insert(strFile)
        If Err = 0 Then
            With Pic
                .Left = Cells(i, "B").Left
                .Top = Cells(i, "B").Top
                .Height = Cells(i, "B").Height
            End With
        Else
            Cells(i, "B").Value = CVErr(xlErrNA)
        End If
        On Error GoTo 0
    Next i
    Application.ScreenUpdating = True
End Sub

 

[gledister]

Its the coolest thing ever!

Thank you very very much!

 

[gledister]

Hi,

am turning back on this post because I have one other question.

I am extracting lots of pictures from the web and I was curious to know if there is any possibility to put the characteristics of each picture(like size of each) in a column nearby, lets say in column C.

Furthermore, due to the fact that I noticed that different URLs have same pictures, wanted to know if its possible to do a checksum of each of these pictures am extracting. Putting the checksum lets say in a column nearby(D column) so I can find which of them are identic.

Any idea?

Regards.

 

[Andrew Poulsom]

What do you mean by size? Height and width or size of the file?

What sort of checksum were you thinking of?

 

[gledister]

Hi Andrew,


1. All three of them: height, length and size(mb) of the file itself.

2. Any that fulfills my objective, to determine which of them are identic.

example:
http://www.mrexcel.com/forum/thanks.jpeg
http://www.mrexcel.com/forum/thanks2.jpeg

They seem to be 2 different pictures but in fact they are the same picture.

any idea?

 

[gledister]

let me be more specific:

example:

<TABLE style="WIDTH: 238pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=317><COLGROUP><COL style="WIDTH: 238pt; mso-width-source: userset; mso-width-alt: 11593" width=317><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: aqua; WIDTH: 238pt; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=15 width=317>http://images3.eprice.it/images/Prodotti/100/34/n3260434.jpg</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: aqua; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=17>http://images4.eprice.it/images/Prodotti/100/84/n3256484.jpg</TD></TR></TBODY></TABLE>

both of them gives this result after checksum:
<TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=203><COLGROUP><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7424" width=203><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 152pt; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=15 width=203>015dfb6dfd91c4a3c009e48f1f8b8988</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=203><COLGROUP><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7424" width=203><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 152pt; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=15 width=203>015dfb6dfd91c4a3c009e48f1f8b8988</TD></TR></TBODY></TABLE>

So that means these files are identic.