Ron,
Okay, I'm back...
I was able to actually take your code and reference the sheet with the 25
airport codes and add the data for each code in 3 columns.
Of course, then I got greedy and thought I wanted something faster. So I
tried taking your code and getting it to work for the Custom Date Range so
that data could be entered in much quicker.
I'm still learning, so I just want to have it work for 1 city and a hardset
date range. I think I can get it to repeat for the different airports later.
For this example, I am trying to run for Houston from 9/1/2008 to 6/28/2009.
I have setup a series of dates in column A of my Excel spreadsheet,
beginning with 9/1/2008 in A1 and daily increments down. I have claimed a
small victory in that when run, it does go the site and grab the correct max,
min and mean from the list.
The problem is that it enters the max, min, and mean for 9/1/2008 as the
temperatures for every day following. Hopefully it is something simple, that
will allow it to move down to the next date and grab that temperature.
Hopefully you can understand my explanation, if not, you can simply enter a
list of dates from 9/1/2008 to 9/10/2008 in A1:A10 and run the code below and
see the repeat.
As always thanks!
Option Explicit
Sub getCityTemps()
Dim AirCode As Range, ACrng As Range
Dim c As Range, rng As Range
Dim j As Long
Dim sURLairport As String
Dim myStr As String
Dim test As Variant
Dim Val As Variant
Dim IE As Object
'Set ACrng = Sheets("City_Airport").Range("B2:B26")
'For Each AirCode In ACrng
' sURLairport = AirCode
Const sURL1 As String =
"
http://www.wunderground.com/history...009&req_city=NA&req_state=NA&req_statename=NA"
'Const sURL2 As String = "/"
Dim sURLdate As String
'Const sURL3 As String = "/DailyHistory.html?MR=1"
Application.Cursor = xlWait
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate sURL1
While IE.ReadyState <> 4
DoEvents
Wend
myStr = IE.Document.body.innerhtml
Set rng = Range("A2").CurrentRegion
Set rng = rng.Resize(rng.Rows.Count, 1)
For Each c In rng
sURLdate = Format(c.Value2, "yyyy/m/d")
c.Offset(0, 1).Value = RegexMid(myStr, "bl gb")
c.Offset(0, 2).Value = RegexMid(myStr, "br gb")
c.Offset(0, 3).Value = RegexMid(myStr, "class=gb")
j = j + 1
Next c
'i = i + 3
'Next AirCode
Application.Cursor = xlDefault
End Sub
Private Function RegexMid(s As String, sTempType As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.MultiLine = True
re.Pattern = "\b" & sTempType & "\b\D+(\d+)"
If re.test(s) = True Then
Set mc = re.Execute(s)
RegexMid = mc(0).submatches(0)
End If
End Function
