Sub Tianqi()
Dim str As String
On Error Resume Next
Cells.Delete
t1 = Time
n = 1
For i = 2018 To 2019 '由2011到2019进行循环
For j = 1 To 12 '按1-12月进行循环
If j < 10 Then '给小于10的月份前补数字0(网址需要)
t = 0
Else
t = ""
End If
str = i & t & j
If i = Year(Date) And j > Month(Date) Then Exit For '如果时间大于本月则推出循环,主要是为了不循环今年的后几个月
With ActiveSheet.QueryTables.Add("url;http://www.tianqihoubao.com/lishi/QICHUN/month/" & str & ".html", Range("a" & n)) '天气后报的网址,如果要爬某个城市的天气请把“beijing”改成对应的城市拼音即可
.WebFormatting = xlWebFormattingNone '不包含格式
.WebSelectionType = xlSpecifiedTables '指定table模式
.WebTables = "1" '第1张table
.Refresh False
End With
n = Cells(Rows.Count, 1).End(3).Row + 1
Next
Next
Columns("A:D").Select
ActiveSheet.Range("$A:$D").RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlNo '删除重复项
Range("C:C,D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove '插入空行
Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True '分列
Columns("D:D").TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True '分类
Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True '分类
ColumnCells.Replace " ", "", 2 '去掉空格
Cells.Replace "℃", "", 2 '去掉℃
Range("B1:G1") = Array("白天天气", "夜晚天气", "最高气温", "最低气温", "白天风", "夜晚风")
str1 = Time - t1
MsgBox Format(CDate(str1), "hh:mm:ss")
End Sub
|