News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Automate Gathering Web Data…

    Posted on WSjpzinn Comment on the AskWoody Lounge
    • This topic has 2 replies, 3 voices, and was last updated 1 month ago.
    Viewing 3 reply threads
    • Author
      • #2288253 Reply
        AskWoody Lounger

        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?

      • #2288831 Reply
        Da Boss

        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 1 month ago by woody.
      • #2288917 Reply
        Rick Corbett

        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 Reply

        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 “” & 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 3 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Automate Gathering Web Data…

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