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

VBA ¤U¸ü¥æ©ö©Ò¸ê®ÆETF«e­±ªº00·|¦Û°Ê¤£¨£¡A­n¦p¦ó­×¥¿

VBA ¤U¸ü¥æ©ö©Ò¸ê®ÆETF«e­±ªº00·|¦Û°Ê¤£¨£¡A­n¦p¦ó­×¥¿

VBA ¤U¸ü¥æ©ö©Ò¸ê®ÆETF«e­±ªº00·|¦Û°Ê¤£¨£¡A­n¦p¦ó­×¥¿
§Ú­n§¹¾ãÃÒ¨é¥N¸¹¦³¥X²{
¦p¡GÆä0050Æå Æ䤸¤j¥xÆW50Æå·|Åܦ¨Æä50Æå  Æ䤸¤j¥xÆW50Æå
001.png
2019-8-3 17:57


µ{¦¡½X¦p¤U
Sub test()

    Dim strGetDate As String    '«Å§i¤é´Á
    strGetDate = Format(Range("b1").Value, "YYYYMMDD")  '®æ¦¡¤Æ¤é´Á
   
   
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.twse.com.tw/exchangeReport/TWTB4U?response=html&date=20190805&selectType=All" _
        , Destination:=Range("$A$4"))
        .Refresh BackgroundQuery:=False
         .Delete
    End With
   
End Sub

¥»©«³Ì«á¥Ñ GBKEE ©ó 2020-4-24 19:40 ½s¿è

¦^´_ 1# frogfrer
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Ar, R As Double, C As Integer, Ttitle As String, S As String
  4.     With New InternetExplorer  '¤Þ¥Î¶µ¥Ø¥[¤J  Microsoft Internet Controls
  5.             .Navigate "https://www.twse.com.tw/exchangeReport/TWTB4U?response=html&date=20200424&selectType=All"
  6.             .Visible = True
  7.             Do While .Busy Or .ReadyState <> 4: DoEvents: Loop
  8.             With .Document.all.TAGS("TABLE")(0)
  9.                 Ttitle = .Rows(0).innerText
  10.                 ReDim Ar(1 To .Rows.Length - 1, .Rows(1).Cells.Length - 1)
  11.                 For R = 1 To .Rows.Length - 1
  12.                     For C = 0 To .Rows(R).Cells.Length - 1
  13.          
  14.                         Ar(R, C) = IIf(C = 0 And Mid(.Rows(R).Cells(C).innerText, 1, 1) = 0, "'", "") & .Rows(R).Cells(C).innerText
  15.                         'IIf(C = 0 And Mid(.Rows(R).Cells(C).innerText, 1, 1) = 0, "'", "")  '**ÃÒ¨é¥N¸¹0¶}ÀY¥[¤W'Âର¤å¦r
  16.                     Next
  17.                 Next
  18.             End With
  19.             .Quit
  20.     End With
  21.     With ActiveSheet   '¥i§ó§ï¤u§@ªí
  22.         .UsedRange.Clear
  23.         .[a1] = Ttitle
  24.         .[a2].Resize(UBound(Ar), 3) = Ar
  25.     End With
  26. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¨S¦³©Ò¦³Åv¡A¥u¦³¥Í©Rªº¨Ï¥ÎÅv¡C
ªð¦^¦Cªí ¤W¤@¥DÃD