GBKEE

¦^´_ 5# c_c_lai

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

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

¦^´_ 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

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

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, "&nbsp;", "")
    
24.         .PutInClipboard
    
25.     End With
    
26.    
    
27.     Sheets.Add().PasteSpecial NoHTMLFormatting:=True    '¤£­n§t®æ¦¡
    
28. End Sub

½Æ»s¥N½X