GBKEE

¦^´_ 1# jasonwu0114
¦Û»s¬Ý½L

¤U¸ü (87.02 KB)

2014-1-15 19:21

ThisWorkbook µ{¦¡½X

1. Option Explicit
   
2. Dim ie()  As Object
   
3. Const Sh = "¬Ý½L"  '«ü©w¤u§@ªí¦WºÙ
   
4. Private Sub Workbook_Open()
   
5.     Dim i As Integer, T As Date
   
6.     Workbook_BeforeClose False
   
7.     With Sheets(Sh)
   
8.         Application.StatusBar = "ºô­¶¤U¸ü¤¤..."
   
9.         For i = 2 To .UsedRange.Columns(1).Rows.Count  'ªÑ²¼¥N¸¹
   
10.             ReDim Preserve ie(2 To i)
    
11.             If .Cells(i, "A") <> "" And IsNumeric(.Cells(i, "A")) And Len(.Cells(i, "A")) >= 4 Then
    
12.                 Set ie(i) = CreateObject("InternetExplorer.Application")
    
13.                 ie(i).Navigate "http://newmis.twse.com.tw/stock/fibest.jsp?stock=" & .Cells(i, "A")
    
14.                 DoEvents
    
15.                 Application.StatusBar = "¤U¸ü... " & .Cells(i, "A")
    
16.                 ie(i).Visible = False
    
17.             Else
    
18.                 Set ie(i) = Nothing
    
19.                 .UsedRange.Rows(i).Offset(, 1) = ""
    
20.             End If
    
21.         Next
    
22.     End With
    
23.     T = Time + #12:00:05 AM#
    
24.     If Time < #9:00:00 AM# Then T = #9:00:00 AM#
    
25.     Application.OnTime T, "ThisWorkbook.©Ò¦³ªÑ»ù"
    
26. End Sub
    
27. Private Sub §Y®ÉªÑ»ù(R As Integer)
    
28.     Dim Element As Object, C As Integer
    
29.     Application.EnableEvents = False
    
30.     With Sheets(Sh)
    
31.         If Not ie(R) Is Nothing Then
    
32.             With ie(R)
    
33.                 Do While .Busy Or .ReadyState <> 4: Loop
    
34.                 Set Element = .document.getElementsByTagName("TABLE")(1) '
    
35.             End With
    
36.             For C = 0 To Element.Rows(1).Cells.Length - 1
    
37.                 .Cells(R, C + 2) = Element.Rows(1).Cells(C).innertext
    
38.             Next
    
39.         Else
    
40.             .UsedRange.Rows(R).Offset(, 1) = ""
    
41.         End If
    
42.     End With
    
43.     Application.EnableEvents = True
    
44. End Sub
    
45. 
    
46. Private Sub ©Ò¦³ªÑ»ù()
    
47.     Dim R As Integer
    
48.     If Time >= #1:30:00 PM# Then
    
49.         Workbook_BeforeClose False
    
50.         Application.StatusBar = "  ¤w¦¬½L!!!"
    
51.         Exit Sub
    
52.     End If
    
53.     For R = 2 To UBound(ie)
    
54.         §Y®ÉªÑ»ù R
    
55.     Next
    
56.     Application.OnTime Time + #12:00:02 AM#, "ThisWorkbook.©Ò¦³ªÑ»ù"
    
57.     Application.StatusBar = Time & vbTab & "§ó·s§¹¦¨"
    
58. End Sub
    
59. Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
60.     Dim e As Variant
    
61.     On Error Resume Next
    
62.     For Each e In ie
    
63.        e.Quit
    
64.        Set e = Nothing
    
65.     Next
    
66. End Sub
    
67. Private Sub Workbook_SheetChange(ByVal Wsh As Object, ByVal Target As Range)
    
68.     If Wsh.Name = Sh Then
    
69.         If Target.Column = 1 And Target.Row > 1 Then Workbook_Open
    
70.     End If
    
71. End Sub

½Æ»s¥N½X