Excel VBA : Copy image from website into Excel

Cause:

Wondering how to copy image from a internet source and paste to Excel using the VBA.

There are many examples on the internet if you google "copy image from web vba". However, those are too complicate as I just want to copy from a particular website and particular one image.That's it. No more other things.

So I keep searching on the internet to find a proper method.

One of the method is using "URLDownloadToFile API", it is too complicate for me as I am just a beginner of VBA.....

Then I find a good example from stactoverflow. By using AddPicture(x,x,x,x,) method, it is very easy to copy a image to Excel!

However, you must know the URL of the image first...

Luckily, I know the basic DOM knowlege through my studies on Javascript.

Action:

How to know the URL of the source of the particular image:

Take my blog as example.
Want to copy the below image on this website.


1. Go tohttp://exp4life.blogspot.hk/ using IE
2. Check the above element by left click, then "check element". Then we got this.



3.Ha, that is a little complicated... I need to find div elements and then find  <img> tag.

So, using getElementById("Profile1").getElementsByTagName("img").


Finally, [elemnet].src, will give us the URL!

Using AddPicture(x,x,x,x,) method

Once we go the URL, everything will be very easy. Just using the AddPicture method.

The Code: 

Sub findURL2()

'

'





   Dim src As String

    Dim IE As Object

  

    Dim objCollection As Object



    ' Create InternetExplorer Object

    Set IE = CreateObject("InternetExplorer.Application")



    ' Don't wnat to see IE when the code is running

    IE.Visible = False



    ' Set IE to go to the target webpage

    IE.Navigate "http://exp4life.blogspot.hk/"

' Need to wait the IE for loading

 Do While IE.Busy

        Application.Wait DateAdd("s", 1, Now)

    Loop

  

    'Find the target element

   Set objCollection = IE.document.getElementById("Profile1").getElementsByTagName("img")

    'Get the URL

     src = objCollection(0).src

    

  

    

      Dim R As Range

      Set R = ActiveSheet.Range("A1")

    Call ActiveSheet.Shapes.AddPicture(src, msoFalse, msoTrue, R.Left, R.Top, -1, -1)





Set IE = Nothing



End Sub

Result: 

Good. Can get the result as expect.

Notes:

Sometimes, it got Error 91 and Error 424.

Error 424>>> Seems IE can't find the element and will throw this error.
Error 91>>>still don't know why......

Reference:

good example
[1]http://stackoverflow.com/questions/8993154/can-i-use-vba-to-import-images-gifs-from-the-web-into-excel