• Automate Gathering Web Data…


    At work, we have many products that change price frequently but not regularly.  We try to be slightly cheaper than some of the ‘big boys’ in our industry but it is much too time consuming to look up the products on a daily basis.  I want to create a spreadsheet that will look up each product on the competitors web site and let me know if the price has gone up, down, or remained the same.  I believe I can create the macro to compare the current price to the previous price and indicate any change and make a recommendation as to the price change I should make on our web site.  I’m thinking I need a for next VBA loop for a list of products – one on each row.  The macro would need to go to their site, search for the product (typically each product has a page), and then somehow scrape the price.  It is easy to do that when I pull up the page but I need the process to be automated so I could just run the macro each morning. I’ve tried to use the Data…From Web tool in Excel but it looks in tables and the information I’m looking for is not in a table.  I believe I can write the VBA code if I can figure out how to get the info.  Any ideas?

    Viewing 2 reply threads
    • #2288831

      Posting for Rick Corbett, who’s having trouble posting…


      Have a look at using AutoHotkey for web automation.

      One chap – Joe Glines aka the-Automator – in particular springs to mind… ‘cos he specialises in this type of automation of web scraping.

      Check out his website, particularly the section on web scraping

      Hope this helps…

      • This reply was modified 3 years, 1 month ago by woody.
    • #2288917

      Many thanks, Woody, for your intervention.

      I didn’t get the chance to mention earlier but AutoHotkey understands/handles COM directly so can pump data into Excel spreadsheets if that’s what you need… although it sounds like you just need an event timer that checks online prices at regular intervals… which doesn’t need a spreadsheet (or log) unless you want to record a history of such changes.

      Hope this helps…

    • #2288928

      I solved my problem by doing a little research on the web.  Not all that complicated once you know what you are doing. Here is my code (edited).

      Sub getBlowoutPrices()
      Dim browser As New InternetExplorer
      Set browser = CreateObject(“InternetExplorer.Application”)
      browser.Visible = False
      ‘loop through the products in the spreadsheet
      Dim prods As Range, prod As Range
      Set prods = Sheets(“Sheet1”).Range(“A2:A52″)
      For Each prod In prods
      Dim searchProd As String
      ‘Prepare the search URL
      searchProd = LCase(prod.Value)
      searchProd = Replace(prod.Value, ” “, “-“)
      browser.navigate “https://www.xxxxx.com/” & searchProd & “.html”
      ‘code to wait until the browswer finds the page.
      Loop Until browser.readyState = READYSTATE_COMPLETE
      ‘get the value from the web page and put it in column F
      Set Doc = browser.document
      Set priceclass = Doc.getElementsByClassName(“price”)
      prod.Offset(0, 5).Value = priceclass.Item(0).innerText
      Set browser = Nothing
      End Sub

    Viewing 2 reply threads
    Reply To: Automate Gathering Web Data…

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: