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

[µo°Ý] ¥æ©ö©Òºô¯¸ªº¦¬½L»ù¤wÅܧó?¥Î°ÊºA¬d¸ß¤w¥¢®Ä?

[µo°Ý] ¥æ©ö©Òºô¯¸ªº¦¬½L»ù¤wÅܧó?¥Î°ÊºA¬d¸ß¤w¥¢®Ä?

¦p¦óª½±µ§ì¨ú¶×¤Jexcel??
http://www.tse.com.tw/ch/trading/exchange/MI_INDEX/MI_INDEX.php
¤ÀÃþ¶µ¥Ø ==>¥þ³¡(¤£§tÅvÃÒ¡A¤ûºµ........)

¦^´_ 1# t8899

´«¤F,¥Î³o­Ó!

Sub XL()
Set XML = CreateObject("Microsoft.XMLHTTP")
Set stream = CreateObject("ADODB.stream")
Dim path As String, thePOSTdata, URL
path = "C:\"
¤é´Á = "1031226"

    URL = "http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/MI_INDEX.php"
    thePOSTdata = "download=csv&qdate=" & ¤é´Á & "&selectType=ALLBUT0999"
        XML.Open "POST", URL, 0
        XML.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        XML.send thePOSTdata
    With stream
        .Open
        .Type = 1
        .write XML.responseBody
        If Dir(path & "¦¬½L" & ¤é´Á & ".csv") <> "" Then Kill (path & "¦¬½L" & ¤é´Á & ".csv")
        .SaveToFile (path & "¦¬½L" & ¤é´Á & ".csv")
        .Close
    End With

End Sub

TOP

¦^´_  t8899

´«¤F,¥Î³o­Ó!

Sub XL()
Set XML = CreateObject("Microsoft.XMLHTTP")
Set stream =  ...
HSIEN6001 µoªí©ó 2014-12-26 21:47

1.«e­±ªº¤j½L²Î­p¸ê°T¥i¥H¤£­n¶Ü ??§Ú¥u­n¨C¤é¦¬½L¦æ±¡(¥þ³¡(¤£§tÅvÃÒ¡B¤ûºµÃÒ)")"
2.±z¬O¦s¦¨csv ÀÉ¡AÁÙ­n¦A¥s¶i¨Ó,¥iª½±µ¶×¤J sheet1 ¶Ü?

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-12-28 07:24 ½s¿è

¦^´_ 3# t8899

¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex_½L«á¸ê°T_¨C¤é¦¬½L¦æ±¡()
  3.     Dim A As Object, xDate As Date, EDATE As Date
  4.     '***********´ú¸Õ¥Î
  5.     '§ì¨ì¦³¬°¤î(¥u§ì5¤Ñ)¡A5¤Ñ³£§ì¤£¨ì¤]´£¥Ü
  6.     EDATE = Date + 5
  7.     xDate = EDATE
  8.     '*************
  9.     'xDate = Date    '¥¿¦¡±`µ{¦¡½X
  10.     With CreateObject("InternetExplorer.Application")
  11.         .Visible = True
  12.         .Navigate "http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/MI_INDEX.php"
  13.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  14. Ie_Refresh:
  15.         With .Document
  16.             .ALL("qdate").Value = Format(xDate, "E/MM/DD") '¤é´Á¥i­×§ï
  17.             .ALL("selectType").Value = "MS"
  18.             .ALL("query-button").Click
  19.         End With
  20.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  21.         If InStr(.Document.BODY.innerText, "¬dµL¸ê®Æ") Then
  22.             If xDate + 4 >= EDATE Then  '´ú¸Õ¥Î********
  23.             'If xDate + 4 >= Date Then   '¥¿¦¡±`µ{¦¡½X
  24.                 Debug.Print xDate       'ÅçÃҥΠ¥i§R°£
  25.                 xDate = xDate - 1
  26.                 GoTo Ie_Refresh
  27.             End If
  28.              .Quit
  29.             MsgBox Format(xDate, "E/MM/DD") & " ¬dµL¸ê®Æ"
  30.             Exit Sub
  31.            
  32.         End If
  33.         Set A = .Document.getElementsByTagName("table")
  34.         .Document.BODY.innerHTML = A(A.Length - 1).outerHTML '¨ú³Ì«áªº¤@­Ó"table"
  35.         
  36.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  37.         .ExecWB 17, 2       '  Select All
  38.         .ExecWB 12, 2       '  Copy selection
  39.         .Quit        'Ãö³¬ºô­¶
  40.          With ActiveSheet    '¥i«ü©w¤u§@ªí
  41.             .UsedRange.Clear
  42.             .Range("A1").Select
  43.             .PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NOHTMLFormatting:=True
  44.         End With
  45.            End With
  46. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  t8899

¸Õ¸Õ¬Ý
GBKEE µoªí©ó 2014-12-27 11:43

ÁÂÁ¡A¥i¥H
¥i§_¦A¥[¤J
°²¦p§ì·í¤Ñ¤é´Áºô¯¸ÁÙ¨S²£¥Í(§ì¤£¨ì)«h§ì«e¤@¤Ñ.....§ì¨ì¦³¬°¤î(¥u§ì5¤Ñ)Á٧줣¨ì´N¸õ¥X
§ì¤£¨ì·í¤Ñ¡A«h´£¥Ü §ì¨ìªº¤é´Á(·í¤Ñ¤£¥Î)¡A5¤Ñ³£§ì¤£¨ì¤]´£¥Ü

TOP

½Ð°Ý GBKEE ºô¤j:
  §Ú±N.ALL("selectType").Value = "ALLBUT0999" ¡¥¥þ³¡(¤£§tÅvÃÒ¡B¤ûºµÃÒ)")
   §ï¦¨.ALL("selectType").Value = "MS"     '¤j½L²Î­p¸ê°T
µ{¦¡¶]¨ì  .BODY.innerHTML = .getElementsByTagName("table")(1).outerHTML
¥X²{ ¤U¦C°T®§:
¡§°õ¦æ¶¥¬q¿ù»~¡¨ 91¡¨ :
¨S¦³³]©wª«¥óÅܼƩΠWITH°Ï¶ôÅܼÆ

ÁÂÁÂ!

TOP

¥»©«³Ì«á¥Ñ t8899 ©ó 2014-12-28 07:24 ½s¿è
½Ð°Ý GBKEE ºô¤j:
  §Ú±N.ALL("selectType").Value = "ALLBUT0999" ¡¥¥þ³¡(¤£§tÅvÃÒ¡B¤ûºµÃÒ)")
   §ï¦¨. ...
tsuneng µoªí©ó 2014-12-27 16:15

§ï¬°
    .BODY.innerHTML = .getElementsByTagName("table")(0).outerHTML

TOP

¥»©«³Ì«á¥Ñ t8899 ©ó 2014-12-28 07:30 ½s¿è
¦^´_  t8899
¸Õ¸Õ¬Ý
GBKEE µoªí©ó 2014-12-27 11:43

¤U­±³o¨â¦æ¤£À´
    .ExecWB 17, 2       '  Select All
    .ExecWB 12, 2       '  Copy selection
17,2 ¸ò 12,2 ¬O«ç»òºâªº ??

TOP

·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  t8899
°Ñ¦Ò¤@¤U
GBKEE µoªí©ó 2014-12-28 07:39

ÃÒ¨é¥N¸¹  «e­±¬O¨â­Ó0Âà¤J·|¤£¨£ (·|·í¦¨¼Æ¦r) 0050 ===>  50
³o¦³µL¿ìªk­×¥¿?

TOP

        ÀR«ä¦Û¦b : §Ú­Ì³Ì¤jªº¼Ä¤H¤£¬O§O¤H¡D¥i¯à¬O¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD