• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting picture from a webpage - doesn't work in a few cases

R

Roshan Valecha

New Member
Hello Everyone!

I am working on a macro that would allow me to extract pictures from a website for Price Comparison purposes.

I have managed to extract pictures from a few websites or from Google Images for example but I am having difficulties with the following website:
"http://fr.louisvuitton.com/images/is/image/lv/1/PP_VP_AS/louis-vuitton--M41746_PM2_Front view.jpg" as an example.

It seems that the webpage has to be loaded to "generate" the picture.

This is the error message I get:
"Run-time error '1004':
Insert method of Pictures class failed"

I have looked around on this forum and others, but it seems that the issue here is different to what I have found online.

These are the two different macros I tried working with:


Code: 
Public Function InsertPicSKU()


    On Error GoTo ErrHandler
    ActiveSheet.Pictures.Insert ("http://fr.louisvuitton.com/images/is/image/lv/1/PP_VP_AS/louis-vuitton--M41746_PM2_Front%20view.jpg")
    Exit Function
   
ErrHandler:
    MsgBox "Error: Photo not found for " & SKU

End Function

Code: 
Sub shape()
Sheets(1).Shapes.AddPicture "http://fr.louisvuitton.com/images/is/image/lv/1/PP_VP_AS/louis-vuitton--M41746_PM2_Front%20view.jpg" _
                          , msoFalse, msoTrue, 100, 100, 500, 600

End Sub

You will find the two macros under the document attached to this post. I am using Excel 2013.

Thank you in advance for your help.
 

Attachments

  • GetPicture_Chandoo.xlsm
    12.1 KB · Views: 3
Test the url by putting it in a cell and converting to hyperlink.

If you click on the hyperlink, it will open browser. However, you should see redirected page and not the image. This is the reason why the codes failes.

This is likely due to how Excel treats a link/cookies as opposed to the browser.

You may have to manipulate browser to get the image in this case.

See links for example of how IE is used via code.
http://stackoverflow.com/questions/17224915/download-files-from-a-web-page-using-vba-html
http://www.myengineeringworld.net/2013/11/excel-vba-download-internet-files.html
 
Hello Chihiro,

Thank you very much for your quick answer. I think I understand what you are saying but I has already tried the second solution ie downloading the file locally first - however, I get the following error message: " This file fomat can't be opened".

Concerning your first example, I am not sure how to relate that to what I am trying to do.

Do you have any suggestions on how to continue?

Thank you,
 
When you save locally, remove the space from name.

I manually saved the file from link and inserted as image on the sheet.

I'll need to do bit of research if you need further help. I don't often work with images in Excel.
 

Attachments

  • Test_JPG.xlsx
    53.5 KB · Views: 7
Thanks Chihiro,

If by space you mean "%20" - I did try that and it still didn't work. When you manually save it as a picture, it does work correctly. But as you may have guessed, I would like a solution without having to ask the macro to "right click" on the page and use the save as method.

Thank you for your help though - if you do come across anything, let me know :)
 

Hi !

In this case, download picture on local hard disk before to insert it in Excel …
​
 
Got partial success with below code. But having no success yet on bypassing SaveAs window.

Tried sendkeys but no luck.

Code: 
Sub Test()
Const OLECMDEXECOPT_DODEFAULT = 0
Const OLECMDID_SAVEAS = 4
Dim IE As Object
Dim sFile As String
Dim oWS As Object

Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://fr.louisvuitton.com/images/is/image/lv/1/PP_VP_AS/louis-vuitton--M41746_PM2_Front%20view.jpg"

Do While IE.ReadyState <> 4
        DoEvents
Loop

    IE.ExecWB OLECMDID_SAVEAS, OLECMDEXECOPT_DODEFAULT
IE.Quit
End Sub
 
@Marc L

Forgot to mention I tried variant of the "urlmon" code. However, downloaded image comes up as unreadable. Only way so far that I've managed was through manipulating IE and calling on SaveAs window.

Edit: This isn't an issue in most instances. But the link provided by OP does cause this issue. I'm guessing they either have redirect or some other code via jquery etc.
 

Just by reading the so so jpg file uploaded by URLDownloadToFile function,
initial link is not a picture file but an HTML script !
​
 
That's what I suspected. So, since we don't know direct-link to the image. SaveAs method using browser is the only method.

I'll play around bit more with sendkeys later today.
 

Just insert a one second (or two) Wait before to hit Enter key …

Roshan, better is to post initial webpage link !
​
 
Thank you both for your insights. I was worried that would be the conclusion.

Marc L, what do you mean to post initial webpage link?
 

On which webpage is the picture …

So easy way is to manually save picture on local hard disk
before to load it in Excel …
​
 
Chihiro, I was thinking of two other ways to save file on local hard drive
as piloting IE is the worst (just keep IE in last ressource when other fail) …

• Execute same request like webbrowsers do.

• Within an UserForm, load it via a WebBrowser object …

The second way seems tricky, let's try the fisrt !
Just check LFN variable before launching Demo :​
Code: 
Sub DocOpen(PathFileExt$)
    On Error Resume Next
    CreateObject("WScript.Shell").Run CreateObject("Scripting.FileSystemObject").GetFile(PathFileExt).ShortPath
End Sub

Sub RequestDownload(URL$, FILE$)
                Dim B() As Byte, F%
    With CreateObject("MSXML2.XMLHttp")
        .Open "GET", URL, False
        .setRequestHeader "DNT", "1"
        On Error Resume Next
        .send
        On Error GoTo 0
        If .Status = 200 Then
            B = .responseBody
            F = FreeFile(1)
            Open FILE For Binary As #F
            Put #F, , B
            Close #F
        End If
    End With
End Sub

Sub Demo()
    SRC$ = "http://fr.louisvuitton.com/images/is/image/lv/1/PP_VP_AS/louis-vuitton--M41746_PM2_Front view.jpg"
    LFN$ = "D:\Tests4Noobs\louis-vuitton-M41746_PM2_Front view.jpg"
    RequestDownload SRC, LFN
    DocOpen LFN
End Sub
Do you like it ? So thanks to click on bottom right Like !

Edit : see mod in post #28 …​
 
Last edited:
Neat code. And I agree with you on piloting IE is the most inefficient way to do this.

However, the way louis-vuitton site is set up, it still downloads the code instead of image :( Only success I had was with piloting IE.

Found below link which may be better than SendKeys method as I found it to be unreliable at best. Will have to try it out later this week when I have time.

http://faidootdoot.blogspot.ca/2015/04/excel-vba-automate-save-as-file-using.html
 
I do not understand 'cause on my side code of my previous post
well download louis-vuitton picture, not code !

Didn't you forget to mod LFN variable with an existing path on your side ?
"GET" request works also with cache, if you could you try after closing Excel
and maybe after restarting your computer …
Without any mod in my RequestDownload procedure ! B variable is the key …

Or you tried to open an old file and not the file downloaded by my request,
its name has only one "-" after "vuitton" …
Or mod LFN variable like "{validDrive&Folder}\Test.jpg" …

If this issue remains, I will post a mod with "POST" request
but I'll have to found out the necessary requestheaders …
​
 
Nope, LFN was adjusted to my local folder which I tested with other sites.

I emptied out the folder before I ran your code.

I'll test it out again in a moment on another computer.
 
OK, your process was right !
If request downloads code, something is missing within request …

Try this "POST" request working well on my side under Seven :​
Code: 
Sub XMLHttpDownload(URL$, FILE$)
                Dim B() As Byte, F%
    With CreateObject("MSXML2.XMLHttp")
        .Open "POST", URL, False
        .setRequestHeader "DNT", "1"
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; WOW64)"
        On Error Resume Next
        .send
        On Error GoTo 0
        If .Status = 200 Then
            B = .responseBody
            F = FreeFile(1)
            Open FILE For Binary As #F
            Put #F, , B
            Close #F
        End If
    End With
End Sub

Sub Demo2()
    SRC$ = "http://fr.louisvuitton.com/images/is/image/lv/1/PP_VP_AS/louis-vuitton--M41746_PM2_Front view.jpg"
    LFN$ = "D:\Tests\louis-vuitton-M41746_PM2_Front view.jpg"
    XMLHttpDownload SRC, LFN
    Cells(5).Select
    ActiveSheet.Pictures.Insert LFN
End Sub
 
Success!

Thanks Marc. You are a wiz as always.

Edit: To clarify, "POST" request did the trick.
 
Thanks !

If you may try RequestDownload procedure with same
.setRequestHeader codelines like in XMLHttpDownload procedure …

Which Windows version ?
​
 
Windows 10 Home, 64bit at one of the computer.
Windows 7 Professional, 64bit at another.

Tested with updated .setRequestHeader from "POST" with "GET" code. Still getting the code with "GET" and not the image.
 

Thanks Chihiro for testing !

Usually POST request needs more headers than GET method …
It's the first time I met this case, I have to remember it !
​
 
You must log in or register to reply here.
Back
Top