Board logo

¼ÐÃD: [µo°Ý] ºô¸ôCSV¤U¸ü°ÝÃD [¥´¦L¥»­¶]

§@ªÌ: spermbank    ®É¶¡: 2013-3-29 11:35     ¼ÐÃD: ºô¸ôCSV¤U¸ü°ÝÃD

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-3-29 20:14 ½s¿è

¤j®a¦n:
     §Ú·Q±qhttp://mops.twse.com.tw/mops/web/t51sb08
     Â^¨ú¤W¥«101¦~²Ä4©u»P¤WÂd101¦~²Ä4©uªº©Ò¦³CSVÀÉ®×
     ¥i¬O°ÝÃD¨ÓÅo
     ¦]¬°¥«³õ§O­n¤À§O¿ï¤W¥«©Î¤WÂd¡A¥B¿ï¾Ü¤W¥«¤S­n¦s¨ú¦h­ÓCSVÀɮסF¿ï¾Ü¤WÂd¤S­n¦s¨ú¦h­ÓCSVÀɮסA¤~¯à¦Û¦æ¨Ö¤J¤@­Óexcelªí®æ·í¤¤
     ©Ò¥H·Q½Ð±Ð
     ¦p¦ó"¤@¦¸"±N¤W¥«¤Î¤WÂdªºCSV¤@¦¸¶×¤J¤@­Óexcelªí®æ·í¤¤©O?
     ÁÂÁÂ
§@ªÌ: GBKEE    ®É¶¡: 2013-3-29 20:14     ¼ÐÃD: RE: ºô¸ôCSV¤U¸ü°ÝÃD

¦^´_ 1# spermbank
³o­ì©lÀɮפ¤§ä¤£¨ìCSVÀɮתººô§}¥i¤U¸ü!!
¸Õ¸Õ¬Ý (¥i¸ÕµÛ­×§ï¤U¸ü¤W¥«,¤WÂdªº¸ê®Æ)
  1. Option Explicit
  2. Sub ¤½¶}¸ê°TÆ[´ú¯¸()
  3.     Dim Ie As Object, xx, k, j, i, AA As Variant
  4.     Dim T As Date
  5.     ActiveSheet.Cells.Clear
  6.      Application.DisplayStatusBar = True
  7.     On Error GoTo ie_err
  8.     Set Ie = CreateObject("InternetExplorer.Application")
  9.     Ie.Navigate "http://mops.twse.com.tw/mops/web/t51sb08"
  10.     Ie.Visible = True
  11.     Do While Ie.Busy Or Ie.ReadyState <> 4: DoEvents: Loop
  12.     '**********  ­×§ï³o¸Ì ªº¿ï¶µ
  13.     Ie.Document.ALL(632).Value = "sii"   '¤W¥« = sii,¤WÂd = otc,¿³Âd = rotc,¤½¶}µo¦æ = pub
  14.     Ie.Document.ALL(643).Value = "101"   '¦~«×
  15.     Ie.Document.ALL(650).Value = "04"    '²Ä¤@©u = 01,²Ä¤G©u = 02,²Ä¤T©u = 03,²Ä¥|©u = 04
  16.     Ie.Document.ALL(667).Click
  17.    
  18.     Ie.Visible = False
  19.     ºô­¶¾ã²z
  20.     Set AA = Ie.Document.getelementsbytagname("table")   '¸ê®Æ°Ï
  21.    ' Ie.Quit
  22.     T = Time
  23.     Application.ScreenUpdating = False
  24.     With ActiveSheet
  25.         On Error Resume Next
  26.         For xx = 13 To AA.Length - 1
  27.             For i = 0 To AA(xx).Rows.Length - 1      '¼g¤J¸ê®Æ
  28.                 k = k + 1
  29.                 For j = 0 To 19
  30.                     .Cells(k, 1).Select
  31.                     Application.StatusBar = "¤U¸ü¸ê®Æ¤¤ ..." & k - 1
  32.                     .Cells(k, j + 1) = AA(xx).Rows(i).Cells(j).innertext
  33.                 Next
  34.             Next
  35.         Next
  36.         Set AA = .Range("S2:S" & .[S2].End(xlDown).Row)
  37.         AA.Replace "°ò¥»¨CªÑ¬Õ¾l", "", xlWhole
  38.         AA.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  39.         .Cells(.Rows.Count, 1).End(xlUp).EntireRow.Delete
  40.          ActiveWindow.ScrollRow = .Cells(.Rows.Count, 1).End(xlUp).Row - 20
  41.         .Cells(.Rows.Count, 1).End(xlUp).Select
  42.     End With
  43.     Application.ScreenUpdating = True
  44.     'Ie.Quit
  45.     Application.StatusBar = "¤U¸ü¸ê®Æ®É¶¡ : " & Format(Time - T, ["S¬í"])
  46.     End
  47. ie_err:
  48.     MsgBox "ºô­¶¦³°ÝÃD,½Ð­«·s°õ¦æ..."
  49.     Set Ie = Nothing
  50. End Sub
  51. Private Sub ºô­¶¾ã²z()   'µ¥«Ýºô­¶¤U¸ü¸ê®Æ§¹²¦ªº®É¶¡
  52.     Dim Tt(1 To 3) As Date
  53.     Tt(1) = Time
  54.     Tt(2) = Time
  55.     Tt(3) = #12:00:10 AM#     '10¬í ½Õ¾ã³o¸Ì
  56.     Do
  57.         If Time > Tt(2) Then
  58.             Application.StatusBar = "ºô­¶¤U¸ü¤¤ ³Ñ¾l¬í¼Æ.. " & Second(Tt(1) + Tt(3) - Time)
  59.             Tt(2) = Time
  60.         End If
  61.         DoEvents
  62.     Loop Until Time > Tt(1) + Tt(3)
  63.     Application.StatusBar = "¸ê®Æ¤U¸ü¤¤...."
  64. End Sub
½Æ»s¥N½X

§@ªÌ: spermbank    ®É¶¡: 2013-3-30 00:56

¥»©«³Ì«á¥Ñ spermbank ©ó 2013-3-30 00:58 ½s¿è

¦^´_ 2# GBKEE


    ·l¯qªí¤W¥«¤Î¤WÂd¥i¥H­C¡A¤Q¤À·PÁ¤j¤j
    ¥t¥~§Ú§âºô§}§ï¦¨http://mops.twse.com.tw/mops/web/t51sb13 Ū¨ú¦X¨Ö·l¯qªí  
    §Úµo²{«o¤S¤£¦æÅo¡A¤£ª¾¹D­n¦p¦ó­×¸Ó?
    ­Yºô¸ô¸ê®Æ·|³°Äò¼W¥[µ§¼Æ¡A¬O§_»Ý­n¦Û¦æ§ó·s§PÂ_µ§¼Æ©O?
    ÁÂÁÂ
§@ªÌ: GBKEE    ®É¶¡: 2013-3-30 09:03

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-3-30 16:06 ½s¿è

¦^´_ 3# spermbank
  1. ³oºô­¶¦³­×§ï±±¨î¶µ,2# ³o¨Çµ{¦¡½X¤£¾A¥Î, 4#ªºµ{¦¡½X¤w­×§ï¬°¥¿½T
  2. Ie.Document.ALL(632).Value = "sii"   '¤W¥« = sii,¤WÂd = otc,¿³Âd = rotc,¤½¶}µo¦æ = pub
  3. Ie.Document.ALL(643).Value = "101"   '¦~«×
  4. Ie.Document.ALL(650).Value = "04"    '²Ä¤@©u = 01,²Ä¤G©u = 02,²Ä¤T©u = 03,²Ä¥|©u = 04
  5. Ie.Document.ALL(667).Click
½Æ»s¥N½X
¨C¤@ºô­¶ªºªí³æ«Ø¸m¤£¤@¼Ë,»Ý¤@¤@¥h´M§ä
  1. Option Explicit
  2. Sub ¤½¶}¸ê°TÆ[´ú¯¸()
  3.     Dim ie As Object, xx, k, j, i, AA As Variant
  4.     Dim T As Date, ¸ê°T As String
  5.     Do
  6.         ¸ê°T = InputBox("1: ·l¯qªí" & vbLf & "2: ¦X¨Ö·l¯qªí")   '¿ï¾Ü¤½¶}¸ê°TÆ[´ú¯¸ªºªí®æ
  7.        '¸ê°T = InputBox("1: ·l¯qªí" & vbLf & "2: ¦X¨Ö·l¯qªí"& vbLf & "3: ???ªí"...............)
  8.         If ¸ê°T = "" Then Exit Sub                              '¤£¿ï¾Üµ²§ôµ{¦¡
  9.     Loop Until Val(¸ê°T) > 0 And Val(¸ê°T) <= 2 '                Val(¸ê°T)<=???  µ²§ô¿ï¾Üªí³æ
  10.     ActiveSheet.Cells.Clear
  11.     Application.DisplayStatusBar = True
  12.     Application.StatusBar = "....... ºô­¶¶}±Ò¤¤.......... "
  13.    
  14.   '  On Error GoTo ie_err
  15.     Set ie = CreateObject("InternetExplorer.Application")
  16.     ie.Navigate ¤U¸üºô§}(¸ê°T)
  17.     Do While ie.Busy Or ie.ReadyState <> 4
  18.             DoEvents
  19.     Loop
  20.     With ie.Document
  21.         T = Time
  22.         Do While ie.Busy Or ie.ReadyState <> 4
  23.             If Time = T + #12:01:00 AM# Then GoTo ie_err                'µ¥­Ôºô­¶²§±`:µ²§ôµ{¦¡
  24.             DoEvents
  25.         Loop
  26.         '**********  ­×§ï³o¸Ì ªº¿ï¶µ
  27.         .getelementbyID("TYPEK").Value = "sii"  '¤W¥« = sii,¤WÂd = otc,¿³Âd = rotc,¤½¶}µo¦æ = pub
  28.         .getelementbyID("year").Value = "101"    '¦~«×
  29.         .getelementbyID("season").Value = "02"    '²Ä¤@©u = 01,²Ä¤G©u = 02,²Ä¤T©u = 03,²Ä¥|©u = 04
  30.         For i = 0 To .getelementsbytagname("input").Length - 1
  31.             If .getelementsbytagname("input")(i).Type = "button" And .getelementsbytagname("input")(i).Value = " ·j´M " Then
  32.                 .getelementsbytagname("input")(i).Click
  33.             End If
  34.         Next
  35.         µ¥«Ýºô­¶
  36.         Set AA = .getelementsbytagname("table")   '¸ê®Æ°Ï
  37.     End With
  38.     T = Time
  39.     With ActiveSheet
  40.         .Cells(1, "A") = IIf(¸ê°T = 1, "·l¯qªí", "¦X¨Ö·l¯qªí")
  41.         .Cells(1, "B") = ¥«³õ§O(ie.Document.getelementbyID("TYPEK").Value)
  42.         .Cells(1, "C") = ie.Document.getelementbyID("year").Value & "¦~«×"
  43.         .Cells(1, "D") = ie.Document.getelementbyID("season").Value & "©u"
  44.         .Cells(1).Select
  45.         Application.ScreenUpdating = False
  46.         k = 1
  47.         On Error Resume Next
  48.         For xx = ºô­¶ªí®æ(¸ê°T) To AA.Length - 1
  49.             For i = 0 To AA(xx).Rows.Length - 1      '¼g¤J¸ê®Æ
  50.                 k = k + 1
  51.                 For j = 0 To 19
  52.                     Application.StatusBar = "¤U¸ü¸ê®Æ¤¤ ..." & k - 1
  53.                     .Cells(k, j + 1) = AA(xx).Rows(i).Cells(j).innertext
  54.                 Next
  55.             Next
  56.         Next
  57.         Set AA = .Range("S3:S" & .[S3].End(xlDown).Row)
  58.         AA.Replace "°ò¥»¨CªÑ¬Õ¾l", "", xlWhole
  59.         AA.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  60.         .Cells(.Rows.Count, 1).End(xlUp).EntireRow.Delete
  61.         .Cells.EntireColumn.AutoFit
  62.         .[A1].Select
  63.     End With
  64.     Application.ScreenUpdating = True
  65.     ie.Quit
  66.     Application.StatusBar = "¤U¸ü¸ê®Æ®É¶¡ : " & Format(Time - T, ["S¬í"])
  67.     End
  68. ie_err:
  69.     ie.Quit
  70.     MsgBox "ºô­¶¦³°ÝÃD,½Ð­«·s°õ¦æ..."
  71.     End
  72. ¸ê°T_Err:
  73. End Sub
  74. Private Sub µ¥«Ýºô­¶()    'µ¥«Ýºô­¶¤U¸ü¸ê®Æ§¹²¦ªº®É¶¡
  75.     Dim Tt(1 To 3) As Date
  76.     Tt(1) = Time
  77.     Tt(2) = Time
  78.     Tt(3) = #12:00:10 AM#     '10¬í ½Õ¾ã³o¸Ì
  79.     Do
  80.         If Time > Tt(2) Then
  81.             Application.StatusBar = "ºô­¶¤U¸ü¤¤ ³Ñ¾l¬í¼Æ.. " & Second(Tt(1) + Tt(3) - Time)
  82.             Tt(2) = Time
  83.         End If
  84.         DoEvents
  85.     Loop Until Time > Tt(1) + Tt(3)
  86.     Application.StatusBar = "¸ê®Æ¤U¸ü¤¤...."
  87. End Sub
  88. Private Function ¤U¸üºô§}(xWord As String) As String      '¶Ç¦^¿ï¾Üªººô§}
  89.     Select Case xWord
  90.         Case "1"
  91.             ¤U¸üºô§} = "http://mops.twse.com.tw/mops/web/t51sb08" '·l¯qªí
  92.         Case "2"
  93.             ¤U¸üºô§} = "http://mops.twse.com.tw/mops/web/t51sb13" '¦X¨Ö·l¯qªí
  94.         ''Case "3"     ·sºô­¶ªººô§}
  95.         ''
  96.         ''
  97.     End Select
  98. End Function
  99. Private Function ¥«³õ§O(xWord As String) As String
  100.        '¤W¥« = sii,¤WÂd = otc,¿³Âd = rotc,¤½¶}µo¦æ = pub
  101.     Select Case xWord
  102.         Case "sii"
  103.             ¥«³õ§O = "¤W¥«"
  104.         Case "otc"
  105.             ¥«³õ§O = "¤WÂd"
  106.         Case "rotc"
  107.             ¥«³õ§O = "¿³Âd"
  108.         Case "pub"
  109.             ¥«³õ§O = "¤½¶}µo¦æ"
  110.     End Select
  111. End Function
  112. Private Function ºô­¶ªí®æ(xWord As String) As Integer
  113.     Select Case xWord
  114.         Case "1"
  115.             ºô­¶ªí®æ = 13  '·l¯qªí
  116.         Case "2"
  117.             ºô­¶ªí®æ = 12  '¦X¨Ö·l¯qªí
  118.         
  119.         ''¨C¤@ºô­¶ªºªí³æ«Ø¸m¤£¤@¼Ë,»Ý¤@¤@¥h´M§ä
  120.         '' ¦Û¦æ¬d¬Ý·s¼W Case "3"......
  121.         ''
  122.     End Select
  123. End Function
½Æ»s¥N½X

§@ªÌ: HSIEN6001    ®É¶¡: 2013-3-30 22:45

¦^´_ 4# GBKEE
            ºô­¶ªí®æ = 13  '·l¯qªí
            ºô­¶ªí®æ = 12  '¦X¨Ö·l¯qªí

½Ð°Ý13»P12¼Æ¾Ú¥Ñ¨Ó
­Y´«¦¨¤£¦Pªí®æ§Ú¸Ó­n¦p¦ó§PÂ_³o¼Æ­È?!
¨Ò¦p:http://mops.twse.com.tw/mops/web/t56sb21_q3
§@ªÌ: HSIEN6001    ®É¶¡: 2013-3-30 23:04

¤£³]ªí®æ¦WºÙ(©ïÀY),¥H¤U¬O­×§ï³¡¤À¸ê®ÆÀ³¥Î
±æ½Ð­×¥¿¿ù»~,ÁÂÁÂ..^___^..
  1. Option Explicit
  2. Sub ¥Ó³øÂàÅý()
  3. Dim ie As Object, xx, k, j, i, AA As Variant
  4. Dim T As Date, ¸ê°T As String
  5.     Do
  6.         ¸ê°T = InputBox("1: ¾ú¥v«ùªÑÂàÅý" & vbLf & "2: ¦X¨Ö·l¯qªí")   '¿ï¾Ü¤½¶}¸ê°TÆ[´ú¯¸ªºªí®æ
  7.         If ¸ê°T = "" Then Exit Sub                              '¤£¿ï¾Üµ²§ôµ{¦¡
  8.     Loop Until Val(¸ê°T) > 0 And Val(¸ê°T) <= 2 '                Val(¸ê°T)<=???  µ²§ô¿ï¾Üªí³æ
  9.    
  10.     ActiveSheet.Cells.Clear
  11.     Application.DisplayStatusBar = True
  12.     Application.StatusBar = "....... ºô­¶¶}±Ò¤¤.......... "
  13.    
  14. 'On Error GoTo ie_err
  15. Set ie = CreateObject("InternetExplorer.Application")
  16. ie.Navigate ¤U¸üºô§}(¸ê°T)
  17.     Do While ie.Busy Or ie.ReadyState <> 4
  18.             DoEvents
  19.     Loop
  20.     With ie.Document
  21.         T = Time
  22.         Do While ie.Busy Or ie.ReadyState <> 4
  23.             If Time = T + #12:01:00 AM# Then GoTo ie_err                'µ¥­Ôºô­¶²§±`:µ²§ôµ{¦¡
  24.             DoEvents
  25.         Loop
  26.     '**********  ­×§ï³o¸Ì ªº¿ï¶µ
  27.     'http://mops.twse.com.tw/mops/web/t56sb21_q3?&step=0&firstin=1&off=1&TYPEK=sii&year=91&smonth=07&emonth=09
  28.    
  29.     .getelementbyID("TYPEK").Value = "sii"  '¤W¥« = sii,¤WÂd = otc,¿³Âd = rotc,¤½¶}µo¦æ = pub
  30.     .getelementbyID("year").Value = "91"    '¦~«×
  31.     .getelementbyID("smonth").Value = "07"  '°_¤ë¥÷
  32.     .getelementbyID("emonth").Value = "09"  '°W¤ë¥÷
  33.         For i = 0 To .getelementsbytagname("input").Length - 1
  34.             If .getelementsbytagname("input")(i).Type = "button" And .getelementsbytagname("input")(i).Value = " ·j´M " Then
  35.                 .getelementsbytagname("input")(i).Click
  36.             End If
  37.         Next
  38.         µ¥«Ýºô­¶
  39.         Set AA = .getelementsbytagname("table")   '¸ê®Æ°Ï
  40.     End With
  41.     T = Time
  42.     With ActiveSheet

  43.         .Cells(1).Select
  44.         Application.ScreenUpdating = False
  45.         k = 1
  46.         On Error Resume Next
  47.         For xx = ºô­¶ªí®æ(¸ê°T) To AA.Length - 1
  48.             For i = 0 To AA(xx).Rows.Length - 1      '¼g¤J¸ê®Æ
  49.                 k = k + 1
  50.                 For j = 0 To 19
  51.                     Application.StatusBar = "¤U¸ü¸ê®Æ¤¤ ..." & k - 1
  52.                     .Cells(k, j + 1) = AA(xx).Rows(i).Cells(j).innertext
  53.                 Next
  54.             Next
  55.         Next
  56.         Set AA = .Range("R3:R" & .[R3].End(xlDown).Row)
  57.         AA.Replace "¬O§_¥Ó³ø«ù", "", xlWhole
  58.         AA.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  59.         .Cells(.Rows.Count, 1).End(xlUp).EntireRow.Delete
  60.         .Cells.EntireColumn.AutoFit
  61.         .[A1].Select
  62.     End With
  63.     Application.ScreenUpdating = True
  64.     ie.Quit
  65.     Application.StatusBar = "¤U¸ü¸ê®Æ®É¶¡ : " & Format(Time - T, ["S¬í"])
  66.     End
  67. ie_err:
  68.     ie.Quit
  69.     MsgBox "ºô­¶¦³°ÝÃD,½Ð­«·s°õ¦æ..."
  70.     End
  71. ¸ê°T_Err:
  72. End Sub
  73. Private Sub µ¥«Ýºô­¶()    'µ¥«Ýºô­¶¤U¸ü¸ê®Æ§¹²¦ªº®É¶¡
  74.     Dim Tt(1 To 3) As Date
  75.     Tt(1) = Time
  76.     Tt(2) = Time
  77.     Tt(3) = #12:00:10 AM#     '10¬í ½Õ¾ã³o¸Ì
  78.     Do
  79.         If Time > Tt(2) Then
  80.             Application.StatusBar = "ºô­¶¤U¸ü¤¤ ³Ñ¾l¬í¼Æ.. " & Second(Tt(1) + Tt(3) - Time)
  81.             Tt(2) = Time
  82.         End If
  83.         DoEvents
  84.     Loop Until Time > Tt(1) + Tt(3)
  85.     Application.StatusBar = "¸ê®Æ¤U¸ü¤¤...."
  86. End Sub
  87. Private Function ¤U¸üºô§}(xWord As String) As String      '¶Ç¦^¿ï¾Üªººô§}
  88.     Select Case xWord
  89.         Case "1"
  90.             ¤U¸üºô§} = "http://mops.twse.com.tw/mops/web/t56sb21_q3" '¾ú¥v«ùªÑÂàÅý
  91.         Case "2"
  92.             ¤U¸üºô§} = "http://mops.twse.com.tw/mops/web/t51sb13" '¦X¨Ö·l¯qªí
  93.         ''Case "3"     ·sºô­¶ªººô§}
  94.         ''
  95.         ''
  96.     End Select
  97. End Function
  98. Private Function ¥«³õ§O(xWord As String) As String
  99.        '¤W¥« = sii,¤WÂd = otc,¿³Âd = rotc,¤½¶}µo¦æ = pub
  100.     Select Case xWord
  101.         Case "sii"
  102.             ¥«³õ§O = "¤W¥«"
  103.         Case "otc"
  104.             ¥«³õ§O = "¤WÂd"
  105.         Case "rotc"
  106.             ¥«³õ§O = "¿³Âd"
  107.         Case "pub"
  108.             ¥«³õ§O = "¤½¶}µo¦æ"
  109.     End Select
  110. End Function
  111. Private Function ºô­¶ªí®æ(xWord As String) As Integer
  112.     Select Case xWord
  113.         Case "1"
  114.             ºô­¶ªí®æ = 11
  115.         Case "2"
  116.             ºô­¶ªí®æ = 12  '¦X¨Ö·l¯qªí
  117.         
  118.         ''¨C¤@ºô­¶ªºªí³æ«Ø¸m¤£¤@¼Ë,»Ý¤@¤@¥h´M§ä
  119.         '' ¦Û¦æ¬d¬Ý·s¼W Case "3"......
  120.         ''
  121.     End Select
  122. End Function
½Æ»s¥N½X

§@ªÌ: diabo    ®É¶¡: 2013-3-30 23:19

¦^´_ 1# spermbank

«Øij±q¸ÑªR­ì©l¸ê®Æºô§}µÛ¤â¡A­Y¸Óºô­¶ª©­±³]­pÅܧó¡Aµ{¦¡½X¤£»Ý­×§ï¡A¤ñ¸û¤£®e©ö¨ü¨ì¼vÅT¡Cµ{¦¡½X¥Î¿ýªº(WEB¬d¸ß-Querytable)´N¥i¥H¨ú±o.....´N¤£¼g¤F....

¹ê¬IIFRS¤§«e[101¦~²Ä4©u(§t)¥H«e]

­Ó§O·l¯qªí
http://mops.twse.com.tw/mops/web/ajax_t51sb08?encodeURIComponent=1&step=1&firstin=1&off=1&TYPEK=sii&year=101&season=04

­Ó§O¸ê²£­t¶Åªí
http://mops.twse.com.tw/mops/web/ajax_t51sb07?encodeURIComponent=1&step=1&firstin=1&off=1&TYPEK=sii&year=101&season=04

¦X¨Ö·l¯qªí
http://mops.twse.com.tw/mops/web/ajax_t51sb13?encodeURIComponent=1&step=1&firstin=1&off=1&TYPEK=sii&year=101&season=04

¦X¨Ö¸ê²£­t¶Åªí
http://mops.twse.com.tw/mops/web/ajax_t51sb12?encodeURIComponent=1&step=1&firstin=1&off=1&TYPEK=sii&year=101&season=04

¤W¥« TYPEK=sii
¤WÂd TYPEK=otc
year = ¥Á°ê¦~, ¨Ò 102
season = ©u«×, ¨Ò 01, 02, 03, 04
§@ªÌ: diabo    ®É¶¡: 2013-3-30 23:21

¦^´_ 5# HSIEN6001

¦P¤W¤@½g¦^ÂСA²¤Æ°ÝÃD±q·½ÀY¶}©l....

http://mops.twse.com.tw/mops/web/ajax_t56sb21?encodeURIComponent=1&run=Y&step=1&TYPEK=sii&year=102&smonth=01&emonth=03&sstep=1&firstin=true
§@ªÌ: diabo    ®É¶¡: 2013-3-31 00:09

¦^´_ 1# spermbank
  1. Sub §ì°]³ø(°]³ø§O, ¥«³õ§O, ¦~, ©u)
  2.    
  3.     Select Case °]³ø§O
  4.         Case "·l¯q":     µ{¦¡ = "ajax_t51sb08"
  5.         Case "¸ê²£":     µ{¦¡ = "ajax_t51sb07"
  6.         Case "¦X¨Ö·l¯q": µ{¦¡ = "ajax_t51sb13"
  7.         Case "¦X¨Ö¸ê²£": µ{¦¡ = "ajax_t51sb12"
  8.     End Select
  9.    
  10.     Select Case ¥«³õ§O
  11.         Case "¤W¥«": ¥«³õ = "sii"
  12.         Case "¤WÂd": ¥«³õ = "otc"
  13.         Case "¿³Âd": ¥«³õ = "rotc"
  14.         Case "¤½¶}": ¥«³õ = "pub"
  15.     End Select
  16.    
  17.    '­ì©lºô§} http://mops.twse.com.tw/mops/web/ajax_t51sb07?encodeURIComponent=1&step=1&firstin=1&off=1&TYPEK=sii&year=101&season=04
  18.     theURL = "http://mops.twse.com.tw/mops/web/" & µ{¦¡ & "?encodeURIComponent=1&step=1&firstin=1&off=1&TYPEK=" & ¥«³õ & "&year=" & ¦~ & "&season=" & ©u
  19.    
  20.     With ActiveSheet.QueryTables.Add(Connection:="URL;" & theURL, Destination:=Range("A1"))
  21.         .Name = "§ì°]³ø"
  22.         .BackgroundQuery = True
  23.         .RefreshStyle = xlOverwriteCells
  24.         .RefreshPeriod = 0
  25.         .WebSelectionType = xlEntirePage
  26.         .WebFormatting = xlWebFormattingNone
  27.         .WebDisableDateRecognition = False
  28.         .WebDisableRedirections = False
  29.         .Refresh BackgroundQuery:=False
  30.         .Delete
  31.     End With
  32.    
  33. End Sub
½Æ»s¥N½X
1. ¥H¤Wµ{¦¡½X¶È§ì¸ê®Æ¡A¨Ã¥¼³B²zª÷¿Ä«OÀIÃÒ¨é...ÃþªÑ·|­p¬ì¥Ø¤£¦Pªº°ÝÃD
2. ¨Ï¥Î½d¨Ò  call §ì°]³ø( "·l¯q","¤WÂd",101,"04")
§@ªÌ: spermbank    ®É¶¡: 2013-3-31 01:50

¦^´_ 4# GBKEE


    G¤j±z¦n:
            
            ¤Q¤À·PÁ¤j¤jªº«ü±Ð¡A¸U¤À·P®¦¡A¦pªG¨S¦³¤j¤jªºÀ°¦£¡A§Úªºµ{¦¡¯uªº¤£ª¾¹D¦p¦ó¯à«e¶i
            ±q¥h¦~¥H¨Ó¡A§Ú³°Äò§Ú¦³«Ü¦h°ÝÃD¡A³£¬OG¤jªº¼ö¤ßÀ°¦£¡A§Úªº·Qªk¡A¤~¯àÂǥѵ{¦¡ºCºC°Â¥X·Q­nªºªF¦è
            ¯uªº¤Q¤À·P¿E

            ¤£¹LÁÙ¬O¦³´X­Ó°ÝÃD·Q°Ý ><"
            ºô­¶ªí®æ = 11
            ºô­¶ªí®æ = 12  '¦X¨Ö·l¯qªí
            ''¨C¤@ºô­¶ªºªí³æ«Ø¸m¤£¤@¼Ë,»Ý¤@¤@¥h´M§ä
            '' ¦Û¦æ¬d¬Ý·s¼W Case "3"......
           ½Ð°Ý¼Æ¦r11»P12­n¦p¦ó´M§ä?

          ¥t¥~¨â­Óºô­¶§Ú¤]·QŪ¤J
          http://mops.twse.com.tw/mops/web/t21sc04  '¨C¤ëÀç·~¦¬¤J²Î­p·JÁ`ªí¡A "¦¹ºô­¶·|¸õ¥X¥t¤@ºô­¶"
          http://mops.twse.com.tw/mops/web/t21sb06 '¨C¤ë¦X¨ÖÀç·~¦¬¤J²Î­p·JÁ`ªí
          §Ú¤S­n¦p¦ó¶×¤J©O? ÁÂÁ¡C
§@ªÌ: HSIEN6001    ®É¶¡: 2013-3-31 08:36

¦^´_ 8# diabo

ÁÂÁÂ,³oUR¸Õ¹L¤F,¥i±¤§ì¤U¨Ó¤å¦r¬O¶Ã½X
G¤jªº´ú¸ÕOK,¦ýÁÙ¦³³\¦h¤£À´,µ¥«Ý«üÂI ^__^
§@ªÌ: diabo    ®É¶¡: 2013-3-31 09:56

¦^´_ 11# HSIEN6001


    À˵øIEªº½s½X¡A¬O§_¿ï¾Ü¡u¦Û°Ê½s½X¡v......
§@ªÌ: HSIEN6001    ®É¶¡: 2013-3-31 16:47

¦^´_ 12# diabo

¤£À´­C!
¦]¬°¸ÓURª½±µÅã¥Ü¶Ã½X,©Ò¥H§Ú¤U¸ü¤]¬O¶Ã½X

½Ð°Ý±z»¡ªº¬O±Nµ{¦¡½X¼g¦bVBAùØÀY±Æ°£¶Ã½X°ÝÃD?!
¥i§_¥Ü½d?!
§@ªÌ: HSIEN6001    ®É¶¡: 2013-3-31 16:56

¦^´_ 4# GBKEE


    G¤j,¦P#5 ¤Î #10¤§°ÝÃD

            ºô­¶ªí®æ = 13  '·l¯qªí
            ºô­¶ªí®æ = 12  '¦X¨Ö·l¯qªí

¥i§_½Ð°Ý13»P12¼Æ¾ÚÀ³¸Ó¦p¦ó§PÂ_³o¼Æ­È?!
¨Ò¦p:http://mops.twse.com.tw/mops/web/t56sb21_q3
§@ªÌ: GBKEE    ®É¶¡: 2013-3-31 17:09

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-3-31 18:38 ½s¿è

¦^´_ 13# HSIEN6001
§R±¼ encodeURIComponent=1

¦^´_ 14# HSIEN6001
¦³ diabo  ´£¨Ñªººô§}¦n¿ì¨Ç
  1. Option Explicit
  2. Sub Ex()
  3. Dim ie As Object, aa As Object, k As Integer, xx As Integer, i As Integer, j As Integer
  4. ActiveSheet.Cells.Clear
  5. Set ie = CreateObject("InternetExplorer.Application")
  6.     ie.Navigate "http://mops.twse.com.tw/mops/web/ajax_t51sb13?step=1&firstin=1&off=1&TYPEK=sii&year=100&season=04"
  7.      Do While ie.Busy Or ie.ReadyState <> 4:   DoEvents:     Loop
  8.     On Error Resume Next
  9.     Set aa = ie.Document.getelementsbytagname("table")   'ªí®æ¸ê®Æ°Ï
  10.      k = 1
  11.     On Error Resume Next
  12.         For xx = 0 To aa.Length - 1
  13.         '§ä¨ì«á­×§ï For xx = ??? To aa.Length - 1
  14.             For i = 0 To aa(xx).Rows.Length - 1      '¼g¤J¸ê®Æ
  15.                 Cells(k, 1) = "Table" & xx
  16.                 k = k + 1                            '¦C¼Æ
  17.                 For j = 0 To 19
  18.                     Application.StatusBar = "¤U¸ü¸ê®Æ¤¤ ..." & k - 1
  19.                     Cells(k, j + 2) = aa(xx).Rows(i).Cells(j).innertext
  20.                 Next
  21.             Next
  22.         Next
  23.     ie.Quit
  24. End Sub
½Æ»s¥N½X

§@ªÌ: HSIEN6001    ®É¶¡: 2013-3-31 19:44

¦^´_ 15# GBKEE

¥H102¦~1~3¤ë¬°¨Ò¡A¤W¥«sii¤Î¤WÂdotc:
ºô§}¬O¡G
http://mops.twse.com.tw/mops/web/ajax_t56sb21?&run=&step=1&TYPEK=sii&year=102&smonth=01&emonth=03&sstep=1&firstin=true                ¡¥¤W¥« sii ¤WÂd otc

¦]¬°¤U¸ü¤§«á§e²{¶Ã½X¡A©Ò¥H¤~¥ÎG¤j#4ªº¤è¦¡
®M¥ÎUR : http://mops.twse.com.tw/mops/web/t56sb21_q3?&step=0&firstin=1&off=1&TYPEK=sii&year=102&smonth=01&emonth=03  ¡¥¤W¥« sii ¤WÂd otc

´ú¸Õ§¹¾ã¥N½X©ó#6 ¡A´ú¸ÕOK ªº (¨S¶Ã½X)
¥u¬O¨Sªk²z¸Ñ----->ºô­¶ªí®æ 13 or 12
¹J¨ì¤£¦Pºô­¶¡A§P§O¤è¦¡¡H¡I

PS¡G­è­è¸Õ #15 ªº¥N½X¡A¤U¸ü¤]§e²{¶Ã½X¡C
§@ªÌ: HSIEN6001    ®É¶¡: 2013-3-31 19:57

¤£¦n·N«ä,¸É¥R»¡©ú¤@¤U
#16 ¨â­ÓURL ³£¬O«ü¦P¤@¥÷ªí®æ
§@ªÌ: diabo    ®É¶¡: 2013-3-31 21:54

¦^´_ 13# HSIEN6001


1.  EXCELªºWEB¬d¸ß¡A¤]¬O³z¹LIE¡A¦]¦¹IEªº³]©w·|¼vÅT¨ì¤U¸üªºµ²ªG¡C
2.  IE     À˵ø¡÷½s½X¡÷¦Û°Ê¿ï¨ú

[attach]14500[/attach]
§@ªÌ: GBKEE    ®É¶¡: 2013-4-1 15:18

¦^´_ 16# HSIEN6001
¥u¬O¨Sªk²z¸Ñ----->ºô­¶ªí®æ 13 or 12  ¹J¨ì¤£¦Pºô­¶¡A§P§O¤è¦¡¡H¡I

4# ³o¬qµ{¦¡½X
  1.       k = 1
  2. 47.        On Error Resume Next
  3. 48.        For xx = ºô­¶ªí®æ(¸ê°T) To AA.Length - 1
  4. 49.            For i = 0 To AA(xx).Rows.Length - 1      '¼g¤J¸ê®Æ
  5. 50.                k = k + 1
  6. 51.                For j = 0 To 19
  7. 52.                    Application.StatusBar = "¤U¸ü¸ê®Æ¤¤ ..." & k - 1
  8. 53.                    .Cells(k, j + 1) = AA(xx).Rows(i).Cells(j).innertext
  9. 54.                Next
  10. 55.            Next
  11. 56.        Next
½Æ»s¥N½X
§ï¦¨15#³o¬qµ{¦¡½X
  1. 10.     k = 1
  2. 11.    On Error Resume Next
  3. 12.        For xx = 0 To aa.Length - 1  
  4. 13.       '¤£¦Pºô­¶¡A§P§O¤è¦¡ '§ä¨ì«á­×§ï For xx = ??? To aa.Length - 1        
  5. 14.            For i = 0 To aa(xx).Rows.Length - 1      '¼g¤J¸ê®Æ
  6. 15.                Cells(k, 1) = "Table" & xx
  7. 16.                k = k + 1                            '¦C¼Æ
  8. 17.                For j = 0 To 19
  9. 18.                    Application.StatusBar = "¤U¸ü¸ê®Æ¤¤ ..." & k - 1
  10. 19.                    Cells(k, j + 2) = aa(xx).Rows(i).Cells(j).innertext
  11. 20.                Next
  12. 21.            Next
  13. 22.        Next
½Æ»s¥N½X

§@ªÌ: HSIEN6001    ®É¶¡: 2013-4-2 00:11

¥»©«³Ì«á¥Ñ HSIEN6001 ©ó 2013-4-2 00:13 ½s¿è

¦^´_ 19# GBKEE
¦^´_ 18# diabo

ÁÂÁ¨â¦ìÀ°¦£¸Ñ´b
§A­Ì³£¦n´Î³á!
·P®¦ ^__^
§@ªÌ: tsuneng    ®É¶¡: 2013-4-21 18:06

·P®¦¤j¤j¤À¨É.
§@ªÌ: randomwalk    ®É¶¡: 2013-5-17 11:18

¦^´_ 8# diabo


    ½Ð±Ðdiabo¥S
    ¦p¦óÀ˵ø­ì©lºô§}?
    ·sª©ªº¤½¶}¸ê°T¯¸¨ÃµLªkÀ˵ø,
    ±æ¤£§[«ü¾É,ÁÂÁÂ




Åwªï¥úÁ{ ³Â»¶®a±Ú°Q½×ª©ª© (http://forum.twbts.com/)