1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Price and name extraction from site

Discussion in 'VBA Macros' started by aqeelll, Jan 24, 2017.

  1. aqeelll

    aqeelll New Member

    Messages:
    13
    can anyone build VBA code for name and price extraction from URLs in column A into column B and C respectively.

    best regards

    Attached Files:

  2. aqeelll

    aqeelll New Member

    Messages:
    13
  3. p45cal

    p45cal Well-Known Member

    Messages:
    760
    Needs reference to Microsoft HTML Object Library:
    Code (vb):
    Public Function GetPrice(url As String)
    Dim x(1 To 1, 1 To 2)
    Dim XMLHTTP As Object, html As HTMLDocument
    Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.setRequestHeader "Content-Type", "text/xml"
    XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
    XMLHTTP.send
    Set html = CreateObject("htmlfile")
    html.body.innerHTML = XMLHTTP.ResponseText
    x(1, 1) = html.getElementsByClassName("product_title entry-title")(0).innerText
    Set bb = html.getElementsByClassName("woocommerce-price  organique-woo-price")(0).getElementsByTagName("meta")
    'x(1, 2) = bb(1).Content & " " & bb(0).Content 'include currency designation as an alternative to line below.
    x(1, 2) = CDbl(bb(0).Content)
    GetPrice = x
    End Function
    Formula example, in 2 cells B2:C2 array-enter (Ctrl+Shift+Enter, not just Enter) this:
    =GetPrice(A2)

    See attached.

    Attached Files:

    Thomas Kuriakose likes this.
  4. aqeelll

    aqeelll New Member

    Messages:
    13
    Added another URL to extract price and name but showing #VALUE! error. Is it possible to add play or any button to initiate script?

    See attached.

    Attached Files:

  5. p45cal

    p45cal Well-Known Member

    Messages:
    760
    As soon as I opened your sample sheet the values appeared (although it showed on the last row that you array-entered only one cell at a time).
    Were you connected to the internet at the time? Does where you are block access to some sites?

    You could write a macro like:
    Code (vb):
    Sub blah()
    For Each cll In Selection.Cells
      cll.Offset(, 1).Resize(, 2).Value = GetPrice(cll.Value)
    Next cll
    End Sub
    which will put the names and prices to the right of the currently selected cells (which should contain the urls).
    Last edited: Feb 22, 2017
  6. aqeelll

    aqeelll New Member

    Messages:
    13
    What I do is just add new url on cell A5 and, in cell B5 entered =GetPrice(A5) and press CTRL+SHIFT+ENTER but showing #VALUE! error, what wrong I am doing?
  7. p45cal

    p45cal Well-Known Member

    Messages:
    760
    Nothing at all. As I said, just opening your file caused the values to show here.
  8. aqeelll

    aqeelll New Member

    Messages:
    13
    but it is showing #Value ! error to me
  9. p45cal

    p45cal Well-Known Member

    Messages:
    760
    What can I do?
    What version of Excel are you using?
  10. aqeelll

    aqeelll New Member

    Messages:
    13
    (although it showed on the last row that you array-entered only one cell at a time). what do u mean by that ?
  11. p45cal

    p45cal Well-Known Member

    Messages:
    760
    When I open your file note that the last row, the Bio Apricot Kernels is showing twice:
    upload_2017-2-22_23-40-8.png
    because you should have selected both cells at once and CTRL+Shift+Enter.
  12. aqeelll

    aqeelll New Member

    Messages:
    13
    ok. thanks a lot. BTW I am using Excel 2010.
  13. aqeelll

    aqeelll New Member

    Messages:
    13
    Code (vb):
    Sub blah()
    For Each cll In Selection.Cells
      cll.Offset(, 1).Resize(, 2).Value = GetPrice(cll.Value)
    Next cll
    End Sub
    Through this macro I can update name and price. right?
  14. p45cal

    p45cal Well-Known Member

    Messages:
    760
    So am I.
    Can you get to the websites in a browser?
  15. p45cal

    p45cal Well-Known Member

    Messages:
    760
    Yes, as I said: "which will put the names and prices to the right of the currently selected cells (which should contain the urls)."
  16. aqeelll

    aqeelll New Member

    Messages:
    13
    yeah website is accessible through browser. Can you give me the screenshot of adding formula for new URL in A7?
  17. p45cal

    p45cal Well-Known Member

    Messages:
    760
    upload_2017-2-23_0-11-9.png

    Now the formula:
    upload_2017-2-23_0-13-30.png
    now Ctrl+Shift+Enter:
    upload_2017-2-23_0-14-44.png
  18. p45cal

    p45cal Well-Known Member

    Messages:
    760
    Signing off for the night…
  19. aqeelll

    aqeelll New Member

    Messages:
    13
    Formula:

    11.png

    Ctrl+Shift+Enter:
    11.png

    3.png

    What could be the issue?
    Last edited: Feb 23, 2017
  20. p45cal

    p45cal Well-Known Member

    Messages:
    760
    Detective work required.
    1. In the same Reference dialogue box you showed above, do you have any entry such as:
    Microsoft XML v.n.0
    ?

    2. Functions don't cause an error to show up when called from a sheet, so put the following macro in the same code-module as the other code and try running it and tell me what happens (details required):
    Code (vb):
    Sub blah2()
    Dim x(1 To 1, 1 To 2)
    url = "https://demo.proteusthemes.com/organique/product/bio-crispy-flakes/"
    Dim XMLHTTP As Object, html As HTMLDocument
    Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.setRequestHeader "Content-Type", "text/xml"
    XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
    XMLHTTP.send
    Set html = CreateObject("htmlfile")
    html.body.innerHTML = XMLHTTP.ResponseText
    x(1, 1) = html.getElementsByClassName("product_title entry-title")(0).innerText
    Set bb = html.getElementsByClassName("woocommerce-price  organique-woo-price")(0).getElementsByTagName("meta")
    'x(1, 2) = bb(1).Content & " " & bb(0).Content
    x(1, 2) = CDbl(bb(0).Content)

    MsgBox x(1, 1)
    MsgBox x(1, 2)
    MsgBox bb(1).Content

    End Sub
    (It should come up with 3 message boxes containing Bio Crispy Falkes, 9 and USD.

    Depending on your answers I may try to re-write the code without using MSXML.
  21. aqeelll

    aqeelll New Member

    Messages:
    13
    Added XML reference 6.0
    Untitled2.png

    Run script showing error:
    Untitled.png

    Debug:
    Untitleda.png
    Last edited: Feb 23, 2017
  22. aqeelll

    aqeelll New Member

    Messages:
    13
    working :) Just update internet exporer. Thanks a lot, How Price and item can be update manually?
  23. p45cal

    p45cal Well-Known Member

    Messages:
    760
    I didn't ask you to add it - it shouldn't be necessary. I just wanted to know whether there was an entry for it, which would indicate to me whether MSXML was on your machine.

    You no longer need blah2; delete it.

    Now, I don't know what you mean regarding 'update manually . Latest values can be gained by just recalculating the sheet.
    If you're talking about using blah, just select the cells with the urls in and run blah again.
  24. p45cal

    p45cal Well-Known Member

    Messages:
    760
    By the way, I see you're putting code in the Sheet1 code-module; any code there will disappear when you delete the sheet. It should be in a standard code-module such as Module1. (Although I see from your last picture you might have moved to Module2!)

Share This Page