useful VBA for weather web site, I need help with one last part please!

[jim5309]

I have code that basically looks at airport codes and fetches weather data from weatherunderground based on the months and years data you give it. i can't figure out how to make it so I can just update it instead of going and getting 2001-2011 data all over again. Right now if I want to update it, I have to run the macro for 1.5 hours and get all the data again so that it's recent--through yesterday say. I would love to be able to just punch a button to go and get the last month of data say. If i send along the code does anyone think they can work this out? I would be forever grateful. Please email me or respond and I will send you the useful vba and excel sheet. jim.t.mullin@gmail.com

 

[JackDanIce]

You'll probably get more of a response if you post your code

 

[jim5309]

Here is the code, keep in mind it is looking to a reference page with 2 columns. 1 is months 1-12, the other has airport codes so it knows what to pull from the web site.

Option Explicit
Sub macro1()
Dim Count As Integer
Dim YearCount As Integer
Dim MonthCount As Integer
Dim xlOverwrite As Boolean
Dim YearVal As String
Dim MonthVal As String
Dim AirportCode As String
Dim SheetName As String
Dim ActiveRange As Range
For Count = 1 To Sheet5.Range("F1").Value
AirportCode = Sheet5.Range("A1").Offset(Count, 0).Text
SheetName = Sheet5.Range("A1").Offset(Count, 1).Text
For YearCount = 2001 To 2010
YearVal = WorksheetFunction.Text(YearCount, "0000")
For MonthCount = 1 To 12
Sheet1.Activate
Range("H3", "AC40").Clear
MonthVal = WorksheetFunction.Text(MonthCount, "00")

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.wunderground.com/history/airport/" & AirportCode & "/" & YearVal & "/" & MonthVal & "/28/MonthlyHistory.html?req_city=NA&req_state=NA&req_statename=NA&format=1" _
, Destination:=Range("Sheet1!$H$2"))
.Name = _
"MonthlyHistory.html?req_city=NA&req_state=NA&req_statename=NA&format=1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwrite
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Application.DisplayAlerts = False
Range("H3:H36").Select
Selection.TextToColumns Destination:=Range("H3"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1)), TrailingMinusNumbers:=True
Application.DisplayAlerts = True

If Not MonthCount = 1 Or Not YearCount = 2001 Then
Sheet1.Select
Range("H4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets(SheetName).Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
ActiveSheet.Paste
Else
Range("H3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets(SheetName).Select
Range("A1").Select
ActiveSheet.Paste
End If
Next MonthCount
Next YearCount
Next Count
End Sub