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

vesperlee

³Â·Ð¦U¦ì¤j¤jÀ°¦£¡AÁÂÁ¡A·P®¦~~~~¡C

¤U¸ü (91.31 KB)
»¡©ú

2012-4-28 15:00

½Ð°Ý~~¡C§Ú·Q¥ÎVBA»s§@¤@­ÓBUTTON¡A·í§Ú«ö¤U®É¯à±q¤U­±ºô§}®»¨ì§Ú«ü©wªº¸ê®Æ¡A¨Ã©ñ¤JexcelªºÀx¦s®æ¤º¡C¸Ó«ç»ò¼g©O¡I
§Ú¥u­n®» ¥H¤U¤ù¬qªº¸ê®Æ
               
                    ±i¼Æ             ¦ûªÑ¥»¤ñ¨Ò
¸³ºÊ«ùªÑ          21,712         6.04%
¶°«O®w¦s          364,848      101.44%
¤»¤é§¡¶q          1,834            0.51%  

¬ÛÃöºô§}
http://jsjustweb.jihsun.com.tw/z/zc/zcx/zcx_6257.asp.htm
http://jsjustweb.jihsun.com.tw///z/zc/zcx/zcxD1.djjs?A=6257"

ABK

¸Ñ¨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

ABK

½Ð±Ð¤@¤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

¤U¸ü (75.46 KB)
¿ù»~°T®§

2017-6-26 16:20

bioleon69

¦^´_ 43# GBKEE

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


byval¥Ø«e´N¥ý¤£¦Ò¼{¤F= =||

GBKEE

¦^´_ 42# bioleon69

1. Set tb = cc.all.tags("table")(12).Rows

½Æ»s¥N½X

¹ï©ó byval¤£¬O«ÜÀ´!¥i°Ñ¦ÒVBAªº»¡©ú.
Sub ³¯­z¦¡
¦³®Ä²v¦a¶Ç»¼¤Þ¼Æ

bioleon69

¦^´_ 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

GBKEE

¦^´_ 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

bioleon69

¦^´_ 39# GBKEE


­n¥Îieªº¤èªk¶Ü??
·Ð½Ð¤j¤jÀ°¦£¬Ý¤@¤Uµ{¦¡½X­þÃ䦳°ÝÃD,§Ú¼gªº¨º¨â¦æ³£¤£¯à«ö¤U¥h

1. Sub test()
   
2. Dim ie, ab, cc
   
3.     Set ie = CreateObject("InternetExplorer.Application")
   
4.     With ie
   
5.          .Visible = True
   
6.         .navigate "http://mops.twse.com.tw/mops/web/t56sb21_q3"
   
7.         Do Until .ReadyState = 4
   
8.             DoEvents
   
9.         Loop
   
10.         Set cc = .document
    
11.         Set ab = .document.forms("form1")
    
12.         ab.typek.Value = "otc"
    
13.         ab.Year.Value = "105"
    
14.         ab.smonth.Value = "03"
    
15.         ab.emonth.Value = "04"
    
16.         'cc.getelementbyid("search_bar1").Click
    
17.        ' ab.submit
    
18.         End With
    
19. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 38# bioleon69

³o¸Ì¬Ý¬Ý    µLªk¥Î¥~³¡¸ê®Æ¶×¤Jªººô¯¸¯à¥ÎVBAŪ¨ú¸ê®Æ¶Ü¡H

bioleon69

¦^´_ 37# bioleon69


GGGG¤j..ÁÙ¦³¤@­Ó°ÝÃD..
(©çÁÂ,ÅýG¤j³Ìªñ«Ü¦£ «¢«¢!)

§Ú¤@­Ó¤@­Ó´ú¸Õ,À³¸Ó¬O²Ä12­Óªí®æ¨S¦³¿ù
¬°¤°»ò·|¤U¸ü¤£¤F©O??©_©Ç

1. Sub TEST()
   
2.     With ActiveSheet.QueryTables.Add(Connection:="URL;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", Destination:=Range("A1"))
   
3.         .Name = "¤W¥««ùªÑÂàÅý"
   
4.         .WebFormatting = xlWebFormattingNone
   
5.         .WebTables = "12"
   
6.         .RefreshStyle = xlOverwriteCells
   
7.         .WebPreFormattedTextToColumns = True
   
8.         .WebConsecutiveDelimitersAsOne = True
   
9.         .WebDisableDateRecognition = False
   
10.         .Refresh BackgroundQuery:=False
    
11.     End With
    
12. End Sub

½Æ»s¥N½X