+ Reply to Thread
Results 1 to 2 of 2

[SOLVED] Best Way to Query Web Page Based on Cell Value in Excel for Mac

  1. #1
    Registered User
    Join Date
    06-26-2015
    Location
    DENVER, CO
    MS-Off Ver
    EXCEL 2011 FOR MAC
    Posts
    13

    [SOLVED] Best Way to Query Web Page Based on Cell Value in Excel for Mac

    Hi all,

    I've been beating my head against the wall trying to come up with a solution to this problem, so hopefully someone can point me in the right direction.

    In the end, I'm trying to create a script/formula/macro/query (something automated) that can retrieve crime statistics based on some data from a table within a worksheet and then place that crime statistic in the same row within the table that the source info came from. Here's a screenshot of the source table:

    Screen Shot 2015-06-26 at 4.41.38 PM.png

    I've been trying to setup a web query from http://www.bestplaces.net/crime/zip-...o/denver/80211 (<---example of a full URL) and setup parameters so that the portions after zip-code/ are automatically generated based on the info in the aforementioned table. So far, I've been unsuccessful in executing anything but a static web query. Below, you will see the query that works, as well as the resulting data:

    Screen Shot 2015-06-26 at 4.51.24 PM.png

    Screen Shot 2015-06-26 at 4.52.54 PM.png

    Ideally, I'd like to set something up that could retrieve the values in cells B4 and B5 and also be run for multiple rows (e.g. locations) from the source table.

    Any suggestions? I apologize for being a novice in this area. An entire day's worth of internet research coupled with trial and error yielded nothing beyond what you see above.
    Attached Images Attached Images
    Last edited by Chipless; 06-27-2015 at 04:30 PM.

  2. #2
    Registered User
    Join Date
    06-26-2015
    Location
    DENVER, CO
    MS-Off Ver
    EXCEL 2011 FOR MAC
    Posts
    13

    Re: Best Way to Query Web Page Based on Cell Value in Excel for Mac

    Solved the first part of this by building a web query with only one dynamic parameter. The query returns the appropriate data after entering in a zip code. Will be posting to the general forum as the next part is not specific to VBA/macros (hopefully). For those that are curious, here is the query that works (thankfully, the website still returns the correct data even if the city and state are left constant in the URL):
    Screen Shot 2015-06-27 at 3.33.48 PM.png
    Last edited by Chipless; 06-27-2015 at 04:34 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel script to log into Web page for my Web query
    By vitt4300 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-20-2015, 02:12 PM
  2. Excel VBA query time based hyperlink query
    By TAU710 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-13-2010, 03:27 PM
  3. how excel web query(*.iqy) handle the login page
    By sleepy_11 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-26-2008, 04:55 AM
  4. [SOLVED] using excel to query asp page
    By Lilivati in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-05-2006, 12:40 PM
  5. Replies: 3
    Last Post: 06-02-2006, 05:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1