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

[µo°Ý] ¤@­Ó¥ÎVBA±qºô­¶¨ú±o·Q­n¸ê®Æªº¼gªk~

¦^´_ 40# bioleon69
  1. Option Explicit
  2. Sub test()
  3. Dim Ie As Object, e As Object, R As Integer, C As Integer
  4.     'Set Ie = CreateObject("InternetExplorer.Application")
  5.     With CreateObject("InternetExplorer.Application")
  6.          '.Visible = True
  7.         .Navigate "http://mops.twse.com.tw/mops/web/t56sb21_q3"
  8.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  9.        '**************************************
  10.         With .Document.forms("form1")
  11.             .typek.Value = "otc"
  12.             .Year.Value = "105"
  13.             .smonth.Value = "03"
  14.             .emonth.Value = "04"
  15.         End With
  16.       ''******************************************
  17.         For Each e In .Document.ALL.TAGS("input")
  18.             If e.Type = "button" And e.Value = " ¬d¸ß " Then e.Click
  19.         Next
  20.         '*****************************************
  21.         Do
  22.             DoEvents
  23.             Set e = .Document.ALL("TABLE01").ALL.TAGS("TABLE")(0)
  24.         Loop Until Not e Is Nothing
  25.         ¸ê®Æ¼g¤J e
  26.             .Quit
  27.     End With
  28. End Sub
  29. Sub Ex()
  30.     With CreateObject("InternetExplorer.Application")
  31.         .Navigate "http://mops.twse.com.tw/mops/web/t56sb21_q3?encodeURIComponent=1&run=Y&step=1&TYPEK=sii&year=105&smonth=01&emonth=02&sstep=1&firstin=true"
  32.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  33.         ¸ê®Æ¼g¤J .Document.ALL("TABLE01").ALL.TAGS("TABLE")(0)
  34.             .Quit
  35.     End With
  36. End Sub
  37. Private Sub ¸ê®Æ¼g¤J(ByVal xTable As Object)
  38.     Dim R As Integer, C As Integer
  39.     With ActiveSheet
  40.             .UsedRange.Clear
  41.             Application.ScreenUpdating = False
  42.             For R = 0 To xTable.Rows.Length - 1
  43.                 For C = 0 To xTable.Rows(R).Cells.Length - 1
  44.                     .Cells(R + 1, C + 1) = xTable.Rows(R).Cells(C).INNERTEXT
  45.                 Next
  46.             Next
  47.              .UsedRange.WrapText = False
  48.              Application.ScreenUpdating = True
  49.     End With
  50. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ bioleon69 ©ó 2017-5-15 17:16 ½s¿è

¦^´_ 41# GBKEE
G¤j,²Ä¤@¬qµ{¦¡ªºFOR EACH¨ºÃä½T¹ê¥i¥H¼ÒÀÀÂIÀ»
¤p§Ì¥ý¦¬¤U¤F!!
---------------------
²Ä¤@¬q(test)ªºÀ³¸Ó¬O±z¦^À³§Ú¦p¦ó¼ÒÀÀÂIÀ»§a
¥H³o­Ó¨Ò¤l¦Ó¨¥,¦ü¥G¨S¿ìªk±q¥D·j´Mºô§}
http://mops.twse.com.tw/mops/web/t56sb21_q3
¼ÒÀÀÂIÀ»«á,ª½±µ¦b§ì¤U­±ªºtable¸ê®Æ
¦Ó¥²¶·±q
http://mops.twse.com.tw/mops/web/t56sb21_q3?encodeURIComponent=1&run=Y&step=1&TYPEK=sii&year=105&smonth=01&emonth=02&sstep=1&firstin=true
¥h§ì¸ê®Æ
¤§«áªº²Ä¤G¬q(ex)¸ò²Ä¤T¬q(¸ê®Æ¼g¤J)¤~¬O¤@­Óºô§ì¹ï§a?(¤p§Ì²z¸Ñ)

---------------------

Private Sub ¸ê®Æ¼g¤J(ByVal xTable As Object)
³o­Óbyval¤£¬O«ÜÀ´,¬°¦ó¤£ª½±µdim¨ìsub¸Ì­±?

¥H¤U¬O¤p§Ì¦Û¤v³Ì¤j²z¸Ñ¯à¤Oªº¼gªk,¤]¬O¨S¿ìªk¦¨¥\¼g¤J
ÁÙ½ÐG¤jÀ°¦£«ü¥¿¤@¤U¿ù»~,ÁÂÁ±z^^"
  1. Sub test()
  2. '*****************************************
  3. Dim Ie
  4.     Set Ie = CreateObject("InternetExplorer.Application")
  5.     With Ie
  6.        'Visible = True
  7.         .Navigate "http://mops.twse.com.tw/mops/web/t56sb21_q3?encodeURIComponent=1&run=Y&step=1&TYPEK=sii&year=105&smonth=01&emonth=02&sstep=1&firstin=true"
  8.         Do Until .readyState = 4
  9.             DoEvents
  10.         Loop
  11. '*****************************************
  12.         Set cc = .Document.body
  13.         Set tb = cc.all.tags("table")(0).Rows '©w¸qªí®æ¬°°}¦C
  14.         'Debug.Print tb.innertext              '°£¿ù¥Î
  15. '*****************************************
  16.          With ActiveSheet
  17.         .UsedRange.Clear
  18.         For i = 0 To tb.Length - 1
  19.         For j = 0 To tb(i).Cells.Length - 1
  20.         .Cells(i + 1, j + 1) = tb(i).Cells(j).innertext '³v¤@¼g¤J
  21.         Next
  22.         Next
  23.         End With
  24.     End With
  25.     Ie.Quit
  26.     Set Ie = Nothing
  27. End Sub
½Æ»s¥N½X
VBA ±q0¶}©l
¥ý±q¾Ç·|¬ÝªºÀ´¶}©l
¥ý±q·|¦³°ò¥»­×§ï¯à¤O¶}©l
¤@¨B¤@¨B¾Ç²ß¤¤

TOP

¦^´_ 42# bioleon69
  1. Set tb = cc.all.tags("table")(12).Rows
½Æ»s¥N½X
¹ï©ó byval¤£¬O«ÜÀ´!¥i°Ñ¦ÒVBAªº»¡©ú.
Sub ³¯­z¦¡
¦³®Ä²v¦a¶Ç»¼¤Þ¼Æ
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 43# GBKEE

ok¤F!ÁÂÁÂg¤j ·R±z~^^


byval¥Ø«e´N¥ý¤£¦Ò¼{¤F= =||
VBA ±q0¶}©l
¥ý±q¾Ç·|¬ÝªºÀ´¶}©l
¥ý±q·|¦³°ò¥»­×§ï¯à¤O¶}©l
¤@¨B¤@¨B¾Ç²ß¤¤

TOP

½Ð±Ð¤@¤U!    §Ú·Q¤U¸ühttp://isin.twse.com.tw/isin/C_public.jsp?strMode=2    ¤W­±ªº¸ê®Æ¡A ¨Ï¥Îwhite5168¤jªº VBA½X¡A ¥X²{"Class WorksheetSpecial¤èªk¥¢±Ñ"¡C½Ð°Ý¬O­þ¸Ì¦³°ÝÃD?


Sub Test()
    Const url As String = "http://isin.twse.com.tw/isin/C_public.jsp?strMode=2"
    Cells.Clear
    Set ie = CreateObject("internetexplorer.application") '¨Ï¥Î¦¹¤è¦¡¥i¥H§K°£ "³]©w¤Þ¥Î¶µ¥Ø"
    With ie
        .Visible = False 'True¬°¶}±Òie, False¬°¤£¶}±Òie
        .Navigate url
        Do While .ReadyState <> 4 'µ¥«Ýºô­¶¶}±Ò
            DoEvents
        Loop
        .ExecWB 17, 2 'Select All
        .ExecWB 12, 2 'Copy selection
        Sheets("¤u§@ªí1").Cells.Select
        Range("A1").Activate
        ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
                False, NoHTMLFormatting:=True
    End With
    Columns("A:B").Delete
    ie.Quit
    MsgBox "¸ê®Æ½Æ»sµ²§ô"
End Sub

¿ù»~°T®§

ªÑ²¼¥N½X¤U¸ü.png
2017-6-26 16:20

TOP

¸Ñ¨M¤F!

°Q½×°Ï¤º§ä¨ì¤@½g ¬ÛÃöªº¶K¤å
[¤W¥«­ÓªÑ¤é¦¨¥æ¸ê°T¤U¸ü§ïª©«Øij]
http://forum.twbts.com/viewthrea ... amp;from=indexheats

¥Ñ¸Ó½g joey0415¤j ´£¨Ñªº VBA½X ¤p§ï¤@¤U´NOK¤F!


Sub ªÑ²¼¥N½X§ó·s()

    Cells.Clear
    surl = "http://isin.twse.com.tw/isin/C_public.jsp?strMode=2"
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & surl, Destination:=Range("$A$1"))
        .Refresh BackgroundQuery:=False
    End With
   
   
End Sub

TOP

        ÀR«ä¦Û¦b : µÊ®ð¼L¤Ú¤£¦n¡A¤ß¦a¦A¦n¤]¤£¯àºâ¬O¦n¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD