none
Insert picture directly from OLE object (msxml2.XMLHTTP) RRS feed

  • Question

  • I have a spreadsheet with one column that contains the name of an image file from a website.  I'm trying to automate downloading the image and inserting it into the spreadsheet.  When I was putting the full URL directly into Pictures.Insert or Shapes.AddPicture, the resulting macro was very slow.  So I changed the code to use URLDownloadToFile to save the image as a temporary file, call Pictures.Insert on that temporary file and then delete the temporary file.  That made the macro much faster.  But then I came across some code that can get an image file using the URL and save it to a file using a write stream.

    My question is whether this code can be modified to directly insert the picture from the stream without having to save it as a temporary file first.

    Here's the code:

    Set oHTTP = CreateObject("msxml2.XMLHTTP") oHTTP.Open "GET", sURL, False oHTTP.send ' i'd like to skip this step Set oStream = CreateObject("ADODB.Stream") Const adTypeBinary = 1 Const adSaveCreateOverWrite = 2 oStream.Type = adTypeBinary oStream.Open oStream.write oHTTP.responseBody oStream.savetofile sfileName, adSaveCreateOverWrite oStream.close ActiveSheet.Pictures.Insert(sFileName) ' and instead do something like: ' ActiveSheet.Pictures.Insert(oHTTP.responseBody)

    ' or maybe:

    ' ActiveSheet.Pictures.Insert(oStream) instead of the savetofile


    Any ideas?  Is this even possible?


    • Edited by Kmart92 Monday, February 20, 2017 10:33 PM
    Monday, February 20, 2017 10:28 PM

All replies

  • Hi Kmart92,

    I try to test with Picture.insert method.

    Sub demo()
    Dim sURL As String
    sURL = "https://static.pexels.com/photos/27714/pexels-photo-27714.jpg"
    ActiveSheet.Pictures.Insert (sURL)
    
    End Sub
    

    I find that it is working very fast. It's performance is same as we insert the image from local path.

    I tried to insert 3.38 MB size of image from the website. it insert the image immediately.

    so here I think that the possible reason for this issue is your slow internet connection.

    so it takes some time to insert image.

    I also try to find something related with your streaming requirement, but unfortunately I did not get anything useful that can help you.

    I find one code block in python (not VBA).

    just for your Reference:

    Example: Inserting images from a URL or byte stream into a worksheet

    but if your connection is slow then the performance of streaming will also be slow.

    so I suggest you to check your connection speed.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 21, 2017 2:58 AM
    Moderator
  • Thanks for your help.  I will check out the link you gave and see if it can help.

    As far as the connection speed, I don't think that is the issue. If the problem was only due to my connection speed, I don't think there would be such a big performance difference between letting Pictures.Insert directly download the pic and downloading it with another function first.  I haven't tried using any kind of process monitor to see what is going on under the hood.  Maybe it is spawning some kind of separate process to do the downloads.  Or maybe there is something about how my system is set up, like a different operating system or different version of Office/Excel than you have.  All I know is that the difference between the two methods on my system is a lot, like more than 100 times faster.

    Tuesday, February 21, 2017 3:15 AM
  • Hi Kmart92,

    you had mentioned that,"All I know is that the difference between the two methods on my system is a lot, like more than 100 times faster."

    may I know , how much time it takes when you use picture.insert method and try to insert image from web?

    on my side, I didn't find any difference between 2 methods.

    so I inform you the possible causes for this issue.

    if possible then you can try to test it so that we can narrow down the issue.

    I don't think that different os, different version of office can be the reason for this.

    one thing you can try is that , try to download one image from web and note the downloading time.

    now try to insert the same image from web to excel using picture.insert method and note the time.

    let me know the testing results.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 22, 2017 2:46 AM
    Moderator
  • The computer this is running on has Windows 7 with Office 2010.  (Yeah, I know, pretty old stuff.) I have a large number of worksheets, probably 20-25 of them, that have a different number of pictures that need to be downloaded.  Say between 40-120 pictures per sheet.  The sheets are created from dynamic web pages so I wanted to create some macros that would allow me to update or redo the image downloads easily if any of the source pages change.

    For testing, I created a test macro that tries to download the first 4 image on a sheet and then does that 4 times, using timer function that uses QueryPerformanceCounter to time the downloads and then average that.  When inserting the pictures directly from the web, it is taking an average of 3-5 seconds per picture.  When I first download the picture using URLDownloadFile or my own function, it takes less than 10 msecs per picture.  Quite a difference! The two different pieces of code I tried timing were:

    ' CurrentTarget is target cell for picture
    ' tempName is name of image file
    ' tempPath is path of temporary directory
    ' tempURLRoot is base of URL image file        
    ' AllShapes is ActiveSheet.Shapes
    
    'Direct from web version
    With CurrentTarget
        Call AllShapes.AddPicture(tempURLRoot & tempName, msoFalse, msoCTrue, .Left, .Top, -1, -1)
        End With
    
    
    'Temporary download version
    If (MyURLDownload(tempURLRoot & tempName, tempPath & tempName)) Then
        With CurrentTarget
            Call AllShapes.AddPicture(tempPath & tempName, msoFalse, msoCTrue, .Left, .Top, -1, -1)
        End With
        Kill tempPath & tempName
    End If
    

    Just so you know, I did try using Pictures.Insert instead of Shapes.AddPicture but the results were about the same and Shapes.AddPicture lets me set the left and top positions of the image all in one call.

    Thursday, February 23, 2017 12:03 AM
  • Hi Kmart92,

    did you tried what I suggested you in my previous reply?

    if not then please try to test it one time.

    as I mentioned , create a new sample file and try to insert just 1 image from web.

    note the downloading time and size of image.

    so that I can take decision about connection and try to look further for any other possible causes for this issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 23, 2017 3:50 AM
    Moderator
  • For downloading a single image that is 14.2 kB:

    Using Picture.Insert with the url takes just over 3 seconds.

    Downloading the picture and then using Picture.Insert on the local file takes just under 10 msecs

    Saturday, February 25, 2017 9:19 AM
  • Hi Kmart92,

    you can see that your image size is very small (14.2 KB).

    then also it takes 3 seconds to insert the picture.

    whereas if you try to insert the image from the local path then it takes 10 milliseconds which is acceptable because if you try to insert the large size image then also it will not take more time then this.

    as I already tested before on my side, I try to insert 3.38 MB size of image from the website.

    and I find that it didn't take any time to insert, it inserted immediately.

    so from these all testing results I still believe that, it's taking more time because of your slow internet connection.

    there is no issue with the code or method that you use to insert picture.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 27, 2017 5:26 AM
    Moderator