Insert the first image found with image search (e.g google) with criteria taken from cell.

Hi all
So I am making a spreadsheet that inserts an image based on what cell B2 contains e.g I put in salmon, hopefully I get an image of a salmon. So far I have the following

Dim URL As String
URL = "https://www.google.co.in/search?q=" & Cells(2, 2) & "&source=lnms&tbm=isch&sa=X" & "&rnd=" & WorksheetFunction.RandBetween(1, 10000)
Sheets(1).Shapes.AddPicture URL _
, msoFalse, msoTrue, 100, 100, 50, 50

Now the URL gets me the image result page for Google images but that's not good enough, I need the first result from the search (or the second or third- it doesn't really matter which one, it only matters that I get a result at all). The second line inserts the image as needed but without a working link, its all for nothing.

I don't need to use Google images- I am happy to use any search engine that will return me a image related to the search criteria. Ideally I would just use the built in online pictures part of excel but recording a macro with that doesn't work (it just points at the temp image file that is created).

Any ideas would be gratefully received.
Many thanks

duplicate post http://www.excelforum.com/excel-prog...using-vba.html

Oh, I had not spotted that. Thanks I will bump that thread instead.

hi

check this code

Please Login or Register  to view this content.

Cheers!

My earlier code was not fetching first image, use this to fetch..

Requirements In Column A write name of object for which you want image search, In B column you will get URL of source image & in C column you will get Image matching size of your cell, you can re-size your C column to make your image bigger or smaller before running macro..

Please Login or Register  to view this content.

Cheers!!

Well this solves everything, thanks so much guys. I suspect it will take me a while to understand how the code works but both of you have provided working solutions for something the internet didn't seem to know how to do so hats off! Thanks again

Repeat of earlier..Ignore

Originally Posted by themob212

Well this solves everything, thanks so much guys. I suspect it will take me a while to understand how the code works but both of you have provided working solutions for something the internet didn't seem to know how to do so hats off! Thanks again

Great this worked for you, Cheers!!

Hi everyone, thank you very much for your help! the code is great but I get an error

I think the error is due to the format %33%A... of the fetched link: http%3A%2F%2Fweknowyourdreamz.com%2Fimages%2Fbaby%2Fbaby-02.jpg

can you help please?

Originally Posted by Vincent121

Hi everyone, thank you very much for your help! the code is great but I get an error

I think the error is due to the format %33%A... of the fetched link: http%3A%2F%2Fweknowyourdreamz.com%2Fimages%2Fbaby%2Fbaby-02.jpg

can you help please?

what keyword you tried?

In the example I posted I tried with "baby" keyword. I found a workaround by coding the following piece of code but it seems to create some errors 10-15% of the time:

Please Login or Register  to view this content.

See revised code you will get proper decoded url:

Please Login or Register  to view this content.

Cheers!!

wow thank you! that's the first time I ask anything on a forum and I didn't expect such a fast and accurate reply
it works perfectly!

do you know how if a macro exist to delete all the pictures?

Originally Posted by Vincent121

wow thank you! that's the first time I ask anything on a forum and I didn't expect such a fast and accurate reply
it works perfectly!

do you know how if a macro exist to delete all the pictures?

Oh thats amazing..

try below code to clear all images, drawing objects

Please Login or Register  to view this content.

If you only want to delete pictures do buzz

Cheers!!

working like a charm, thanks a million

have a good day

Thats great.. good day, keep posting.

Hi

I used this code last week and it worked well.

However, this week for the same search terms i am now never getting a match for the "furl = InStrRev(url2, "&imgrefurl=", -1)". the aElement.href doesnt seem to contain "&imgrefurl=" in any of the searches?

Has something changed in the IE language?

Thanks

Nigel

This is an awesome script!

I get complie error 5 with this code, can anybody help me?

Thanks

Besides Microsoft Internet Control and HTML object library should i add any other reference? I am using this code on a database of 2500 items. After 200 its giving me error "Application defined or object defined error"? is there anything I am doing wrong??

Hi friends

I found a VBA code to help me Insert the first image found with image search (e.g google) with criteria taken from cells. This code from 'excelliot' help me so much things, but can you help me to solve the problem that "if a picture is not found, or the keyword mismatch with google image search result, or there is some error that 1st picture, the code will ignore the 1st picture and will picked 2nd picture to replace the 1st one" and also if the error still there with the 2nd picture, the code will ignore the error cell value and continue with the next value? Because I got the error code:

Please Login or Register  to view this content.

An when I clicked on to Debug button, it's pointed to this line:

Please Login or Register  to view this content.

Thank

I found the problem came from source of pictures. Some source pictures from Amazon, Amazonaws, shopify... cant fetch the pictures and the code result error. If I add the exclude keywords in the code, the code will work without any problem. Like this:

Please Login or Register  to view this content.

to exclude the result from amazon.com and amazonaws, I added: "+-amazon+-amazonaws" to "&source=lnms&tbm=isch&sa=X&rnd=1", that will be:

Please Login or Register  to view this content.

Can we code the max request time for download picture, in case if the pictures cannot finish download in 20 seconds will be skip to the 2nd link...? Because sometimes I waited to download a picture cost about 5 6mins but we can save a lot of time with the 2nd link, it cost only 5secs. Thank

Thank to notice, I will read and follow the rules.

Is there anybody who has an Excel, where it works?
I've tried to apply it here, but it doesnt work.

So if anybody has a spreadshet where it works, please send it

Im also looking for the same solution.

Row A Row B
Tesla p-85 (picture of it)

ETC

Im getting Run-time error '5': Invalid procedure call or argument

furl = Mid(ur12, 40, furl - 40)

hi! sorry, could you please post a working worksheet with this code? i just cant make it

Same, get an error at furl with Run-time error '5': Invalid procedure call or argument