Multiple Web Queries

[Chipless]

I have a table of zip codes (column F) for which I need the associated crime rates populated (columns H and I):

Screen Shot 2015-06-27 at 3.43.24 PM.png

So far, I've created a web query in another worksheet that returns the crime rate for whatever zip code is entered (cell B1) as follows:

Screen Shot 2015-06-27 at 3.45.53 PM.png

What would be the most efficient way to populate the values of cells B5 and B6 from the above query into the appropriate columns within the zip code table for each different zip code?

[ptmuldoon]

I'm by far no expert. But could you set the Range in Column F (Zipcode), and then loop through that range getting the crime rates you need, and then use a Cell.Offset to place the returned values in columns H and I?

[Chipless]

I'm by far no expert. But could you set the Range in Column F (Zipcode), and then loop through that range getting the crime rates you need, and then use a Cell.Offset to place the returned values in columns H and I?

Thanks for the response. How would I loop through the range? The web query uses the zip code in cell B1, and places that zip code at the end of a URL string that it queries. So, if I set the query to lookup a range instead of a single cell, it ends up trying to pull an invalid URL

[ptmuldoon]

Thanks for the response. How would I loop through the range? The web query uses the zip code in cell B1, and places that zip code at the end of a URL string that it queries. So, if I set the query to lookup a range instead of a single cell, it ends up trying to pull an invalid URL

This is pretty basic but may help explain. And probably better to set a Named Range of the ZipCode Column. But the below should help in understanding how to set a range for the zipcode column, loop through it, and append the zip code value to your url.

Sub LoopZips()
    Dim rCell, Zips As Range
    Dim WQuery As String
    
    WQuery = "http://www.mywebquery.com?zipcode="
    Set Zips = Range("C2:C10")
    
    For Each rCell In Zips
        MsgBox WQuery & rCell.Value
        
        'Put the Crime Rates 2 cells right
        rCell.Offset(0, 2).Value = "Violent Crime Info"
        rCell.Offset(0, 3).Value = "Property Crime Info"
    Next rCell

End Sub

You can also probably get some better help in VBA forum, or maybe a Mod can shift this topic there. And any sample code or file would be helpful too.

[Chipless]

This is pretty basic but may help explain. And probably better to set a Named Range of the ZipCode Column. But the below should help in understanding how to set a range for the zipcode column, loop through it, and append the zip code value to your url.

Sub LoopZips()
    Dim rCell, Zips As Range
    Dim WQuery As String
    
    WQuery = "http://www.mywebquery.com?zipcode="
    Set Zips = Range("C2:C10")
    
    For Each rCell In Zips
        MsgBox WQuery & rCell.Value
        
        'Put the Crime Rates 2 cells right
        rCell.Offset(0, 2).Value = "Violent Crime Info"
        rCell.Offset(0, 3).Value = "Property Crime Info"
    Next rCell

End Sub

You can also probably get some better help in VBA forum, or maybe a Mod can shift this topic there. And any sample code or file would be helpful too.

Thank you so much. Please forgive my total ignorance when it comes to VBA/macros (I wasn't even sure if I needed a macro to pull off what I wanted to do), but what do I need to change in your code? I changed the URL, but what should I change the value= to?

[ptmuldoon]

Thank you so much. Please forgive my total ignorance when it comes to VBA/macros (I wasn't even sure if I needed a macro to pull off what I wanted to do), but what do I need to change in your code? I changed the URL, but what should I change the value= to?

Can you provide the file you are working with? We need to see the web query and the corresponding results being generated to be able to loop through it.

[Chipless]

Can you provide the file you are working with? We need to see the web query and the corresponding results being generated to be able to loop through it.

My apologies, I should have provided these from the start. Thank you so much for your help.

Here is the query I created:Screen Shot 2015-06-28 at 5.17.42 PM.png


And here is the result when a zip code is entered in (note, I only need the data in cells B5 and B6):
Screen Shot 2015-06-28 at 5.18.11 PM.png

[ptmuldoon]

Ok,

What your asking is going to be beyond my limited skills. I've just begun learning how to pull data from the web. And I'm not sure if what your asking is going to be possible from that specific website as I do not see an html elementID for those values to reference and pull from.

Someone else may have another suggestion or approach. Or possible the same data may be accessible from another website that has a free API, etc to reference and get pull the info.

Essentially, you want to loop that web query. But it also appears that you can't just add the zipcode to the URL, and also need to know the state and town.

But don't give up hope yet. This may still be workable. Just needs some sharper minds on it.

I just noticed your using a Mac as well. And I think Excel behaves a little differently on a Mac as well vs a PC. So we may need some specific help in that direction as well.

[Chipless]

Ok,

What your asking is going to be beyond my limited skills. I've just begun learning how to pull data from the web. And I'm not sure if what your asking is going to be possible from that specific website as I do not see an html elementID for those values to reference and pull from.

Someone else may have another suggestion or approach. Or possible the same data may be accessible from another website that has a free API, etc to reference and get pull the info.

Essentially, you want to loop that web query. But it also appears that you can't just add the zipcode to the URL, and also need to know the state and town.

But don't give up hope yet. This may still be workable. Just needs some sharper minds on it.

I just noticed your using a Mac as well. And I think Excel behaves a little differently on a Mac as well vs a PC. So we may need some specific help in that direction as well.

Thank you for the quick response yet again. The URL still returns the data as desired if ONLY the zip code is changed at the very end of the URL (the state and city can remain constant, so I arbitrarily selected Denver, CO since it doesn't affect the results of the query). Does that make things easier?

[ptmuldoon]

It may help, but others may know better.

So the city/state in the URL actually doesn't matter. It could be for any location, and web query result seems to only look at the Zip code and get the results for that location.

[Chipless]

So the city/state in the URL actually doesn't matter. It could be for any location, and web query result seems to only look at the Zip code and get the results for that location.

That is correct.

[Chipless]

This is still unsolved, so if anyone has the time to respond, I'd pay you in eternal appreciation.

[Chipless]

Still need help.

[Chipless]

This still remains unsolved, so please help if you can!

Edit: Just noticed that the query code screenshot link from post #7 is broken:
Query Code.png