joey0415

¦^´_ 19# GBKEE

½Ð°Ý¦b²Ä¤@¬qªº

If IE Is Nothing Then

»¡¦¹³B»Ý­nª«¥ó¡A½Ð°Ý¶Wª©¦p¦ó­×¥¿©O¡H

HSIEN6001

¦^´_ 21# joey0415

¾ã¬q»yªk¬O³o¼Ëªº , ºî¦X #2 & #9
ThisWorkbook¼Ò²Õªºµ{¦¡½X

1. Option Explicit
   
2. Private Sub Workbook_Open()
   
3.     Sheet1.Msg = True
   
4.     Run "Sheet1.¹Ï§Î§ó·s"
   
5. End Sub
   
6. Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
7.     On Error Resume Next
   
8.     If Not Sheet1.IE Is Nothing Then Sheet1.IE.Quit
   
9. End Sub

½Æ»s¥N½X

Sheet1¼Ò²Õªºµ{¦¡½X

1. Option Explicit
   
2. Public IE As Object, Msg As Boolean, IEx As Object
   
3. Const ¹Ï§Î = "C:\ÅçÃÒ¹Ï.jpg"
   
4. Const ÃÒ¨é¥N¸¹ = "F2"
   
5. Const ÅçÃÒ½X = "F4"
   
6. Private Sub Worksheet_Change(ByVal Target As Range)
   
7.     Range(ÃÒ¨é¥N¸¹).Interior.ColorIndex = IIf(Range(ÃÒ¨é¥N¸¹).Value = "", 2, 36)
   
8.     With Target.Cells(1)
   
9.          If .Address(0, 0) = ÅçÃÒ½X Then .Interior.ColorIndex = IIf(Len(Trim(.Cells)) = 5, 36, 2)
   
10.          If .Address(0, 0) = ÅçÃÒ½X And Len(Trim(.Cells)) = 5 And Range(ÃÒ¨é¥N¸¹).Value <> "" Then
    
11.             If IE Is Nothing Then
    
12.                 Target = ""
    
13.                 Msg = True
    
14.                 ¹Ï§Î§ó·s
    
15.                 Exit Sub
    
16.             End If
    
17.             Application.EnableEvents = False
    
18.             ¤é³øªí¸ü¤J
    
19.             Target = ""
    
20.             Application.EnableEvents = True
    
21.         End If
    
22.     End With
    
23. End Sub
    
24. Private Sub ¤é³øªí¸ü¤J()
    
25.     Dim e As Object, A As Object, k  As Integer, i As Integer, S As String
    
26.     If IE Is Nothing Then
    
27.         ¹Ï§Î§ó·s
    
28.         MsgBox "ÅçÃҹϤw§ó·s"
    
29.         Exit Sub
    
30.     End If
    
31.     With IE
    
32.         .Document.all.tags("INPUT")("stk_code").Value = Range(ÃÒ¨é¥N¸¹)
    
33.         .Document.all.tags("INPUT")("auth_num").Value = Trim(Range(ÅçÃÒ½X))
    
34.         Set A = .Document.all.tags("BUTTON")
    
35.         For Each e In A
    
36.             If Trim(e.Innertext) = "¬d¸ß" And e.ID = "" Then
    
37.             e.Click
    
38.             Exit For
    
39.             End If
    
40.         Next
    
41.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
    
42.         UsedRange.Offset(6).Clear
    
43.         Range("a" & k + 1) = S
    
44.         If .Document.body.Innertext Like "***¸ÓªÑ²¼¸Ó¤éµL¥æ©ö¸ê°T***" Then S = "***¸ÓªÑ²¼¸Ó¤éµL¥æ©ö¸ê°T***"
    
45.         If .Document.body.Innertext Like "***ÅçÃÒ½X¿ù»~¡A½Ð­«·s¬d¸ß¡C***" Then S = "***ÅçÃÒ½X¿ù»~¡A½Ð­«·s¬d¸ß¡C*** "
    
46.         If S <> "" Then
    
47.             Range("a" & k + 1) = S
    
48.             MsgBox S
    
49.             GoTo NN
    
50.         End If
    
51.         Set IEx = CreateObject("InternetExplorer.Application")
    
52.         IEx.Navigate "about:Tabs"
    
53.         Set A = .Document.all.tags("A")
    
54.         ³æ­¶¸ü¤J .Document.all.tags("table")(0).outerHTML
    
55.         [A6].Select
    
56.         '********µ{¦¡½X¼g¦b¤u§@ªí¼Ò²Õ: Me «ü³o¤u§@ªí¼Ò²Õ
    
57.         Me.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
    
58.         '****************************************
    
59.         If A.Length = 459 Then
    
60.             For i = 2 To 3
    
61.                 ³æ­¶¸ü¤J .Document.all.tags("table")(i).outerHTML
    
62.                 With Range("A" & Rows.Count).End(xlUp).Offset(1)
    
63.                     .Select
    
64.                     Me.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
    
65.                 End With
    
66.             Next
    
67.         Else
    
68.             For k = 0 To A.Length - 1
    
69.                 If Val(A(k).Innertext) >= 1 Then
    
70.                     Debug.Print A(k).Innertext
    
71.                     A(k).Click
    
72.                     Do While .Busy Or .readyState <> 4: DoEvents: Loop
    
73.                     Set A = .Document.all.tags("A")
    
74.                     Do While .Busy Or .readyState <> 4: DoEvents: Loop
    
75.                     For i = 2 To 3
    
76.                         ³æ­¶¸ü¤J .Document.all.tags("table")(i).outerHTML
    
77.                         With Range("A" & Rows.Count).End(xlUp).Offset(1)
    
78.                             .Select
    
79.                             Me.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
    
80.                         End With
    
81.                     Next
    
82.                 End If
    
83.             Next
    
84.         End If
    
85.         IEx.Quit
    
86.         Set IEx = Nothing
    
87.         ¾ã²z
    
88. NN:
    
89.         .Quit
    
90.     End With
    
91.     Set IE = Nothing
    
92.     ¹Ï§Î§ó·s
    
93. End Sub
    
94. Private Sub ³æ­¶¸ü¤J(S)
    
95.     With IEx
    
96.         .Document.body.innerHTML = S
    
97.         .ExecWB 17, 2       '  Select All
    
98.         .ExecWB 12, 2       '  Copy selection
    
99.     End With
    
100. End Sub
     
101. Private Sub ¾ã²z()
     
102.     On Error Resume Next
     
103.     Application.EnableEvents = False
     
104.     With UsedRange.Offset(10)
     
105.         .Replace "§Ç¸¹", "=ex", xlWhole
     
106.         .SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
     
107.     End With
     
108.     UsedRange(1).Select
     
109.     Application.EnableEvents = True
     
110. End Sub
     
111. Private Sub Get_Ie()
     
112.     Set IE = CreateObject("InternetExplorer.Application")
     
113.     With IE
     
114.        ' .Visible = True
     
115.         '¨é°Ó¶R½æÃÒ¨é¤é³øªí¬d¸ß¨t²Î¡]¤@¯ë¥æ©ö¡^
     
116.         .Navigate "http://www.gretai.org.tw/web/stock/aftertrading/broker_trading/brokerBS.php?l=zh-tw"
     
117.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
     
118.     End With
     
119. End Sub
     
120. Private Sub ¹Ï§Î§ó·s()
     
121.     If IE Is Nothing Then Get_Ie
     
122.     If Msg Then MsgBox "ÅçÃÒ¹Ï §ó·s§¹²¦"
     
123.     Msg = False
     
124.     With IE
     
125.         .Refresh
     
126.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
     
127.         ºô¸ô¹Ï¤ù¦sÀÉ .Document.all.tags("IMG")(0).href
     
128.     End With
     
129.     Sheet1.Shapes("ÅçÃÒ¹Ï").Fill.UserPicture ¹Ï§Î    '
     
130. End Sub
     
131. Private Sub ºô¸ô¹Ï¤ù¦sÀÉ(img As String)
     
132.     Dim xml As Object     '¥Î¨Ó¨ú±oºô­¶¸ê®Æ
     
133.     Dim stream            'As ADODB.stream   '¥Î¨ÓÀx¦s¤G¶i¦ìÀÉ®×
     
134.     Set xml = CreateObject("Microsoft.XMLHTTP")
     
135.     Set stream = CreateObject("ADODB.stream")
     
136.     xml.Open "GET", img, 0
     
137.     xml.send
     
138.     With stream
     
139.         .Open
     
140.         .Type = 1
     
141.         .write xml.ResponseBody
     
142.         If Dir(¹Ï§Î) <> "" Then Kill ¹Ï§Î
     
143.         .SaveToFile (¹Ï§Î)
     
144.         .Close
     
145.     End With
     
146. End Sub

½Æ»s¥N½X

HSIEN6001

¦^´_ 19# GBKEE

±z¦n,§Ú¤w¸g§ä¨ìCSV¤U¸üªº°Ñ¼Æ
GET
URL = "http://www.gretai.org.tw/web/stock/aftertrading/broker_trading/download_ALLCSV.php?curstk=" & ÃÒ¨é¥N¸¹ & "&stk_date=" & ¤é´Á & "&auth=" & ÅçÃÒ½X

§Ú§âªí¨­¶×¤J§ï¦¨³o¬q

1.     Dim xml As Object     '¥Î¨Ó¨ú±oºô­¶¸ê®Æ
   
2.     Dim stream            'As ADODB.stream   '¥Î¨ÓÀx¦s¤G¶i¦ìÀÉ®×
   
3.     Set xml = CreateObject("Microsoft.XMLHTTP")
   
4.     Set stream = CreateObject("ADODB.stream")
   
5. 
   
6. Dim path As String, url
   
7. path = "C:\"
   
8.     Dim ¤é´Á As String
   
9.     ¤é´Á = "1031215"
   
10. 
    
11. 'GET http://www.gretai.org.tw/web/stock/aftertrading/broker_trading/download_ALLCSV.php?curstk=ªÑ²¼¥N¸¹&stk_date=¤é´Á&auth=ÅçÃÒ½X
    
12. 
    
13.     url = "http://www.gretai.org.tw/web/stock/aftertrading/broker_trading/download_ALLCSV.php?curstk=" & Sheets("Sheet1").[F2] & "&stk_date=" & ¤é´Á & "&auth=" & Sheets("Sheet1").[F4]
    
14.         xml.Open "GET", url, 0
    
15.         xml.send
    
16.     With stream
    
17.         .Type = 1
    
18.         .Open
    
19.         .write xml.responseBody
    
20.         If Dir(path & Sheets("Sheet1").[F2] & ".csv") <> "" Then Kill (path & Sheets("Sheet1").[F2] & ".csv")
    
21.         .SaveToFile (path & Sheets("Sheet1").[F2] & ".csv")
    
22.         .Close
    
23.     End With

½Æ»s¥N½X

«D±`ÁÂÁ±zªº«ü¾É!!
^__^

joey0415

¦^´_ 23# HSIEN6001
¥i¤£¥i¤À¨É¤@­Ó°µ¦nªºzipÀÉ¡A¤è«K¬ã¨s¶Ü¡H

³o»ò¦hPrivate Sub¡A¤£¤Ó·|»P­×§ï

ÁÂÁ°Õ

flask

Sheets("¤WÂdªÑ²¼").Activate
    With CreateObject("InternetExplorer.application")           '³Ð«Ø¤@­ÓªÅªºie
        .Visible = True                                         'Åýie¥i¨£
        .Navigate "http://www.gretai.org.tw/web/stock/aftertrading/broker_trading/brokerBS.php?l=zh-tw"
        Do Until .ReadyState = 4               'µ¥«Ýie§¹²¦¸ü¤J
            DoEvents
        Loop
         code = InputBox("¿é¤JÅçÃÒ½X", "code", code)
        For Each stock In Range([a2], [a65536].End(xlUp))'ªÑ²¼¥N¸¹

        .Document.ALL("stk_code").Value = stock '¶ñ¼gªÑ²¼¥N¸¹
        .Document.ALL("auth_num").Value = code '¶ñ¼gÅçÃÒ½X
     
    With Sheets("web")
      .Cells.Delete
      .QueryTables.Add Connection:="URL;", Destination:=.Range("A1")
      
    With .QueryTables.Add(Connection:="URL;http://www.gretai.org.tw/web/stock/aftertrading/broker_trading/download_ALLCSV.php?curstk=" & stock & "&stk_date=" & ¤é´Á & "&auth=" & code & "", Destination:=.[a1])
        .AdjustColumnWidth = False
        .WebFormatting = xlWebFormattingNone
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With
   
    .Columns("A:A").TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)), _
        TrailingMinusNumbers:=True
        
    End With
End With
¤WÂdÅçÃÒ½X¦³60¬íªº¦³®Ä©Ê,¥u­n¥´­Ó10¨Ó¦¸´N¥i¤U¸ü§¹!

flask

Sheets("¤WÂdªÑ²¼").Activate
    With CreateObject("InternetExplorer.application")           '³Ð«Ø¤@­ÓªÅªºie
        .Visible = True                                         'Åýie¥i¨£
        .Navigate "http://www.gretai.org.tw/web/stock/aftertrading/broker_trading/brokerBS.php?l=zh-tw"
      
        Do Until .ReadyState = 4               'µ¥«Ýie§¹²¦¸ü¤J
            DoEvents
        Loop
       code = InputBox("¿é¤J¬d¸ßcode", "code", code)
        For Each stock In Range([a2], [a65536].End(xlUp))
        .document.All("stk_code").Value = stock
        .document.All("auth_num").Value = code 'Cells(1, 1)
   
    With Sheets("web")
    .Cells.Delete
      .QueryTables.Add Connection:="URL;", Destination:=.Range("A1")
       DoEvents
    With .QueryTables.Add(Connection:="URL;http://www.gretai.org.tw/web/stock/aftertrading/broker_trading/download_ALLCSV.php?curstk=" & stock & "&stk_date=" & ¤é´Á & "&auth=" & code & "", Destination:=.[a1])
        .AdjustColumnWidth = False
        .WebFormatting = xlWebFormattingNone
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With
   
    .Columns("A:A").TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)), _
        TrailingMinusNumbers:=True
        If .[a4] = " " Then .Cells.Delete: GoTo a
    End With
        If Sheets("web").[a4] = "ÅçÃÒ½X¤w¹O´Á¡A½Ð­«·s¬d¸ß" Then
        stock.Offset(, 2) = "ng"
        .Quit
        
        .Visible = True                                         'Åýie¥i¨£
        .Navigate "http://www.gretai.org.tw/web/stock/aftertrading/broker_trading/brokerBS.php?l=zh-tw"
        Do Until .ReadyState = 4               'µ¥«Ýie§¹²¦¸ü¤J
            DoEvents
        Loop
      
         code = InputBox("¿é¤J¬d¸ßcode", "code", code)
                GoTo a
        End If
    ¾ã­¶¤U¸ü    '¸ê°T¾ã²z
    a:
     Next
      End With
µ{§Ç«Ü¶Ã¬Û¤¬°Ñ¦Ò¤@¤U!

HSIEN6001

¦^´_ 25# flask

   .Columns("A:A").TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)), _
        TrailingMinusNumbers:=True

    ½Ð°Ý³o¬q«ç»òÀ³¥Î,§Ú¤]·Q­nzipÀÉ
¥i§_µ¹°Ñ¦Ò,ÁÂÁÂ

HSIEN6001

¦^´_ 24# joey0415

¦sÀɦì¸m¦Û¦æ­×¥¿
°Ñ¦Ò!
    ¤U¸üCSV.rar (10 KB)

¤U¸ü¦¸¼Æ: 28

2014-12-16 20:10

GBKEE

¦^´_ 28# HSIEN6001
ÁÙ¦³ Workbooks.Open ¥i¥Î

1. Option Explicit
   
2. Public IE As Object, Msg As Boolean
   
3. Const ¹Ï§Î = "d:\ÅçÃÒ¹Ï.jpg"
   
4. Const ÃÒ¨é¥N¸¹ = "F2"
   
5. Const ÅçÃÒ½X = "F4"
   
6. Private Sub Worksheet_Change(ByVal Target As Range)
   
7.     Range(ÃÒ¨é¥N¸¹).Interior.ColorIndex = IIf(Range(ÃÒ¨é¥N¸¹).Value = "", 2, 36)
   
8.     With Target.Cells(1)
   
9.          If .Address(0, 0) = ÅçÃÒ½X Then .Interior.ColorIndex = IIf(Len(Trim(.Cells)) = 5, 36, 2)
   
10.          If .Address(0, 0) = ÅçÃÒ½X And Len(Trim(.Cells)) = 5 And Range(ÃÒ¨é¥N¸¹).Value <> "" Then
    
11.             If IE Is Nothing Then
    
12.                 Target = ""
    
13.                 Msg = True
    
14.                 ¹Ï§Î§ó·s
    
15.                 Exit Sub
    
16.             End If
    
17.             Application.EnableEvents = False
    
18.             '¤é³øªí¸ü¤J
    
19.           CSV¸ü¤J
    
20.             Target = ""
    
21.             Application.EnableEvents = True
    
22.         End If
    
23.     End With
    
24. End Sub
    
25. 
    
26. Private Sub Get_Ie()
    
27.     Set IE = CreateObject("InternetExplorer.Application")
    
28.     With IE
    
29.         .Visible = True
    
30.         '¨é°Ó¶R½æÃÒ¨é¤é³øªí¬d¸ß¨t²Î¡]¤@¯ë¥æ©ö¡^
    
31.         .Navigate "http://www.gretai.org.tw/web/stock/aftertrading/broker_trading/brokerBS.php?l=zh-tw"
    
32.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
    
33.     End With
    
34. End Sub
    
35. 
    
36. Private Sub ¹Ï§Î§ó·s()
    
37.     If IE Is Nothing Then Get_Ie
    
38.     If Msg Then MsgBox "ÅçÃÒ¹Ï §ó·s§¹²¦"
    
39.     Msg = False
    
40.     With IE
    
41.         .Refresh
    
42.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
    
43.         ºô¸ô¹Ï¤ù¦sÀÉ .Document.all.tags("IMG")(0).href
    
44.     End With
    
45.     Sheet1.Shapes("ÅçÃÒ¹Ï").Fill.UserPicture ¹Ï§Î    '
    
46. End Sub
    
47. 
    
48. Private Sub ºô¸ô¹Ï¤ù¦sÀÉ(img As String)
    
49.     Dim xml As Object     '¥Î¨Ó¨ú±oºô­¶¸ê®Æ
    
50.     Dim stream            'As ADODB.stream   '¥Î¨ÓÀx¦s¤G¶i¦ìÀÉ®×
    
51.     Set xml = CreateObject("Microsoft.XMLHTTP")
    
52.     Set stream = CreateObject("ADODB.stream")
    
53.     xml.Open "GET", img, 0
    
54.     xml.send
    
55.     With stream
    
56.         .Open
    
57.         .Type = 1
    
58.         .write xml.responseBody
    
59.         If Dir(¹Ï§Î) <> "" Then Kill ¹Ï§Î
    
60.         .SaveToFile (¹Ï§Î)
    
61.         .Close
    
62.     End With
    
63. End Sub
    
64. 
    
65. Private Sub CSV¸ü¤J()
    
66.     Dim e As Object, A As Object, kDate As String, S As String
    
67.     Application.EnableEvents = True
    
68.     If IE Is Nothing Then
    
69.         ¹Ï§Î§ó·s
    
70.         MsgBox "ÅçÃҹϤw§ó·s"
    
71.         Exit Sub
    
72.     End If
    
73.     With IE
    
74.         .Document.all.tags("INPUT")("stk_code").Value = Range(ÃÒ¨é¥N¸¹)
    
75.         .Document.all.tags("INPUT")("auth_num").Value = Trim(Range(ÅçÃÒ½X))
    
76.         Set A = .Document.all.tags("BUTTON")
    
77.         For Each e In A
    
78.             If Trim(e.Innertext) = "¬d¸ß" And e.ID = "" Then
    
79.             e.Click
    
80.             Exit For
    
81.             End If
    
82.         Next
    
83.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
    
84.         If .Document.body.Innertext Like "***¸ÓªÑ²¼¸Ó¤éµL¥æ©ö¸ê°T***" Then S = "***¸ÓªÑ²¼¸Ó¤éµL¥æ©ö¸ê°T***"
    
85.         If .Document.body.Innertext Like "***ÅçÃÒ½X¿ù»~¡A½Ð­«·s¬d¸ß¡C***" Then S = "***ÅçÃÒ½X¿ù»~¡A½Ð­«·s¬d¸ß¡C*** "
    
86.         If S <> "" Then
    
87.             'Range("a" & k + 1) = S
    
88.             MsgBox S
    
89.             GoTo NN
    
90.         End If
    
91.         kDate = Format(Date, "emmdd")
    
92.         Application.DisplayAlerts = False
    
93.         With Workbooks.Open("http://www.gretai.org.tw/web/stock/aftertrading/broker_trading/download_ALLCSV.php?curstk=" & Range(ÃÒ¨é¥N¸¹) & "&stk_date=" & kDate & "&&auth=" & Range(ÅçÃÒ½X))
    
94.             .SaveAs "D:\" & Range(ÃÒ¨é¥N¸¹)
    
95.             With .Sheets(1)
    
96.                 .UsedRange.Range("G:K").SpecialCells(xlCellTypeConstants).Offset(1).Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
    
97.                 .UsedRange.Range("G:K").Clear
    
98.             End With
    
99.             .Close True
    
100.         End With
     
101.         Application.DisplayAlerts = True
     
102.         MsgBox "ÃÒ¨é¥N¸¹ : " & Range(ÃÒ¨é¥N¸¹) & " CSV ¸ü¤J§¹²¦"
     
103. NN:
     
104.         .Quit
     
105.     End With
     
106.     Set IE = Nothing
     
107.     ¹Ï§Î§ó·s
     
108. End Sub

½Æ»s¥N½X

HSIEN6001

¦^´_ 25# flask

§Ú¤w¸g¬ÝÀ´¤F,¤Ó¼F®`¤F
¯uªº«Ü§Ö!!ÁÂÁÂ