Data > Get External Data > From Web IN BULK

[jordanpendergrass]

I am using Excel 2007.

I know how to import into Excel from the web using Data > Get External Data > From Web. But I need to do this process 1,000 times. I will always be going to the same website to make my query, but each time I go to the website, I need to query a different number that is in my spreadsheet. My 1,000 pieces of data are arranged in a column, from A1:A1000.

For example, let's say I have 1,000 postal codes and I want to know the current temperature for each postal code. That way, I could refresh my page and always have the current temperature in 1,000 cities

I could do a data import for each postal code one-by-one, by 1) going to Get External Data > From Web 2) open a weather-related website in the import search window 3) type in the postal code 4) choose the desired field (yellow box with black arrow) 5) click import to finish the process.

BUT, that would take too long. I want to run the process automatically for each value in A1:A1000, which in the example was a postal code. All that changes in the data import is the value I query on the web. The website stays the same.

Is there a shortcut?

Thank You.

PS, I was directed to this forum from ExcelIsFun! I hope you can help!

 

[jc.021286]

Hi there,

a couple ways of doing this, though with that many I think you would be best doing a quick script in VBA.

The website that you are using should have a methodology that it uses in the address.

You should see something akin to
http://www.address.com/param1_H0H0H0.html

Something similar to the above where you can see your postal code or other item that is being searched for.

This should work fairly easy.
All we do is run the query and want to swap out those values.
Do a record macro to record your actions importing the data once.

stop recording and hit Alt+F11, in the code we will do 4 things
1) set up a loop
2) choose the postal code
3) update the address to query
4) update the place to put the new query.

For each item:
with the code, you will add the bolded
1)
Sub Macro_1()
'
'
For a = 1 to 1000
'your web query will be here
'you should see "URL;http://...." Destination:=Range("$A$1"))
'
'
Next a

--- now we will have it run through the code 1000 times

2) If postal codes are from A1:A1000
we'll have after the "For a = 1 to 1000" this line
PostalCode = Cells(a,1)

--- thus when "a" changes we will set the postalcode equal to row "a" column 1 (thus for a = 1 to 1000 we will go through A1:A1000)

3) need to change the URL;http:www... that is used
the portion that changes is the postal code, so edit that line to be take out the postal code and sub in your variable
"URL;http:www.weatherreport.com/something_p=" & postalcode & ".html"

--- we break it apart by ending the address short with a " and use "& postalcode &" to insert our variable, and then resume by inserting any ending section of the web address such as a ".html"
--- quotations are very important

4) Currently it will cycle through everything, GREAT!, though it will continuously overwrite your data, we can set it to space itself appart when adding the new query. If your query returns a box that is 10 rows by 2 columns we will place the 1000 query tables down the rows spacing them by 12 spaces from the previous.

We change the destination from being Destination:=Range("$B$1") to
Destination:=Cells(a*12-11,2)

this will place it every 12 cells.

You should be done,
Try following the above post as best you can and return with the code to ensure that it works. (side note: you may wish to delete named ranges as too many named ranges can make your file rediculously large and slow)

Hope this helps,
Regards,
jc

 

[jordanpendergrass]

Thank you so much. I am still trying your code. What are the other options if vba does not work? I would like to learn all the ways.

 

[jc.021286]

depends on the information and how it is layed out.

Other options,
Preset up your 1000 charts manually and set them to reload / update on open (takes a lot of time, only good time wise if < 25 items)

Some options on various websites may allow you to look and compare, so temperature, you may be able to request to see temperatures in 10 cities at once and you would pick up that information.

Pay a younger sibling to do the work.

Contact the website admin and ask if they have a database that you could use, they must have the information in some form already on hand and that stores historical information. Sometimes you can ask nicely, (don't hold your breath, only worked on two occasions for me to get an access database from a website of interest)

I think there was an addon for firefox that I saw at one point where you could essentially save a webpage, and it would follow all the links to save those webpages and so on and so forth (took up a lot of space). This was to hopefully increase the speed at which material could be loaded as it would preload the information.

That's about it that I can think of
jc