GBKEE

¦^´_ 1# wufonna
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub Ex¤U¸üCSV()
   
3.     Dim xml As Object
   
4.     Dim stream
   
5.     Dim URL As String     '¥Øªººô§}
   
6.     Set xml = CreateObject("Microsoft.XMLHTTP") '¥Î¨Ó¨ú±oºô­¶¸ê®Æ
   
7.     Set stream = CreateObject("ADODB.stream")   'ADODB.stream   '¥Î¨ÓÀx¦s¤G¶i¦ìÀÉ®×
   
8.     URL = "http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/STOCK_DAY_print.php?genpage=genpage/Report201408/201408_F3_1_8_2485.php&type=csv"
   
9.     xml.Open "get", URL, 0
   
10.     xml.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    
11.     xml.send
    
12.     With stream
    
13.         .Open
    
14.         .Type = 1
    
15.         .write xml.ResponseBody
    
16.         'SaveToFile:ÀɮצWºÙ¤w¦s¦b®É·|¦³¿ù»~,¶·¥ý§R°£¤w¦s¦bªºÀɮצWºÙ
    
17.         If Dir("D:\2485.CSV") <> "" Then Kill "D:\2485.CSV"
    
18.         .SaveToFile ("D:\2485.CSV")
    
19.         .Close
    
20.     End With
    
21. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 3# wufonna
´«¤@­Ó¤è¦¡

1. Option Explicit
   
2. Sub Ex_­ÓªÑ¤ë¦¨¥æ¸ê°T()
   
3.     Dim i As Integer, S As Integer, k As Integer, A As Object, ii, j
   
4.     With CreateObject("InternetExplorer.Application")
   
5.        ' .Visible = True
   
6.         .Navigate "http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/STOCK_DAY_print.php?genpage=genpage/Report201408/201408_F3_1_8_2485.php&type=list"
   
7.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
   
8.         Ep .document.getElementsByTagName("table")(1).outerHTML
   
9.         .Quit        'Ãö³¬ºô­¶
   
10.     End With
    
11. End Sub
    
12. Sub Ep(S As String)
    
13.     Dim D As New DataObject
    
14.     'DataObject ª«¥ó ¦b¶i¦æÂà´«°Ê§@®É¡A°µ¬°®æ¦¡¤Æ¤å¦r¸ê®Æªº¼È¦s°Ï°ì¡C¨ä¤]¥i¥H¼È¦s©MÀx¦s¦b DataObject ªº¤å¦r¤ù¬q¬ÛÃöªº®æ¦¡¡C
    
15.     '«Å§i Dim D As New DataObject '¶·¦b¤u¨ã-> ³]©w¤Þ¥Î¶µ¥Ø¥[¤J ·s¼W¤Þ¥Î Microsoft Forms 2.0 Object Library ,©Î©ó±M®× ¥[¤J¤@ªí³æ§Y¥i
    
16.     With D
    
17.         .SetText S
    
18.         .PutInClipboard
    
19.         With ActiveSheet
    
20.             .UsedRange.Clear
    
21.             .Range("a1").Select
    
22.             .PasteSpecial Format:="Unicode ¤å¦r"
    
23.         End With
    
24.     End With
    
25. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 6# wufonna
Set  CreateObject("InternetExplorer.Application")=Nothing
¤£¬OÅܼƬO¤@­Ó¹ê»Úªºª«¥ó.
Nothing ÃöÁä¦r¬O¥Î¨Ó±N¤@­Óª«¥óÅܼƱq¤@­Ó¹ê»Úªºª«¥óùؤÀÂ÷¶}¨Ó¡C¨Ï¥Î Set ³¯­z¦¡¥i«ü©w Nothing µ¹ª«¥óÅܼơC

­×§ï¬Ý¬Ý

1. Option Explicit
   
2. Dim IE As Object  '«Å§i¬°³o¼Ò²Õªº¨p¥ÎÅܼÆ(³o¼Ò²Õ¤¤ªºµ{§Ç¥i©I¥sªºÅܼÆ)
   
3. Sub Index()
   
4. Dim i As Integer, I2 As Integer
   
5. Dim x, y, XX, YY, ZZ
   
6. Dim k
   
7. Set IE = CreateObject("InternetExplorer.Application")
   
8. Const AA As Integer = 5
   
9. ¤u§@ªí3.Activate
   
10. '¤u§@ªí3.Select
    
11. With ¤u§@ªí1
    
12. ''''''''¥¼´ú¸Õ
    
13.     For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row   
    
14. Call Ex_1(Split(Date, "/")(0) & Format((Split(Date, "/")(1)), "00"), I2)
    
15.     Next
    
16. '''''''¥¼´ú¸Õ
    
17. End With
    
18. End If
    
19. IE.Quit  '¦bSub Index() µ{¦¡µ²§ô«eÃö³¬ºô­¶
    
20. End Sub
    
21. Sub Ex_1(SS As String, MM As Integer)
    
22.     Dim i As Integer, S As Integer, k As Integer, A As Object, II, j
    
23. '    ¤u§@ªí3.Select
    
24.     With IE
    
25.        ' .Visible = True
    
26.        On Error Resume Next
    
27.         .Navigate "http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY/STOCK_DAY_print.php?genpage=genpage/Report" & SS & "/" & SS & "_F3_1_8_" & MM & ".php&type=list"
    
28.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
    
29.         Ep .document.getElementsByTagName("table")(1).outerHTML
    
30.        ' .Quit        'Ãö³¬ºô­¶
    
31.     End With
    
32. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 9# wufonna


IE.Quit  '¦bSub Index() µ{¦¡µ²§ô«eÃö³¬ºô­¶
¨S¦³IE.Quit ,Ãö³¬ExcelÀ³¥Îµ{¦¡«áIEÁÙ¬O¶}±Òªº,¦]IE.Visible = False (ª«¥ó¨S¦³Åã¥Ü.)
Set IE = Nothing,ÄÀ©ñ¤FÅܼƩҵ²¦Xªºª«¥ó,ExcelÀ³¥Îµ{¦¡¤¤,¥i´î¤Ö¦û¥Îªºªº°O¾ÐÅé