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]

Where did you get those checksums?

 

[gledister]

it was just an example using this program: http://www.efsoftware.com/cm/e.htm

its a md5.

But I used that just as for an example.

 

[gledister]

it checks them just if pics are in local...which is different to my problem lets say.

 

[gledister]

Hi Andrew,

Lets say I can solve on my own the checksum problem.

Do you have any idea how to put the size of the file in a column nearby?(those 3 parameters, height, width and size of the file nearby the URLs)

Kind regards.

Thanks in advance for your precious time and help.

 

[Andrew Poulsom]

Try this:

Sub InsertImages()
    Dim msXML As Object
    Dim LastRow As Long
    Dim i As Long
    Dim strFile As String
    Dim Pic As Picture
    Application.ScreenUpdating = False
    Set msXML = CreateObject("Microsoft.XMLHTTP")
    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
                msXML.Open "HEAD", strFile, False
                msXML.send
                With Cells(i, "C")
                    .NumberFormat = "0"
                    .Value = .Height * 100 & .Width * 100 & msXML.getResponseHeader("Content-Length")
                End With
            End With
        Else
            Cells(i, "B").Resize(1, 2).Value = CVErr(xlErrNA)
        End If
        On Error GoTo 0
    Next i
    Set msXML = Nothing
    Application.ScreenUpdating = True
End Sub

I don't think height and width are going to help you because they all seem to be the same size.

 

[gledister]

Hi Andrew,

Results comes like this: 150001050014742

What does that exactly mean?!

 

[Andrew Poulsom]

Height 15000 Width 10500 File size 14742.

 

[gledister]

perfect.

thanks again for your precious help.

Regards.

 

[gledister]

Hi Mr. Poulsom,


I have a list of 100 Urls containing pictures in a white background.


Right now, with your macro I can insert images, I can find their characteristics as height width and size so I am gently asking if there's any way I can find its dominant color. For example this url:
http://resources.saldiprivati.com/Sales/Frau/17112011/1_99R8 NERO.jpg

This is black. Of course I dont need as a result written black, lets say in column E but even its RGB coordinates or any other coordinate that can bring me to that colour.

Is it possible with Excel? If not, is there any way you may know that can manage that stuff?

Thank you very much in advance.

Regards.

 

[Andrew Poulsom]

Sorry, I have no idea how to find the dominant colour of an image.