On Fri, 19 Jun 2009 07:14:02 -0700, Jason
wrote:
Ron,
I changed
Range("A2").CurrentRegion
to
Range("A3838").CurrentRegion
and it started putting temperatures in B1, C1, D1, and then on down instead
of B3838, C3838, D3838 and then down.
And what, exactly, is the range that results from that command? Take a look at
HELP for the CurrentRegion property and you may be able to figure out for
yourself what's going on and how to fix it. Or else you'll have to provide
more details of your setup.
My last request, is more of a wish, and not an urgent need, but I want to
setup a sheet with a list of city airport codes, for example:
Sheet name "Airports"
In "Airports" Cells B1-B25 would contain a list of 25 airport codes in a
specific order
A modified version of your existing script would then
-take the airport code in cell B1, likely NYC as we have already done
-insert it into the website address for sURL1
http://www.wunderground.com/history/airport/k
-run your script, inserting the temps in B*,C*,D*
-then read the next airport from "Airports" B2, likely LAX
-modify the sURL1 to
-http://www.wunderground.com/history/airport/kLAX
-insert the temperatures into E*,F*,G*
Essentially column A is the date, then next 3 columns are the Max, Min, Mean
for City 1, then the next 3 columns are the Max, Min, Mean for City 2 and so
on...
That should be easy to do. But perhaps that is something you'd rather work on?
I know I learn a lot by doing...
I think what we have now is already leaps and bounds ahead of the manual
copying and pasting. So this will definitely work for us, if we need more I
am sure I will post those needs under a new thread.
However, I would like to know in your script how you got it to look at the
website and pull the data...if you wouldn't mind adding a couple comments to
your code that would great. This way, I can hopefully learn a little instead
of just copying and pasting your work.
That is a matter of knowing a little (unfortunately too little) about the
InternetExplorer object (see
http://msdn.microsoft.com/en-us/libr...84(VS.85).aspx
Constructing the proper URL to download the web page
This was done by examining the URL when I had navigated to the web page
in question.
Examining the HTML source to try to figure out an unambiguous way of defining
the data I wanted to obtain, using the VBScript Regular Expression engine.
The URL parts all have "URL" as part of their name.
The Regular Expression pattern parts all have "Pat" in their name.
Hope this helps.
Thank you immensely,
Jason
"Ron Rosenfeld" wrote:
On Thu, 18 Jun 2009 13:17:01 -0700, Jason
wrote:
It works great!
Good to hear! Thanks for the feedback.
However, once I saw it run, I tried to run it in an existing spreadsheet
where the first cell would be A3838 instead of A2 and that did not go too
well...
You really need to be specific. And you will probably find that by doing so,
and by clearly elaborating your problem, a solution will become apparent.
I'm guessing that the "did not go too well" problem perhaps has to do with
repetitive calls to the web page? If that is the case, then you probably are
not properly setting rng. But with limited information, it's hard to advise
further.
Right now, I'm just trying to speed it up, which you are helping with
immensely.
I suppose by "it", you mean the process of entering these dates.
I'm sure the next step would be to put all this into a database
and have it run automatically daily.
If you are going to run it every day, then calling the web page as I've done
should be adequate. If you were going to run it weekly or monthly, then a call
to the web page that downloads a customizable series of dates might be better.
--ron
--ron