ªÑ²¼¸ê®Æ¶×¤J¡Aµ{¦¡°õ¦æ¦³°ÝÃD ºô­¶¤¸¯À

caesar0125

¼Ò¥éª©¤j½s§ï¤@­Óµ{¦¡

¦ý¶×¤J¦³®É·|°±¤î¤¤Â_¡A°±¤îªº¦ì¸m¤£¤@©w

§ÚÃhºÃ¬O°O¾ÐÅ餣¨¬¡A¸Ó¦p¦ó§ï©O¡H

1. Sub ¾ú¥vªÑ»ù§ó·s()
   
2.     Dim xTable As Object, k As Integer, c As Integer, r As Integer, rc As Integer, sn As Integer
   
3.     Dim url As String, i As Integer, E As Object
   
4.     With Sheets("Àç¹BÁZ®Ä")
   
5.           .UsedRange.Clear
   
6.     End With
   
7.     Sheets("Á`ªí").Select
   
8.     rc = Cells(Rows.Count, 1).End(xlUp).Row
   
9.     For i = 5 To rc
   
10.     sn = Cells(i, 1)
    
11. 
    
12.     url = "http://goodinfo.tw/StockInfo/StockBzPerformance.asp?STOCK_ID=" & sn & " &YEAR_PERIOD=10&RPT_CAT=M_YEAR"
    
13.          With CreateObject("InternetExplorer.application")
    
14.         .Visible = True
    
15.         .Navigate url
    
16. 
    
17.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
    
18.         Set xTable = .Document.getElementsByTagName("TABLE")(11) '¸ê®Æ¦b³o
    
19.         With Sheets("Àç¹BÁZ®Ä")
    
20. 
    
21.             k = k + 1
    
22.             For r = 0 To xTable.Rows.Length - 1
    
23.                 For c = 0 To xTable.Rows(r).Cells.Length - 1
    
24.                     .Cells(k, c + 1) = xTable.Rows(r).Cells(c).innertext
    
25.                 Next
    
26.                 k = k + 1
    
27.             Next
    
28.         End With
    
29.         Set xTable = .Document.getElementsByTagName("TABLE")(13) '¸ê®Æ¦b³o
    
30.         With Sheets("Àç¹BÁZ®Ä")
    
31. 
    
32.             k = k + 1
    
33.             For r = 0 To xTable.Rows.Length - 1
    
34.                 For c = 0 To xTable.Rows(r).Cells.Length - 1
    
35.                     .Cells(k, c + 1) = xTable.Rows(r).Cells(c).innertext
    
36.                 Next
    
37.                 k = k + 1
    
38.             Next
    
39.         End With
    
40.         Set xTable = .Document.getElementsByTagName("TABLE")(19) '¸ê®Æ¦b³o
    
41.         With Sheets("Àç¹BÁZ®Ä")
    
42. 
    
43.             k = k + 1
    
44. 
    
45.              For r = 0 To 3
    
46.                 For c = 0 To xTable.Rows(r).Cells.Length - 1
    
47.                     .Cells(k, c + 1) = xTable.Rows(r).Cells(c).innertext
    
48.                 Next
    
49.                 k = k + 1
    
50.             Next
    
51.         End With
    
52.         .Quit
    
53.     End With
    
54.     Next
    
55. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 25# GBKEE
ÁÂÁ±z¡I
#15 ~ #20 ¶¡ªº´£°Ý¡A³Ì«á§Ú¬O¥H

1. .Open "Post", url, False

½Æ»s¥N½X

¨ú¥N¤F

1. .Open "Get", url, False

½Æ»s¥N½X

¤~±o¥H¸Ñ¨M "&select2=" & TVal(Select_Name)
ªº°Ñ¼Æ¤Þ¤J°ÝÃD¡C
¥i¬O«o¥X²{¼g¤J¤§¤å¦r³¡¤À¬O¤@°ï¶Ã½X¡A
©Ò¥H¤~·|¦A¥á¥X ¥Ñºô­¶¨ú±oªº¸ê®Æ¡A¤£ª¾¹D­n¦p¦ó¦P¨B½s½X¦¨¥¿½T¤§¤¤¤å½X¦^¶Ç
¬ÛÃöªº´£°Ý¡C¤Wºô¬d¬Ý¤F Google/¶Ã½X ªº°Q½×
¦n¦ü¤£¤Ó·f¹Ç¡AÃø¥H¸ÑÃD¡C
ÁÂÁ±z¶O¤ß¦a¦^´_¡C
¤W­z "¦p¦ó¦P¨B½s½X" §Ú¥ç¦³¥[¤J¸É¥R¡A¨Ãªþ¤WÀÉ®×
¦A¦¸ÁÂÁ±z¤F¡I³Â·Ð±z¡I

GBKEE

¦^´_ 15# c_c_lai
À³¹B  ºô­¶¦³¬y¶qºÞ¨î.
1 ÀÉ®×Ãö³¬
2­«¶}ÀÉ®×,   ­«±Òadslªº³s½u,°õ¦æµ{¦¡

¥t³]¤@­Ó­n¶}¶}ÃöÃö goodinfo_¦X¨Ö°]³øÀÉ®× ªºÀÉ®×
ThisWorkbook ¼Ò²Õªºµ{¦¡½X

1. Option Explicit
   
2. Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
3.     Application.Quit
   
4. End Sub
   
5. Private Sub Workbook_Open()
   
6.     Dim App As New Application, xFile As String
   
7.     xFile = "D:\goodinfo_¦X¨Ö°]³ø.xls"  ' goodinfo_¦X¨Ö°]³øµ{¦¡ªºÀÉ®×
   
8.     Application.Visible = False
   
9. Re_App:
   
10.     With App
    
11.         .Visible = True
    
12.         .Workbooks.Open (xFile)
    
13.     End With
    
14.     If App.Visible = False Then
    
15.     Set App = Nothing
    
16.     GoTo Re_App   
    
17.     End If
    
18.     ThisWorkbook.Close
    
19. End Sub

½Æ»s¥N½X

goodinfo_¦X¨Ö°]³øÀÉ®× ThisWorkbook ¼Ò²Õªºµ{¦¡½X

1. Option Explicit
   
2. Private Sub Workbook_Open()
   
3.     adsl³s½u   '°Ñ¦Ò   http://forum.twbts.com/thread-17716-1-1.html ªº adsl³s½uµ{¦¡½X
   
4.     goodinfo_¦X¨Ö°]³ø
   
5. End Sub
   
6. Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
7.     Application.Quit
   
8. End Sub
   
9. Sub goodinfo_¦X¨Ö°]³ø()   
   
10.     Dim xTable As Object, k As Integer, C As Integer, sn As Double, r As Integer
    
11.     Dim url As String, i As Double, E As Variant
    
12.     Dim oXmlhttp As Object, oHtmldoc As Object, surl, op, tm
    
13.    For Each E In ThisWorkbook.Names
    
14.         If E.Name = "³øªí¶}©l¦C" Then GoTo xStart
    
15.    Next
    
16.     ThisWorkbook.Names.Add "³øªí¶}©l¦C", 1
    
17.     ThisWorkbook.Names.Add "³øªí¶}®É¶¡", Time
    
18.     ThisWorkbook.Activate
    
19. xStart:
    
20.     Sheets("Àç¹BÁZ®Ä").Activate
    
21.         With Sheets("Àç¹BÁZ®Ä2")
    
22.             .UsedRange.Clear
    
23.         End With
    
24.     For i = [³øªí¶}©l¦C] To Sheets("Á`ªí").Cells(Rows.Count, 2).End(xlUp).Row '
    
25. APP_EX:
    
26.         sn = Sheets("Á`ªí").Cells(i, 2)
    
27.         url = "http://goodinfo.tw/StockInfo/StockBzPerformance.asp?STOCK_ID=" & sn & "&YEAR_PERIOD=10&RPT_CAT=M_YEAR"
    
28.         Set oXmlhttp = CreateObject("msxml2.xmlhttp")
    
29.         Set oHtmldoc = CreateObject("htmlfile")
    
30.         With oXmlhttp
    
31.             .Open "Get", url, False
    
32.             .Send
    
33.             oHtmldoc.write .responseText
    
34.         End With
    
35.         Set xTable = oHtmldoc.all.tags("TABLE")
    
36.            '*********¥iÆ[¬Ý ¬y¶q³QºÞ¨î ************************************
    
37.            Sheets("Àç¹BÁZ®Ä").Cells(i, 1).Resize(, 3) = Array(xTable.Length, sn, Time)
    
38.            Sheets("Àç¹BÁZ®Ä").Cells(i, 1).Select
    
39.            '************************************************************
    
40.               If xTable.Length = 0 Then
    
41.                  ThisWorkbook.Names.Add "³øªí¶}©l¦C", i
    
42.                  ThisWorkbook.Close True
    
43.               End If
    
44.             With Sheets("Àç¹BÁZ®Ä2")
    
45.             For Each E In Array(11, 13, 19) '11,13,19  "TABLE"
    
46.                 k = k + 1
    
47.                 For r = 0 To xTable(E).Rows.Length - 1
    
48.                 For C = 0 To xTable(E).Rows(r).Cells.Length - 1
    
49.                     .Cells(k, C + 1) = xTable(E).Rows(r).Cells(C).innertext
    
50.                 Next
    
51.                 k = k + 1
    
52.                 Next
    
53.             Next
    
54.         End With
    
55.     Next
    
56.     ThisWorkbook.Names("³øªí¶}©l¦C").Delete
    
57.     MsgBox "¶O®É " & Application.Text(Time - [³øªí¶}®É¶¡], "M¤ÀS¬í") & " Finished Complete!"
    
58. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 23# stillfish00
«D±`ÁÂÁ§A¡I
¤§«e¸I¨ìªº½m²ß³£¬O±µÄ²¨ì Get¡A
©Ò¥H¤]¨S¯d·N¦p¦ó¥hÀ³¥Î Post ªº³B­ù¡A
·PÁ§Aªº«ü¾É¡C   ¥Ø«e
TVal = Array("MS", "ALL", "0049", "0099P", "019919T", "0999", "0999P", "01", "02", "03", _
                "04", "05", "06", "07", "21", "22", "08", "09", "10", _
                "11", "12", "13", "24", "25", "26", "27", "28", "29", _
                "30", "31", "14", "15", "16", "17", "18", "23", "9299", "19", "20", "CB")
¥Ø«e´N¥u³Ñ¤U "ALL" (¥þ³¡) ªº°ÝÃD¤´§ì¤£¨ì¡A¨ä¾l³£ OK¡A©Î³\¥N½X¤£¹ï§í©Î¬O . . . . .

¤U¸ü (34.48 KB)

2016-6-14 11:10

§Ú¦AÄ~Äò¥[ªo§a¡C
¦A¦¸ÁÂÁ§AÅo¡I

stillfish00

¦^´_ 20# c_c_lai
¥i¥Hµy·L°Ñ¦Ò³o©« : http://forum.twbts.com/viewthread.php?tid=15544

1. Sub Test()
   
2.     Dim xTable As Object, k As Integer, C As Integer, R As Integer        '  , sn As Integer
   
3.     Dim url As String, cts As Integer, E As Variant, xDate As String      '  , rc As Integer
   
4.     Dim oXmlhttp As Object, oHtmldoc As Object, select2 As String         '  , tm
   
5.     Dim TVal() As Variant
   
6. 
   
7.     If Select_Name = -1 Then Exit Sub
   
8.     TVal = Array("MS", "ALL", "0049", "0099P", "019919T", "0999", "0999P", "01", "02", "03", _
   
9.                 "04", "05", "06", "07", "21", "22", "08", "09", "10", _
   
10.                 "11", "12", "13", "24", "25", "26", "27", "28", "29", _
    
11.                 "30", "31", "14", "15", "16", "17", "18", "23", "9299", "19", "20", "CB")
    
12. 
    
13.     url = "http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/MI_MARGN.php"
    
14.     xDate = Format(Sheets("Á`ªí").[B1], "EE/MM/DD")
    
15.     sPost = "qdate=" & Replace(xDate, "/", "%2F") & "&selectType=" & TVal(Select_Name)  'urlencode
    
16.     Set oXmlhttp = CreateObject("msxml2.xmlhttp")
    
17.     Set oHtmldoc = CreateObject("htmlfile")
    
18.     With oXmlhttp
    
19.         .Open "Post", url, False
    
20.         '.setRequestHeader "Connection", "Keep-Alive"   'µu®É¶¡¤º¦h¦¸¬d¸ß«Øij¥i¥[³o¦æ
    
21.         .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    
22.         .setRequestHeader "Content-Length", Len(sPost)
    
23.         .Send sPost
    
24.         '¤W­± Open °Ñ¼Æ¥ÎFalse(=¦P¨B)¡A¥i¥H¤£¥Î¦A§PÂ_status
    
25.         'Do While .Status <> 200 Or .readyState <> 4: DoEvents: Loop
    
26.         oHtmldoc.write .responseText
    
27.         'MsgBox .responseText
    
28.     End With
    
29. 
    
30.     Set xTable = oHtmldoc.ALL.tags("TABLE")
    
31.     ' Stop
    
32.     '  ¬Ý¬Ý°Ï°ìÅܼƵøµ¡ xTable ªº¤º®e
    
33.     Set xTable = oHtmldoc.ALL.tags("TABLE")(0)
    
34.     ' Stop
    
35.     '  ¦A¦¸¬Ý¬Ý°Ï°ìÅܼƵøµ¡ xTable ªº¤º®e
    
36.     MsgBox xTable.INNERTEXT
    
37. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 21# stillfish00
½Ð±ÐÀ³¦p¦ó¸Ñ¨M©O¡H
§x³n§Ú¦h¤é¤F¡C
ÁÂÁ©p¡I

stillfish00

¦^´_ 20# c_c_lai
¬Ý¤W¹Ï106¦æ form method="post"
»¡©ú·í§AÂI¬d¸ß®É¬O¥Hpost¶Ç°e½Ð¨D¡A©Ò¥H§A¤£¯à¥u¥Îget¦bºô§}¥[°Ñ¼Æ¡C

c_c_lai

¦^´_ 17# GBKEE

¤U¸ü (128.95 KB)

2016-6-13 11:02

c_c_lai

¦^´_ 17# GBKEE
´ú¸Õ¥Î (¤w®M¥Î±z«Øij¤§¨ç¼Æ)

1. Sub Test()
   
2.     Dim xTable As Object, k As Integer, C As Integer, R As Integer        '  , sn As Integer
   
3.     Dim url As String, cts As Integer, E As Variant, xDate As String      '  , rc As Integer
   
4.     Dim oXmlhttp As Object, oHtmldoc As Object, select2 As String         '  , tm
   
5.     Dim TVal() As Variant
   
6.    
   
7.     If Select_Name = -1 Then Exit Sub
   
8.     TVal = Array("MS", "ALL", "0049", "0099P", "019919T", "0999", "0999P", "01", "02", "03", _
   
9.                 "04", "05", "06", "07", "21", "22", "08", "09", "10", _
   
10.                 "11", "12", "13", "24", "25", "26", "27", "28", "29", _
    
11.                 "30", "31", "14", "15", "16", "17", "18", "23", "9299", "19", "20", "CB")
    
12.    
    
13.     xDate = Format(Sheets("Á`ªí").[B1], "EE/MM/DD")
    
14.     url = "http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/MI_MARGN.php?qdate=" & xDate & "&selectType=" & TVal(Select_Name)
    
15.     'url = "http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/MI_MARGN.php?qdate=" & xDate & "&selectType=" & Select_Name
    
16.     'url = "http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/MI_MARGN.php?qdate=" & xDate & "&selectType=¤ôªd¤u·~"
    
17.     Set oXmlhttp = CreateObject("msxml2.xmlhttp")
    
18.     Set oHtmldoc = CreateObject("htmlfile")
    
19.     With oXmlhttp
    
20.         .Open "Get", url, False
    
21.         .Send
    
22.         
    
23.         Do While .Status <> 200 Or .readyState <> 4: DoEvents: Loop
    
24.         
    
25.         oHtmldoc.write .responseText
    
26.         'MsgBox .responseText
    
27.     End With
    
28.    
    
29.     Set xTable = oHtmldoc.ALL.tags("TABLE")
    
30.     ' Stop
    
31.     '  ¬Ý¬Ý°Ï°ìÅܼƵøµ¡ xTable ªº¤º®e
    
32.     Set xTable = oHtmldoc.ALL.tags("TABLE")(0)
    
33.     ' Stop
    
34.     '  ¦A¦¸¬Ý¬Ý°Ï°ìÅܼƵøµ¡ xTable ªº¤º®e
    
35.     MsgBox xTable.INNERTEXT
    
36. End Sub
    
37. 
    
38. Private Function Select_Name() As Integer
    
39.     With Sheets("Á`ªí").ComboBox1
    
40.         If .ListIndex = -1 Then MsgBox ("±z©|¥¼¿ï¾Ü¡u²£·~Ãþ§O¡v¡A½Ð©ó" & vbCrLf & "½T»{«á¦A¦¸ÂI¿ï¡y¶}±Òºô­¶¡z¡A" & vbCrLf & "ÁÂÁ±z¡I")
    
41.         Select_Name = .ListIndex    '  Select_Name = -1,0,1,2,3,4,5,6,7,8,9,.....39
    
42.     End With
    
43. End Function

½Æ»s¥N½X

¤U¸ü (27.38 KB)

2016-6-13 11:01

c_c_lai

¦^´_ 17# GBKEE
¦n«Øij¦¬¨ì¤F¡A©â®É¶¡¦A¨Ó¸Õ¸Õ¾ã¦X«á¤§®ÄªG¡A
­ì¥»¸Ì­±ªº¥DÃD¼Ò²Õ¬O¦U¦Û¿W¥ßªºÀ³¥Î´ú¸Õ¡A
¬ðµo©_·Q¡A¦pªG¦P¤@¥DÃD§¡¤À§O¨Ï¥Î¤£¦P³B²z¤è¦¡¡A
¦p CreateObject("InternetExplorer.Application")¡B
.QueryTables.Add()¡B¥H¤Î CreateObject("msxml2.xmlhttp")
»P CreateObject("htmlfile") ·f°tÀ³¥Îµ¥¡A¨ÃÆ[¹î¥¦­Ì¦P®É¥~³¡
±a¤J°Ñ¼Æ¡B¦p url = "http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/MI_MARGN.php?qdate=" & xDate & "&selectType=" & select2
©Î¤º³¡³B²z¤è¦¡¡A¦p .Document.ALL("input_date").Value = xDate
.Document.ALL("select2").SelectedIndex = i  ¥H¤Î¤º³¡Ä²µo¤§
.Document.ALL("login_btn").Click µ¥ªº¤£¦PÀ³¥Î¡A±q¨ä¦U§Oªº¨¤«×
¨Óµû¦ô°õ¦æ¦¨®Äªº¡C¨ÃÂǦ¹¥ç¥i±Òµo¤j®aªºª¾ªºÅv¯q¤Î¦h¤è¤§À³¥Î¡C
ÁÂÁ±zÅo¡I