ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

¥»©«³Ì«á¥Ñ GBKEE ©ó 2016-3-4 14:19 ½s¿è

¦^´_ 9# yehmengfeng
¸Õ¸Õ¬Ý
UserForm ¼Ò²Õªºµ{¦¡½X
UserForm »Ý¨î©w±±¨î¶µ CommandButton1,ComboBox1,ComboBox2
  1. Option Explicit
  2. Dim Sh(1 To 2) As Worksheet
  3. Private Sub UserForm_Initialize()
  4.     Set Sh(1) = Sheets.Add  '³]©w·s¼W¤u§@ªí
  5.     Sh(1).Visible = False   'ÁôÂäu§@ªí
  6.     Æ[´ú¬d¸ß_³]©w
  7. End Sub
  8. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  9.     Application.DisplayAlerts = False
  10.     Sh(1).Delete
  11.     Application.DisplayAlerts = True
  12. End Sub
  13. Private Sub Æ[´ú¬d¸ß_³]©w()
  14.     Dim Url As String, i As Double, op As Object, xDate As Date
  15.     Dim oHtmldoc As Object
  16.     Set oHtmldoc = CreateObject("htmlfile")
  17.     Url = "http://e-service.cwb.gov.tw/HistoryDataQuery/QueryDataController.do?command=viewMain"   '
  18.     With CreateObject("msxml2.xmlhttp")
  19.         .Open "Get", Url, False
  20.         .Send
  21.         oHtmldoc.write .responseText
  22.     End With
  23.     With ComboBox1
  24.         For Each op In oHtmldoc.all.tags("SELECT")(0)
  25.             .AddItem
  26.             .List(.ListCount - 1, 0) = op.innertext  '´ú¯¸:¦WºÙ
  27.             .List(.ListCount - 1, 1) = op.Value      '´ú¯¸:¼Æ­È
  28.         Next
  29.         .ListIndex = 0
  30.     End With
  31.     With ComboBox2
  32.         For i = 0 To -59 Step -1  '60­Ó¤ë¥÷
  33.             xDate = DateAdd("M", i, Date)
  34.             .AddItem
  35.             .List(.ListCount - 1, 0) = Format(xDate, "EE¦~MM¤ë")
  36.             .List(.ListCount - 1, 1) = DateSerial(Year(xDate), Month(xDate), 1)
  37.         Next
  38.         .ListIndex = 0
  39.     End With
  40. End Sub
  41. Private Sub CommandButton1_Click() '«ö¯Ã(¸ê®Æ¬d¸ß)
  42.      Dim surl As String, QT As Range, Qdate As Date, Station As String, Msg As Boolean
  43.     'http://e-service.cwb.gov.tw/HistoryDataQuery/DayDataController.do?
  44.     surl = "URL;http://e-service.cwb.gov.tw/HistoryDataQuery/DayDataController.do?"
  45.     surl = surl & "command=viewMain&station=" & ComboBox1.List(ComboBox1.ListIndex, 1)
  46.     Station = ComboBox1.List(ComboBox1.ListIndex, 0) & "(" & ComboBox1.List(ComboBox1.ListIndex, 1) & ")" '¤u§@ªí¦WºÙ
  47.     On Error GoTo Make_station:  '³B¸Ì¿ù»~: ´ú¯¸¤£¦s¦b
  48.     Set Sh(2) = Sheets(Station)  '«ü©w¤u§@ªí(´ú¯¸)
  49.     On Error GoTo 0              '¦³¿ù»~¤£³B²z¤F
  50.     Application.ScreenUpdating = False
  51.     For Qdate = ComboBox2.List(ComboBox2.ListIndex, 1) To DateAdd("m", 1, ComboBox2.List(ComboBox2.ListIndex, 1)) - 1
  52.         surl = "URL;http://e-service.cwb.gov.tw/HistoryDataQuery/DayDataController.do?command=viewMain&station=" & ComboBox1.List(ComboBox1.ListIndex, 1) & "&datepicker=" & Format(Qdate, "yyyy-m-dd")
  53.         If Qdate > Date Then Exit For    '¤é´Á¤j©ó·í¤é
  54.         If Not Sh(2).Range("A:A").Find(Qdate, LookIn:=xlFormulas) Is Nothing Then GoTo Ne  '¤é´Á¸ê®Æ¤w¦s¦b,¤U¤@¤é´Á°j°é
  55.         Application.StatusBar = "¶×¤J " & Qdate & " ¸ê®Æ..."  'ª¬ºA¦Cªº¤å¦r
  56.         With Sh(1)
  57.             .UsedRange.Delete
  58.             With .QueryTables.Add(Connection:=surl, Destination:=.Range("$A$1"))
  59.                 .WebTables = "MyTable"
  60.                 .WebFormatting = xlWebFormattingNone
  61.                 .Refresh BackgroundQuery:=False
  62.                 If .ResultRange.Rows.Count > 5 Then  '¦³¸ê®Æ
  63.                     Set QT = .ResultRange  '§tªíÀYªº¸ê®Æ½d³ò
  64.                     With Sh(2)
  65.                         If .UsedRange.Count = 1 Then                      '¤u§@ªí(´ú¯¸)¬°ªÅ¥Õ
  66.                             .Range("A1") = "Æ[´ú®É¶¡"
  67.                             .Range("A1").Resize(5).Merge
  68.                         Else
  69.                             Set QT = QT.Rows("6:" & QT.Rows.Count)         '¤£§tªíÀYªº¸ê®Æ
  70.                         End If
  71.                         With .Cells(Rows.Count, "b").End(xlUp)
  72.                             If .Row = 1 Then                '¤u§@ªí(´ú¯¸)¬°ªÅ¥Õ
  73.                                 QT.Copy .Cells
  74.                             Else                            '.Row = 4 ->¨S¦³¸ê®Æ¦ý¦³ªíÀY
  75.                                 QT.Copy .Cells(IIf(.Row = 4, 3, 2))
  76.                             End If
  77.                         End With
  78.                         With .Range(.Cells(Rows.Count, "A").End(xlUp).Offset(1).Address & ":A" & Sh(2).Cells(Rows.Count, "B").End(xlUp).Row)
  79.                             .Cells = Qdate                      'AÄæ¼g¤W¤é´Á
  80.                             .NumberFormatLocal = "yyyy-mm-dd"   'µ¹¤©®æ¦¡
  81.                         End With
  82.                     End With
  83.                 End If
  84.                 .Delete
  85.             End With
  86.         End With
  87. Ne:
  88.     Next
  89.     Application.StatusBar = False
  90.     Application.ScreenUpdating = True
  91.      '±Æ§Ç
  92.     With Sh(2).UsedRange.Offset(5)
  93.         .Sort Key1:=.Range("A1"), Order1:=xlAscending, Key2:=.Range("B1"), Order2:=xlAscending, Header:=xlNo
  94.     End With
  95.     Exit Sub
  96. Make_station:
  97.     Sheets.Add(Sheet1).Name = Station  '·s¼W¤u§@ªí(´ú¯¸)
  98.     Resume   '¦^¨ì¿ù»~ªºµ{¦¡½XÄ~Äòµ{¦¡
  99. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

ÁÂÁÂstillfish00ªº¨ó§U¡A¦]¬°Åv­­¤£¨¬¡A©Ò¥HµLªk¬d¬Ý¡A½Ð¨£½Ì¡C
³Ìªñ¦]¬°¤É¯Å²Õªø¡A©Ò¥H¤u§@¶qÅܤj«Ü¦h¡AÁÙ±o¦h¦hªº½Õ¾A¡C
¦ý¦³ªÅÁÙ¬O¦b¸Õ°µ¡A§Æ±æ¯à°µªº¥X¨Ó¡C
¥Ø«e¬O§â¤W­±ªO¥D¼gªº ¸ÕµÛ¦Û¤w­×§ï¬Ý¬Ý....¡A¦ýÁÙ¬O¦ê¤£°_¨Ó¡C

TOP

GBKEEªO¥D±z¦n¡G
½Ð°Ý §Ú¨Ï¥Î±zpoªºµ{¦¡¸Õ°µ¡Aµo²{·|¤@ª½¥X²{ ½s¿è¿ù»~¡GÅܼƥ¼©w¸q
½Ð°Ý³o­n¦p¦ó¸Ñ¨M¡A¤@ª½§ä¤£¥X¨Ó¨º¦³°ÝÃDÂI¡C

§Ú¬d¨ìªº¸ê®Æ¸ÑÄÀ¡G¡u¦b¼Ò²Õ¶}ÀY¼g¤F Option Explicit¡Aµ{¦¡¸ÌªºÅܼƴN¤@©w­n¥ý«Å§i¤~¯à¨Ï¥Î¡A§_«hµ{¦¡­n°õ¦æªº®É­Ô¡AVBA ¸ÑĶ¾¹·|«ü¥X "Åܼƥ¼©w¸q"¡C
¨S¥[ Option Explicit¡AVBA¸ÑĶ¾¹´N¤£ºÞµ{¦¡¸ÌªºÅܼƦ³¨S¦³«Å§i¡A ¦n³B¬O¦Û¥Ñ¡AÃa³B¬O­Y¤£¤p¤ß¥´¿ù¦r´N·|³Q·í¦¨¥t¤@­ÓÅܼơA ¨Ò¦p: ¦³¤@ÅܼƦWºÙ¬° Customer¡Aµ{¦¡¸Ì¦³¤@³B»~¥´¦¨ Custmoer¡AVBA ¸ÑĶ¾¹¤£·|«ü¥X¿ù»~¡A¥¦·|»{¬° Customer©MCustmoer¬O¨â­Ó¤£¦PªºÅܼơA¦]¦¹³o­Óµ{¦¡°õ¦æªºµ²ªG´N¤£·|¥¿½T¡C¡v

abbr_867650710b08c22e5ea3e2c8ad13e3be.png (76.61 KB)

abbr_867650710b08c22e5ea3e2c8ad13e3be.png

TOP

¦^´_ 13# yehmengfeng
§Ú¨Ï¥Î2003 ¥¿±`
§A¸Õ¸Õ­×¥¿¬Ý¬Ý
  1. Private Sub CommandButton1_Click() '«ö¯Ã(¸ê®Æ¬d¸ß)
  2.      Dim surl As String, QT As Range, Qdate As Date, Station As String, Msg As Boolean
  3.     surl = "URL;http://e-service.cwb.gov.tw/HistoryDataQuery/DayDataController.do?"
  4.     Stop
  5.     MsgBox ComboBox1
  6.     MsgBox ComboBox1.List(Me.ComboBox1.ListIndex, 1)  '¥[¤WME¬O§_¥i­×¥¿¿ù»~
  7.     MsgBox ComboBox1.List(ComboBox1.ListIndex, 1)
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

ÁÂÁÂGBKEEªO¥D  §Ú¸Õ¸Õ¬Ý¡AÁÂÁ±z¡C

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD