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

ªÑ»ù¥N¸¹©M°_©l¤é´Áªº§ì¨ú

«ü©w¤U¸ü3¦~¾ú¥vªÑ»ùªº¸ê®Æ

³Â·Ð½Ð¦U¦ì°ª¤â±M®a¤Îª©¥D«ü¾É¸Ñ´b¡G
¤U­±ºô§}¤¤¥i«ü©w¬d¸ß¾ú¥v¸ê®Æªº°Ï¶¡¿ï¶µ(¹w³]­È¬°20¤Ñ)¡A¦³¥ý¦bºô¯¸¤¤§ó§ï¬d¸ß°Ï¶¡¬°3¦~¡C
¦ý¨C¦¸¤U¸ü¶×¤JEXCELªº¸ê®Æ¥u¦³21¤Ñ¡A¤£ª¾»Ý­n¥[¤J¤°»òµ{¦¡½X¤~¥i¸Ñ¨M¡H¡H
www.cnyes.com/twstock/ps_historyprice/6282.htm
  1. Sub Macro1()

  2.     With ActiveSheet.QueryTables.Add(Connection:= _
  3.         "URL;http://www.cnyes.com/twstock/ps_historyprice/6282.htm", Destination:= _
  4.         Range("A1"))
  5.         .RefreshStyle = xlOverwriteCells
  6.         .WebTables = "2"
  7.         .Refresh BackgroundQuery:=False
  8.     End With
  9.     ActiveWindow.SmallScroll Down:=-21

  10. End Sub
½Æ»s¥N½X
¦]¬°YAHOO¡BGOOGLEªº¾ú¥v¸ê®Æ¦³®ÉµLªkÅã¥Ü¨Ò¥~(¶}¥ð¥«)ªº¼Æ¾Ú¡A³y¦¨­pºâMA­Èªº¼Æ¾Ú¤£·Ç½T¡C
·PÁ¦U¦ì°ª¤â±M®a¼·ªÅ¸Ñ´b¡C

TOP

¦^´_ 1# gelai01000001
¤U¸ü3¦~¾ú¥vªÑ»ùªº¸ê®Æ
  1. Option Explicit
  2. Sub ¹d¦ëºô_¾ú¥v¦æ±¡()
  3.     Dim Sh As Worksheet, Code As String, d_Start As String, d_End  As String
  4.     Dim A As Object, i As Integer, c As Integer, T As Date
  5.     Code = InputBox("¿é¤JªÑ²¼¥N¸¹ :  ", "ªÑ²¼¥N¸¹", 2303)
  6.     d_End = InputBox("¿é¤Jµ²§ô¤é´Á :  ", "µ²§ô¤é´Á", Date)
  7.     If Len(Code) <= 3 Or Not IsDate(d_End) Then Exit Sub
  8.     Set Sh = ActiveSheet
  9.     With Sh
  10.         .UsedRange.Clear
  11.         .[a1] = Code
  12.         .[b1] = DateAdd("yyyy", -3, d_End)  '¤U¸ü3¦~¾ú¥vªÑ»ùªº¸ê®Æ
  13.         .[C1] = d_End
  14.         Code = .[a1]
  15.         d_Start = Format(.[b1], "yyyy/mm/dd")
  16.         d_End = Format(.[C1], "yyyy/mm/dd")
  17.     End With
  18.     With CreateObject("InternetExplorer.application")
  19.         .Navigate "http://www.cnyes.com/twstock/ps_historyprice/" & Code & ".htm"
  20.         '.Visible = True
  21.         Application.StatusBar = Code & " ¾ú¥v¦æ±¡ µ¥­Ô¤¤..."
  22.         Do While .Busy Or .ReadyState <> 4
  23.              DoEvents
  24.         Loop
  25.         Set A = .Document.getelementsbytagname("input")
  26.         A(5).Value = d_Start
  27.         A(6).Value = d_End
  28.         A(7).Click
  29.         T = Time
  30.         Do
  31.             DoEvents
  32.         Loop Until Time > T + #12:00:08 AM#
  33.         Set A = .Document.getelementsbytagname("table")(1)
  34.         Application.StatusBar = Code & " ¾ú¥v¦æ±¡ ¤U¸ü¤¤..."
  35.         Cells(2, 1) = .Document.getelementsbytagname("span")(79).innertext
  36.         For i = 0 To A.Rows.Length - 1
  37.             For c = 0 To A.Rows(i).Cells.Length - 1
  38.                 Sh.Cells(i + 3, c + 1) = A.Rows(i).Cells(c).innertext
  39.             Next
  40.         Next
  41.        .Quit
  42.     End With
  43.     Application.StatusBar = Code & " ¾ú¥v¦æ±¡ " & Application.Text(Time - T, "[S] ¬í") & " ¤U¸ü§¹¦¨"
  44.     MsgBox "OK"
  45.     Application.StatusBar = False
  46. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE

·PÁÂGBKEEª©¥Dªº¼ö¤ß¥Î¤ßªº¸Ñ´b¡C
¦]¦ì¥»¤H¤£§÷¡A¤£¬Æ¤F¸Ñ¤U¦Cµ{¦¡½Xªº»yªk¤Î°õ¦æ¥Î·N¡C
ÁٷнÐGBKEEª©¥D©âªÅ¥i»¡©úµù¸Ñ´b«ü¾É¤@¤U¶Ü¡H
  1. With CreateObject("InternetExplorer.application")
  2.         .Navigate "http://www.cnyes.com/twstock/ps_historyprice/" & Code & ".htm"
  3.         '.Visible = True
  4.         Application.StatusBar = Code & " ¾ú¥v¦æ±¡ µ¥­Ô¤¤..."
  5.         Do While .Busy Or .ReadyState <> 4
  6.              DoEvents
  7.         Loop
  8.         Set A = .Document.getelementsbytagname("input")
  9.         A(5).Value = d_Start
  10.         A(6).Value = d_End
  11.         A(7).Click
  12.         T = Time
  13.         Do
  14.             DoEvents
  15.         Loop Until Time > T + #12:00:08 AM#
  16.         Set A = .Document.getelementsbytagname("table")(1)
  17.         Application.StatusBar = Code & " ¾ú¥v¦æ±¡ ¤U¸ü¤¤..."
  18.         Cells(2, 1) = .Document.getelementsbytagname("span")(79).innertext
½Æ»s¥N½X
GBKEEª©¥D¡G°õ¦æµ{¦¡¹Lµ{¤¤¡Aµo²{¤U¸ü¦ÜEXCEL¤¤ªº¸ê®Æ¬O¦³¥Ñ¡qSh.Cells(i + 3, c + 1) = A.Rows(i).Cells(c).innertext¡r¦Ó¨Ó¡A
                        ¤£¦P©ó¤U¸üºô­¶¸ê®Æ¡qWebTables = "number"¡r¡A©Ò¥Îªº«ü¥O¤èªk¡C
§Ú¦³¤Wºô¦¬·j´M¬ÛÃö¸ê®Æ¡A¤j¬ù²M·¡ª¾¹D¡A¤W¦Cµ{¦¡½X¦¡¬O¦bVBAµ{¦¡¤¤±Ò°Êºô­¶ªº(javascript)«ü¥O¡C
¤£ª¾¬O»P¤£¬O©Î¤@ª¾¥b¸Ñ¡I·PÁÂGBKEEªº«ü¾É¡C

TOP

¦^´_ 3# gelai01000001
CreateObject("InternetExplorer.application") ¦h·j´M¤@¤U·|¦h¤F¸Ñªº
  1. With CreateObject("InternetExplorer.application")
  2.     '¾ú¥v¦æ±¡ ¹w³]¬°¤@­Ó¤ë¸ê®Æ,¤é´Áªº§ó§ï»Ý¦bºô­¶¤W§ó§ï
  3.         .Navigate "http://www.cnyes.com/twstock/ps_historyprice/" & Code & ".htm"  'IEªººô§}
  4.         '.Visible = True
  5.         Application.StatusBar = Code & " ¾ú¥v¦æ±¡ µ¥­Ô¤¤..."
  6.         Do While .Busy Or .ReadyState <> 4     'µ¥­Ôºô­¶¸ê®Æ¤U¸ü§¹¦¨...
  7.              DoEvents
  8.         Loop
  9.         Set A = .Document.getelementsbytagname("input")  'ºô­¶¤¤¦¨­û tagname¬° "input"
  10.         A(5).Value = d_Start    '0 ¶}©lºâ°_²Ä6­Ó => 5     ¶}©l¤é´Á
  11.         A(6).Value = d_End      '0 ¶}©lºâ°_²Ä7­Ó => 6     µ²§ô¤é´Á
  12.         A(7).Click              '0 ¶}©lºâ°_²Ä8­Ó => 7     «ö¤U¬d¸ßÁä
  13.         T = Time
  14.         Do
  15.             DoEvents
  16.         Loop Until Time > T + #12:00:08 AM#              '«ö¤U¬d¸ßÁä«á,»Ýµ¥­Ôºô­¶¸ê®Æ¤U¸ü§¹¦¨
  17.         Set A = .Document.getelementsbytagname("table")(1) ''0 ¶}©lºâ°_²Ä2­Ó => 1
  18.         'ºô­¶¤¤¦¨­û tagname¬° "table" ,¬°¸ê®Æ©Ò¦bªº¦ì¸m.
  19.         Application.StatusBar = Code & " ¾ú¥v¦æ±¡ ¤U¸ü¤¤..."
  20.         Cells(2, 1) = .Document.getelementsbytagname("span")(79).innertext  'innertext: ¤å¦r
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

½Ð°Ý¤j¤j¤U¸ü®É¶¡¥i¥HÂX¤j¨ì¦hªø?:)

TOP

¦^´_ 5# edwardlay
2# ¬Ý­n´X¦~¥i­×§ï -3  ¬Ý¬Ý
  1. 12.        .[b1] = DateAdd("yyyy", -3, d_End)  '¤U¸ü3¦~¾ú¥vªÑ»ùªº¸ê®Æ
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦p¦ó±N¤U¸üªºªÑ¥«¨C¤é½L«á¦æ±¡(»s§@¦¨¦UªÑªº¾ú¥vªÑ»ù¸ê®Æ)

·Ð½Ð¦U¦ì°ª¤â±M®a¸Ñ´b¡AÁÂÁÂ!
½Ð°Ý¦³¿ìªk±N¤U¦Cµ{½Xªº¥[¥H­×§ï¡A§â¨C¤é¦UªÑªº½L«á¦æ±¡©Ò±oªº¸ê®ÆÂà´«¦¨(¦UªÑªº¾ú¥v¸ê®Æ)¡C
  1. Sub ¤U¸üºô¯¸¸ê®Æ()
  2.     Dim Startmonth As String, Startday As String, xDate As Date, Msg As Boolean, Sh As Worksheet
  3.     On Error GoTo EX                        'µ{¦¡°õ¦æ¤¤¦³¿ù»~®É¸õ¨ì EX: Ä~Äò°õ¦æµ{¦¡
  4.     xDate = Date                            '·í¤é
  5.     Set Sh = ActiveSheet                    '«ü©w¤u§@ªí:¦¹µ{¦¡¦b¥ô¦ó¼Ò²Õ¤¤¬Ò¥i¥Î
  6. EX:
  7.     If Err.Number <> 0 Or Msg = True Then   '·í¤é(©|¥¼¦³¸ê®Æ:¿ù»~) ©Î ¥ð¥«
  8.         [color=Red]xDate = xDate - 1    (À³§ï¬°xDate = Date - 1)[/color]     °²¦p¥ð¥«¶W¹L1¤Ñ¥H¤W(2,3,4,5,6,¬K¸`9¤Ñ)¡A¤S¦p¦ó­×§ï©O?         
  9.         Err.Clear
  10.         Msg = False
  11.     End If
  12.     Sh.UsedRange.Clear                       '²M²z¤u§@ªí¤w¨Ï¥Îªº½d³ò
  13.     Startday = Format(xDate, "YYYYMMDD")
  14.     Startmonth = Format(xDate, "YYYYMM")
  15.         '¤U¸üºô¯¸ªººô§}
  16.     With Sh.QueryTables.Add(Connection:="URL;http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/genpage/Report" & Startmonth & "/A112" & Startday & "ALLBUT0999_1.php?select2=ALLBUT0999&chk_date=" & Format(xDate, "E/MM/DD"), Destination:=Range("A3"))
  17.         .RefreshStyle = xlOverwriteCells                            '§ì¨úºô­¶ªº²Ä10­Óªí®æ§@¬°¶×¤J¸ê®Æ
  18.         .WebTables = "10"
  19.         .Refresh BackgroundQuery:=False                             '·í¤é(©|¥¼¦³¸ê®Æ:¿ù»~)
  20.         If Application.CountA(Sh.QueryTables(1).ResultRange) = 0 Then  '¥ð¥« ¨S¦³¸ê®Æ
  21.             Msg = True
  22.             GoTo EX
  23.         End If
  24.     End With
  25.     With Sh.UsedRange
  26.         .Font.Size = 12
  27.         .EntireColumn.AutoFit
  28.     End With
  29. End Sub
½Æ»s¥N½X
¦]¬°§Ú¤w¦³¤U¸ü¦UªÑ¾ú¥vªÑ»ùªº¸ê®Æµ{¦¡©M¸ê®Æ¡A¥B¨Ì¥N½X¤ÀÃþ¦¨¦h­Ó¬¡­¶Ã¯¨Ò¦p¡G
workbook(1.xls)¦³worksheet(1102)¡Bworksheet(1103).....µ¥¡C1¬¡­¶Ã¯¤¤¦³¥N½X¡A²Ä¤@­Ó¼Æ¦r¦³[1]ªº¦h­Ó¤u§@ªí¡C
workbook(2.xls)¦³worksheet(2102)¡Bworksheet(2103).....µ¥¡C2¬¡­¶Ã¯¤¤¦³¥N½X¡A²Ä¤@­Ó¼Æ¦r¦³[2]ªº¦h­Ó¤u§@ªí¡C
......
......
workbook(9.xls)¦³worksheet(9102)¡Bworksheet(9103).....µ¥¡C9¬¡­¶Ã¯¤¤¦³¥N½X¡A²Ä¤@­Ó¼Æ¦r¦³[9]ªº¦h­Ó¤u§@ªí¡C
©Ò¥H·Q½Ð°Ý¦³¨S¦³§ó²«K§Ö³tªº¤èªk¡CÁÂÁ¦U¦ì±M®a«e½úªº©âªÅ«ü¾É¡C

TOP

¦^´_ 7# gelai01000001
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub ¤U¸üºô¯¸¸ê®Æ()
  3.     Dim Startmonth As String, Startday As String, xDate As Date, Msg As Boolean, Sh As Worksheet
  4.     Dim Wb As Workbook, Ws As Worksheet, Stock  As Range
  5.     On Error GoTo EX                        'µ{¦¡°õ¦æ¤¤¦³¿ù»~®É¸õ¨ì EX: Ä~Äò°õ¦æµ{¦¡
  6.     xDate = Date                            '·í¤é
  7.     Set Sh = Workbooks.Add.Sheets(1)        '·s¬¡­¶Ã¯ªº²Ä¤@­Ó¤u§@ªí
  8. EX:
  9.     If Err.Number <> 0 Or Msg = True Then   '·í¤é(©|¥¼¦³¸ê®Æ:¿ù»~) ©Î ¥ð¥«
  10.         xDate = xDate - 1                   '©¹«á°h¤@¤Ñ¤@ª½¨ì¶}¥«. ( °²¦p¥ð¥«¶W¹L1¤Ñ¥H¤W(2,3,4,5,6,¬K¸`9¤Ñ)
  11.         Err.Clear
  12.         Msg = False
  13.     End If
  14.     Startday = Format(xDate, "YYYYMMDD")
  15.     Startmonth = Format(xDate, "YYYYMM")
  16.         '¤U¸üºô¯¸ªººô§}
  17.     With Sh.QueryTables.Add(Connection:="URL;http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/genpage/Report" & Startmonth & "/A112" & Startday & "ALLBUT0999_1.php?select2=ALLBUT0999&chk_date=" & Format(xDate, "E/MM/DD"), Destination:=Range("A3"))
  18.         .RefreshStyle = xlOverwriteCells                                '§ì¨úºô­¶ªº²Ä10­Óªí®æ§@¬°¶×¤J¸ê®Æ
  19.         .WebTables = "10"
  20.         .Refresh BackgroundQuery:=False                                 '·í¤é(©|¥¼¦³¸ê®Æ:¿ù»~)
  21.         If Application.CountA(Sh.QueryTables(1).ResultRange) = 0 Then   '¥ð¥« ¨S¦³¸ê®Æ
  22.             Msg = True
  23.             GoTo EX
  24.         End If
  25.     End With
  26.     For Each Wb In Workbooks
  27.         For Each Ws In Wb.Sheets
  28.            Set Stock = Sh.[A:A].Find(Ws.Name, lookat:=xlWhole)
  29.             If Not Stock Is Nothing Then
  30.                 Stock.Offset(, 2).Resize(, 14).Copy
  31.                 With Ws
  32.                     With .Range("a" & .Rows.Count).End(xlUp).Offset(1)
  33.                         .Cells = xDate
  34.                         .Offset(, 1).PasteSpecial xlPasteValuesAndNumberFormats
  35.                     End With
  36.                 End With
  37.             End If
  38.         Next
  39.     Next
  40.     Sh.Parent.Close False           'Ãö³¬: ·s¬¡­¶Ã¯ªº²Ä¤@­Ó¤u§@ªí
  41. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦p¦ó¨Ï¥Î.iqy§ì¦¹ºô§}¸ê®Æ

½Ð°Ý¤j®a,

        ¤p§Ì¦b¦¹ºô§} http://www.cnyes.com/twstock/directorholder/1101.htm  ,·Q§ì¸³ºÊ«ùªÑ¾lÃB¸ê®Æ,
¦ý¨C­Ó¤ëªº¸ê®Æ¬O¥Î¤U©Ô¦¡¿ï³æ¿ï¨ú,ºô§}³£¤£·|ÅÜ°Ê,½Ð°ÝÀ³¸Ó¦p¦ó¤~¯à§Q¥Î.iqy§ó§ï°Ñ¼Æ§ì¸ê®Æ...


ÁÂÁ¦U¦ì
BigDog
¥Î²³æªº¤ß°µ½ÆÂøªº¨Æ

TOP

¦^´_ 1# BigDog


    ³o­ÓÀ³¸ÓµLªk¥Îiqy §ì¨ú
­n¥ÎVBA

TOP

        ÀR«ä¦Û¦b : ·R¤£¬O­n¨D¹ï¤è¡A¦Ó¬O­n¥Ñ¦Û¨­ªº¥I¥X¡C
ªð¦^¦Cªí ¤W¤@¥DÃD