[µo°Ý] ½Ð°Ý¦p¦ó§ì¨ú®ð¶H§½opendata xml °ÝÃD

yehmengfeng

¤p§Ìªñ¨Ó¦b§ä½×¤å¸ê®Æ¡G
½Ð°Ý¦U¦ìµ{¦¡°ª¤â¡A¦p¦ó§ì¨ú¡uÆ[´ú¸ê®Æ¬d¸ß¨t²Î¡vªº¤º¸ê®Æ¡Ghttp://e-service.cwb.gov.tw/HistoryDataQuery/index.jsp
¦]½×¤å¼g§@¤W¸ê®Æ¤ÀªR¨C­Ó¤ëªº®ð­Ô¸ê®Æ¡A½Ð°Ý¦p¦ó¨Ï¥Îexcel VBAªº¤è¦¡§â¸ê®Æ§ì¨ú¨ìexcel
½Ð°Ý¦p¦ó¨ú±oÆ[´ú¸ê®Æ¬d¸ß¨t²Îªº¤ë³øªí¡Ghttp://e-service.cwb.gov.tw/HistoryDataQuery/MonthDataController.do?command=viewMain&station=467410&datepicker=2015-01

stillfish00

¦^´_ 1# yehmengfeng
°Ñ¦Ò¬Ý¬Ý

1. Sub GetWeatherInfo()
   
2.     Dim oXmlhttp As Object: Set oXmlhttp = CreateObject("msxml2.xmlhttp")
   
3.     Dim oHtmldoc As Object: Set oHtmldoc = CreateObject("htmlfile")
   
4.     Dim oClip As Object: Set oClip = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") 'DataObject
   
5.     Dim sURL As String
   
6.    
   
7.     sURL = "http://e-service.cwb.gov.tw/HistoryDataQuery/MonthDataController.do?command=viewMain&station=467410&datepicker=2016-01"
   
8.     With oXmlhttp
   
9.         .Open "Get", sURL, False
   
10.         .Send
    
11.         oHtmldoc.write .responseText
    
12.     End With
    
13.    
    
14.     Dim oTable As Object
    
15.     Set oTable = oHtmldoc.GetElementById("MyTable")
    
16.     If oTable Is Nothing Then MsgBox "Â^¨úTable¥¢±Ñ...": Exit Sub
    
17.    
    
18.     'remove top header row (press/temperature...)
    
19.     oTable.FirstChild.RemoveChild oTable.FirstChild.ChildNodes(0)
    
20.    
    
21.     'copy to clip board
    
22.     With oClip
    
23.         .SetText Replace(oTable.outerhtml, " ", "")
    
24.         .PutInClipboard
    
25.     End With
    
26.    
    
27.     Sheets.Add().PasteSpecial NoHTMLFormatting:=True    '¤£­n§t®æ¦¡
    
28. End Sub

½Æ»s¥N½X

joey0415

¦^´_ 1# yehmengfeng
ÅܼƦۤv§ï§ï¬Ý¡I

1. Sub ¥¨¶°1()
   
2. '
   
3.     Cells.Delete
   
4. 
   
5.     surl = "URL;" & "http://e-service.cwb.gov.tw/HistoryDataQuery/MonthDataController.do?command=viewMain&station=467410&datepicker=2015-01"
   
6.    
   
7.     Set QT = ActiveSheet.QueryTables.Add(Connection:=surl, Destination:=Range("$A$1"))
   
8.    
   
9.     With QT
   
10.         .WebFormatting = xlWebFormattingNone
    
11.         .Refresh BackgroundQuery:=False
    
12.         .Delete
    
13.     End With
    
14.    
    
15.     Set QT = Nothing
    
16. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 2# stillfish00
¦^´_ 3# joey0415
ºî¦X2¦ì,¨Ï¥Îªí³æ§e²{,¥i¿ï¾Ü¦a°Ï,¸ê®Æ®æ¦¡,®É¶¡. ¶×¤J©Ò­nªº®ð¶H¸ê®Æ.

·s¼W UserForm(ªí³æ) ¤¤¥[¤JComboBox1, ComboBox2, ComboBox3 ,CommandButton1

1. Option Explicit
   
2. Dim xObject()
   
3. Private Sub UserForm_Initialize()
   
4.     xObject = Array(ComboBox1, ComboBox2, ComboBox3)
   
5.     Æ[´ú¬d¸ß_³]©w
   
6.      Com
   
7.      ComboBox1.Value = ComboBox1.List(0)
   
8.      ComboBox2.Value = ComboBox2.List(0)
   
9. End Sub
   
10. Private Sub ComboBox1_Change() '´ú¡@¡@¯¸
    
11.      Com
    
12. End Sub
    
13. Private Sub ComboBox2_Change() '¸ê®Æ®æ¦¡
    
14.     Dim i  As Double
    
15.     With ComboBox3
    
16.         .Clear
    
17.         Select Case ComboBox2.ListIndex
    
18.             Case 0 '
    
19.                 For i = Date - 1 To DateAdd("Q", -1, Date) Step -1
    
20.                     .AddItem
    
21.                     .List(.ListCount - 1) = Format(i, "YYYY-MM-DD")
    
22.                 Next
    
23.             Case 1
    
24.                 i = Date
    
25.                 Do
    
26.                     .AddItem
    
27.                     .List(.ListCount - 1) = Format(i, "YYYY-MM")
    
28.                     i = DateAdd("M", -1, i)
    
29.                 Loop Until Year(i) < Year(Date) - 1
    
30.             Case 2
    
31.                 For i = Year(Date) To Year(Date) - 1 Step -1
    
32.                     .AddItem
    
33.                     .List(.ListCount - 1) = i
    
34.                 Next
    
35.         End Select
    
36.         If ComboBox2.ListIndex > -1 Then .Value = .List(0)
    
37.     End With
    
38.     Com
    
39. End Sub
    
40. Private Sub ComboBox3_Change()  '®É¡@¡@¶¡
    
41.     Com
    
42. End Sub
    
43. Private Sub Com() '
    
44.     Dim E As Variant
    
45.     With CommandButton1
    
46.         .Enabled = True
    
47.         For Each E In xObject
    
48.             If E.ListIndex = -1 Then .Enabled = False '«ö¯Ã(Æ[´ú¸ê®Æ¬d¸ß):¤£¥i¥Î
    
49.         Next
    
50.     End With
    
51. End Sub
    
52. Private Sub CommandButton1_Click() '«ö¯Ã(Æ[´ú¸ê®Æ¬d¸ß)
    
53.      Dim surl As String, QT As QueryTable
    
54.     'http://e-service.cwb.gov.tw/HistoryDataQuery/YearDataController.do?command=viewMain&station=467410&datepicker=2016
    
55.     'http://e-service.cwb.gov.tw/HistoryDataQuery/MonthDataController.do?"
    
56.     'http://e-service.cwb.gov.tw/HistoryDataQuery/DayDataController.do?
    
57.     surl = "URL; http://e-service.cwb.gov.tw/HistoryDataQuery/"
    
58.     Select Case ComboBox2.ListIndex
    
59.         Case 0
    
60.             surl = surl & "DayDataController.do?"
    
61.         Case 1
    
62.             surl = surl & "MonthDataController.do?"
    
63.         Case 2
    
64.             surl = surl & "YearDataController.do?"
    
65.     End Select
    
66.     surl = surl & "command=viewMain&station=" & ComboBox1.List(ComboBox1.ListIndex, 1)
    
67.     surl = surl & "&datepicker=" & ComboBox3
    
68.     With ActiveSheet
    
69.         .Cells.Delete
    
70.         Set QT = .QueryTables.Add(Connection:=surl, Destination:=.Range("$A$1"))
    
71.         With QT
    
72.             .WebFormatting = xlWebFormattingNone
    
73.             .Refresh BackgroundQuery:=False
    
74.             .Delete
    
75.         End With
    
76.     End With
    
77.     Set QT = Nothing
    
78. End Sub
    
79. Private Sub Æ[´ú¬d¸ß_³]©w()
    
80.     Dim i As Double, E As Object, op As Object
    
81.     Dim oXmlhttp As Object, oHtmldoc As Object, Url As String
    
82.     Set oXmlhttp = CreateObject("msxml2.xmlhttp")
    
83.     Set oHtmldoc = CreateObject("htmlfile")
    
84.     Url = "http://e-service.cwb.gov.tw/HistoryDataQuery/QueryDataController.do?command=viewMain"   '
    
85.     With oXmlhttp
    
86.         .Open "Get", Url, False
    
87.         .Send
    
88.         oHtmldoc.write .responseText
    
89.     End With
    
90.     With oHtmldoc
    
91.         Set E = .all.tags("SELECT")(0)
    
92.         Set op = .all.tags("option")
    
93.     End With
    
94.     For i = 0 To op.Length - 1
    
95.         If i <= E.Length - 1 Then
    
96.             With ComboBox1
    
97.                 .AddItem
    
98.                 .List(.ListCount - 1, 0) = op(i).innertext  '´ú¯¸:¦WºÙ
    
99.                 .List(.ListCount - 1, 1) = op(i).Value      '´ú¯¸:¼Æ­È
    
100.             End With
     
101.         Else
     
102.             With ComboBox2
     
103.                 .AddItem
     
104.                 .List(.ListCount - 1, 0) = op(i).innertext  '¸ê®Æ®æ¦¡:¦WºÙ
     
105.             End With
     
106.         End If
     
107.     Next
     
108. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 4# GBKEE
¸g´ú¸Õµo²{¡G
¡uPrivate Sub CommandButton1_Click()     '  «ö¯Ã(Æ[´ú¸ê®Æ¬d¸ß)¡v

    surl = "URL; http://e-service.cwb.gov.tw/HistoryDataQuery/"
­n­×¥¿¬°¡G
    surl = "URL;http://e-service.cwb.gov.tw/HistoryDataQuery/"

"URL;" »P "http://" ¶¡¤£¯à¦s¦bªÅ®æ (Space)¡A§_«h°õ¦æ®É¡A·|²£¥Í¦p¤Uªº¿ù»~°T®§¡G

¡uµLªk¶}±Ò "URL; http://e-service.cwb.gov.tw/Hist ... tepicker=2016-01-08"
¡CµLªk³s±µºô»Úºô¸ô¦øªA¾¹¡C¡v

±z¼Ò²Õªº²Õ¦X¡A¨ä¼u©Ê»P¿ï¾Ü©Ê¥R¤À¦aªí¹F¥X¨Ó¡AÁÂÁ±z¡I

¤U¸ü (37.9 KB)

2016-1-9 18:31

GBKEE

¦^´_ 5# c_c_lai

ÁÂÁ§Aªº´ú¸Õ¡A2003¥iªÅ¤@®æ¡C

c_c_lai

¦^´_ 6# GBKEE
©Ò¥H»¡¹À¡A³o¤]¬O·L³nª©¥»¤§¤£¤@­Pªº¸á¯f¡A
Ãø©Ç¦³±`»¡¦b 2003 ¯à°÷°õ¦æªº¡A´«¤Fª©¥»
«o¤S¤£¯à¡CÄ´¦p±`µo¥Í¡G Automation ¤§Ãþ¡C

yehmengfeng

·PÁ¦U¦ìªº¨ó§U¡A­ì¨Óexcel vba¦³³o»ò¦h¼Ëªº¤èªk¡AÅý§Ú§ó¼e¼sªº«ä¦ÒªÅ¶¡
ªñ¤éÁÙ¦³¤£Â_ªº¾Ç²ßvbaµ{¦¡¡AÁÙ¦³§V¤O®ø¤Æ¤¤, ¦ý....ÁÙ¨S§¹¥þ²z¸Ñ¡@¡Ö¡Õ
½Ð°Ý¡A¦pªG­n§â¦a°Ï(ex¡G¥x«n¡B¥xªF)©ñ¦b¬¡­¶¥»¤W°Ï®æ¶},¥i¥H¦Û¦æ¿ï¾Ü®É¶¡°Ï¶¡(ex¡G2015-01-01 ¦Ü 2015-12-31)¡A§â¨C¤éªº¸ê®Æ®æ¦¡¥þ³¡©ñ¦b¤@°_,¥Î®É¶¡§@°Ï®æ. ¶×¤J¨ìexcel

yehmengfeng

¦U¦ì¦Ñ®v­Ì¦n¡G
¤§«e¦³µo°Ý¤@­Ó°ÝÃD¡A¦³Ãö¦p¦ó§ì¨ú¡uÆ[´ú¸ê®Æ¬d¸ß¨t²Î¡vªº¤º¸ê®Æ¡Ghttp://e-service.cwb.gov.tw/HistoryDataQuery/index.jsp
¤§«e©«¤l¡Ghttp://forum.twbts.com/thread-16114-1-1.html
«á¨Ó¨Ì¾Ú¸Ì­±ªº¸ê®Æ¥h­×§ïµ{¦¡½X¡A¤]ªá¤F«Ü¦hªº®É¶¡¥h§ä¤èªk¡A¦ýÁÙ¬O°µ¤£¥X¨Ó¡A½Ð°Ý.....¥i¥H±Ð±Ð§Ú¦p¦ó°µ¶Ü? §ÖºÆ¤F¡C
ps....§Ú¤£¬O¤p¥Õ°Õ¡A §Ú¯uªº«Ü·PÁ¦U¦ì¦Ñ®vªº¨ó§U¡A¤ß·Q......­ì¨Ó~~~vbaªºµ{¦¡¥i¥H³o¼Ë¼g~~¤Ó¯«©_¤F
½Ð°Ý¡A¦pªG­n§â¦a°Ï(ex¡G¥x«n¡B¥xªF)©ñ¦b¬¡­¶¥»¤W°Ï®æ¶},¥i¥H¦Û¦æ¿ï¾Ü®É¶¡°Ï¶¡(ex¡G2015-01-01 ¦Ü 2015-12-31)¡A§â¨C¤éªº¸ê®Æ®æ¦¡¥þ³¡©ñ¦b¤@°_,¥Î®É¶¡§@°Ï®æ. ¶×¤J¨ìexcel

stillfish00

¦^´_ 9# yehmengfeng
°Ñ¦Ò¬Ý¬Ý
®ð¶H.zip (31.7 KB)

¤U¸ü¦¸¼Æ: 17

2016-2-25 16:20