GBKEE

¦^´_ 5# caesar0125
§Ú¥ÎIE8 §ì¨ú¸ê®Æ¤]¦³§xÃø
§ï¥Î      CreateObject("msxml2.xmlhttp") + CreateObject("htmlfile")
¸Õ¬Ý¬Ý

1. Sub goodinfo_¦X¨Ö°]³ø()
   
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 Variant
   
4.     Dim oXmlhttp As Object, oHtmldoc As Object, surl, op
   
5.     'Sheets("Á`ªí").Select
   
6.     rc = Sheets("Á`ªí").Cells(Rows.Count, 1).End(xlUp).Row
   
7.     Sheets("Àç¹BÁZ®Ä").UsedRange.Clear
   
8.     For i = 5 To rc
   
9.         sn = Sheets("Á`ªí").Cells(i, 1)
   
10.         url = "http://goodinfo.tw/StockInfo/StockBzPerformance.asp?STOCK_ID=" & sn & " &YEAR_PERIOD=10&RPT_CAT=M_YEAR"
    
11.          Set oXmlhttp = CreateObject("msxml2.xmlhttp")
    
12.          Set oHtmldoc = CreateObject("htmlfile")
    
13.          With oXmlhttp
    
14.             .Open "Get", url, False
    
15.             .Send
    
16.             oHtmldoc.write .responseText
    
17.         End With
    
18.         Set xTable = oHtmldoc.all.tags("TABLE")
    
19.         With Sheets("Àç¹BÁZ®Ä")
    
20.           '  .Cells.Clear
    
21.             For Each E In Array(11, 13, 19) '11,13,19  "TABLE"
    
22.                 k = k + 1
    
23.                 For R = 0 To xTable(E).Rows.Length - 1
    
24.                     For C = 0 To xTable(E).Rows(R).Cells.Length - 1
    
25.                         .Cells(k, C + 1) = xTable(E).Rows(R).Cells(C).innertext
    
26.                     Next
    
27.                     k = k + 1
    
28.                 Next
    
29.             Next
    
30.           End With
    
31.     Next
    
32. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 8# c_c_lai
¥i¥[¤W¨¾¿ù

1. Do
   
2.             Set xTable = oHtmldoc.all.tags("TABLE")
   
3.             Debug.Print xTable.Length, sn
   
4.           ' If xTable.Length >= 19 Then Stop
   
5.         Loop Until xTable.Length >= 19

½Æ»s¥N½X

GBKEE

¦^´_ 10# c_c_lai
XP IE8 ¥Î 6#ªºµ{¦¡½X,¨S¥[¤W¨¾¿ùªºµ{¦¡½X
¨S¦³§A©Ò»¡ªº¿ù»~

1. http://goodinfo.tw/StockInfo/StockBzPerformance.asp?STOCK_ID=1437 &YEAR_PERIOD=10&RPT_CAT=M_YEAR
   
2. 24       1437
   
3. http://goodinfo.tw/StockInfo/StockBzPerformance.asp?STOCK_ID=1438 &YEAR_PERIOD=10&RPT_CAT=M_YEAR
   
4. 24       1438
   
5. http://goodinfo.tw/StockInfo/StockBzPerformance.asp?STOCK_ID=1439 &YEAR_PERIOD=10&RPT_CAT=M_YEAR
   
6. 24       1439
   
7. http://goodinfo.tw/StockInfo/StockBzPerformance.asp?STOCK_ID=1440 &YEAR_PERIOD=10&RPT_CAT=M_YEAR
   
8. 24       1440
   
9. http://goodinfo.tw/StockInfo/StockBzPerformance.asp?STOCK_ID=1441 &YEAR_PERIOD=10&RPT_CAT=M_YEAR
   
10. 24       1441
    
11. http://goodinfo.tw/StockInfo/StockBzPerformance.asp?STOCK_ID=1442 &YEAR_PERIOD=10&RPT_CAT=M_YEAR
    
12. 24       1442
    
13. http://goodinfo.tw/StockInfo/StockBzPerformance.asp?STOCK_ID=1443 &YEAR_PERIOD=10&RPT_CAT=M_YEAR
    
14. 24       1443
    
15. http://goodinfo.tw/StockInfo/StockBzPerformance.asp?STOCK_ID=1444 &YEAR_PERIOD=10&RPT_CAT=M_YEAR
    
16. 24       1444

½Æ»s¥N½X

GBKEE

¦^´_ 13# c_c_lai
³oºô­¶¦³¬y¶qºÞ¨î.

GBKEE

¦^´_ 16# c_c_lai


   

url ¸Ì¬°¦ó§Ú¤@ª½§ì¤£¨ì¥¿½Tªº selectType ¹ïÀ³­È¡H
  ºô­¶ªº«Ø¸m§Ú¨S¤Jªù,©Ò¥H§Ú¤]¦bºN¯Á¤¤

  .getElementsByName:      getElements ½Æ¼Æ-> ª«¥óªº¶°¦X
  .getElementsBytagName:  getElements ½Æ¼Æ-> ª«¥óªº¶°¦X
  .ALL.tags:                     tags ½Æ¼Æ-> ª«¥óªº¶°¦X
  .getElementById :          getElement  ³æ¼Æ ->³æ¤@ªºª«¥ó

1. Set xTable = oHtmldoc.ALL.tags("TABLE")
   
2. Stop
   
3. '¬Ý¬Ý°Ï°ìÅܼƵøµ¡ xTable ªº¤º®e
   
4.     Set xTable = oHtmldoc.ALL.tags("TABLE")(0)
   
5. Stop
   
6. '¦A¦¸¬Ý¬Ý°Ï°ìÅܼƵøµ¡ xTable ªº¤º®e
   
7.     MsgBox xTable.INNERTEXT

½Æ»s¥N½X

ªþÀɤ¤¦³³\¦h¬Û¦Pªºµ{¦¡½X.,,,,,,,,,,,

1.      TVal = Array("MS", "", "0049", "0099P", "019919T", "0999", "0999P", "01", "02", "03", _
   
2.                 "04", "05", "06", "07", "21", "22", "08", "09", "10", _
   
3.                 "11", "12", "13", "24", "25", "26", "27", "28", "29", _
   
4.                 "30", "31", "14", "15", "16", "17", "18", "23", "9299", "19", "20", "CB")
   
5.     '**************************
   
6.    If .ComboBox1.Value = "Select Name" Then
   
7.             MsgBox ("±z©|¥¼¿ï¾Ü¡u²£·~Ãþ§O¡v¡A½Ð©ó" & vbCrLf & "½T»{«á¦A¦¸ÂI¿ï¡y¶}±Òºô­¶¡z¡A" & vbCrLf & "ÁÂÁ±z¡I")
   
8.             Exit Sub
   
9.         End If        
   
10.         For cts = 0 To UBound(lst)
    
11.             If lst(cts) = .[D1] Then select2 = TVal(cts): Exit For
    
12.         Next cts

½Æ»s¥N½X

¥i¥H¾ã¦X¤@¤U

1. Option Explicit
   
2. Const MyUrl = "http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/MI_MARGN.php"
   
3. Private Sub Workbook_Open()
   
4.     Dim oHtmldoc, op, cts
   
5.     Set oHtmldoc = CreateObject("htmlfile")
   
6.     With CreateObject("msxml2.xmlhttp")
   
7.         .Open "Get", MyUrl, False
   
8.         .Send
   
9.         Do While .Status <> 200 Or .readyState <> 4: DoEvents: Loop
   
10.         oHtmldoc.write .responseText
    
11.     End With
    
12.     Set op = oHtmldoc.all.tags("select")("selectType")
    
13.     With Sheets("Á`ªí").ComboBox1
    
14.         .Clear
    
15.         For cts = 0 To op.Length - 1
    
16.                 .AddItem                                             '²M³æ¤è¶ô©Î¤U©Ô¦¡²M³æ¤è¶ô¤¤¥[¤J¤@­Ó¶µ¥Ø
    
17.                 .List(.ListCount - 1, 0) = op(cts).Text   ' ²Ä1Äæ,³Ì¦h9Äæ
    
18.                 .List(.ListCount - 1, 1) = op(cts).Value ' ²Ä2Äæ,³Ì¦h9Äæ
    
19.         Next
    
20.         .Value = "Select Name"  '.List(0)  '0: «H¥Î¥æ©ö²Î­p, 1:¥þ³¡,2: «Ê³¬¦¡°òª÷......
    
21.     End With
    
22. End Sub

½Æ»s¥N½X

1. Private Function Select_Name() As Integer
   
2.     With Sheets("Á`ªí").ComboBox1
   
3.         If .ListIndex = -1 Then MsgBox ("±z©|¥¼¿ï¾Ü¡u²£·~Ãþ§O¡v¡A½Ð©ó" & vbCrLf & "½T»{«á¦A¦¸ÂI¿ï¡y¶}±Òºô­¶¡z¡A" & vbCrLf & "ÁÂÁ±z¡I")
   
4.         Select_Name = .ListIndex
   
5.     End With
   
6. End Function

½Æ»s¥N½X

1. Sub ¿Ä¸ê¿Ä¨é2()
   
2.     Dim qdate As String, select2 As String
   
3.     'Dim TVal(), cts As Integer
   
4.     If Select_Name = -1 Then
   
5.         Exit Sub
   
6.      Else
   
7.         With Sheets("Á`ªí")
   
8.             qdate = Format(.[B1], "EE/MM/DD")
   
9.             With .ComboBox1
   
10.             select2 = .List(.ListIndex, 1)
    
11.             End With
    
12.         End With
    
13.      End If   
    
14.     With Sheets("¿Ä¸ê_¿Ä¨é")
    
15.         .Select
    
16.         .Cells.Clear
    
17.     End With

½Æ»s¥N½X

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