[µo°Ý] ¤W¥«­ÓªÑ¤é¦¨¥æ¸ê°T¤U¸ü§ïª©«Øij

prin.huang

­ì¥ý°Ñ¦Ò¯¸¤W¸ê®Æ¨Ï¥Îmsxml2.xmlhttp¼g¤F¤U¸ü¤W¥«ªÑ²¼¤é¦¨¥æ¸ê°Tªº¥¨¶°¡A¨S·Q¨ì¤~¥Î¨â¤Ñ¡A¤µ¤Ñ¬ðµM¤£¯à¨Ï¥Î¤F¡A­ì¨ÓTWSE¤S§ïª©¤F¡A¬d¤F¥b¤Ñmsxml2.xmlhttp¦n¹³¨S¿ìªk§ì°ÊºA²£¥Íªºªí®æ¸ê®Æ¡A·Q­×§ï¥¨¶°¡A«o¤£ª¾¸Ó¥Î¤°»ò¤è¦¡¡A¤£ª¾¬O§_¦³«Øijªº¤u¨ã¡H
ªþ¤W­ì¥ýªº¥¨¶°¨Ñ°Ñ ¤W¥«µý¨÷¤é¦¨¥æ.rar (59.21 KB)

¤U¸ü¦¸¼Æ: 27

2017-5-23 21:46

¦Ò

joey0415

¦^´_ 35# t8899

©xºôªº°ÝÃD¡A¹L¤@°}¤l´N¥¿±`

t8899

¦^´_  t8899

³£¥i¥H¦Û¤v¸ü¤Jexcel

¦Û¤w¿ý»s¤@¤U±Æ§ÇÀ³¸Ó«Ü²³æ

½m²ß¬Ý¬Ý
joey0415 µoªí©ó 2017-6-22 18:37

½Ð±Ð³s±µ®Éµ¥4¬í¦pªG¨S¦^À³´N¸õ¥X¡A­n¦p¦ó§ï??
  Application.ScreenUpdating = False
    Sheets("Sheet5").Select
Cells.ClearContents
ddd = "URL;http://www.twse.com.tw/exchangeReport/TWT48U?response=html"
With ActiveSheet.QueryTables.Add(Connection:=ddd, Destination:=Range("$A$1"))
.WebTables = "1"
.Refresh BackgroundQuery:=False
End With

joey0415

¦^´_ 33# t8899

³£¥i¥H¦Û¤v¸ü¤Jexcel

¦Û¤w¿ý»s¤@¤U±Æ§ÇÀ³¸Ó«Ü²³æ

½m²ß¬Ý¬Ý

t8899

¦^´_  t8899
joey0415 µoªí©ó 2017-6-22 18:29

¥i¥H«ö·Ó¤é´Á¥Ñ¤p¨ì¤j±Æ§Ç¶Ü?

joey0415

¦^´_ 31# t8899

http://www.twse.com.tw/exchangeReport/TWT48U?response=html

t8899

¦^´_  t8899
¦Û¤v½m²ß¬Ý¬Ý
joey0415 µoªí©ó 2017-6-3 20:50

¦A½Ð±Ð¤@¤U­±³o­Ó
http://www.twse.com.tw/zh/page/trading/exchange/TWT48U.html
  («ö·Ó°£Åv°£®§¤é´Á¥Ñ¤p¨ì¤j±Æ§Ç)

prin.huang

¦^´_ 29# jsleee
°²³]­n§ìhttp://www.twse.com.tw/exchangeReport/MI_INDEX?response=html&date=20170609&type=MS
³oºô­¶ªº²Ä¤@­Óªí¡A±q²Ä¤G¦C¶}©l§ì
(¦]µ{¦¡§PÂ_Äæ¦ì¯x°}«Å§i¾a­n§ì¨úªº²Ä¤@¦C¦³´X­ÓÄæ¦ì, ­Y­n±q²Ä¤@¦C¶}©l§ì, §A¦Û¤v­n¦A§ï¤@¤U)
¼g¤@¬qµ{¦¡µ¹function»Ý­nªº¸ê°T¡A¦A§âfunction¶Ç¦^ªº¸ê®Æ¡A¿é¥X¨ìexcel¬¡­¶ªí¡A°Ñ¦Ò¦p¤U

1. Sub main()
   
2. Dim URL$, VV As Boolean, AB() As String
   
3. 
   
4. URL = "http://www.twse.com.tw/exchangeReport/MI_INDEX?response=html&date=20170609&type=MS"
   
5. AB = GetWebTb1(URL, 1, 2, 1, VV)
   
6. If VV = True Then ActiveSheet.Range("A1:E63") = AB
   
7. 
   
8. End Sub
   
9. 
   
10. Private Function GetWebTb1(sURL00$, nTT00%, nRR00%, nCC00%, bRd00 As Boolean)
    
11. '===sURL00      ¬°Â^¨úºô§}
    
12. '===nTT00       ¬°Åª¨ú²Ä´X­ÓTable(±q1¶}©l)
    
13. '===nRR00       ¸ÓTable¥Ñ²Ä´X¦C¶}©lŪ¨ú(±q1¶}©l)
    
14. '===nCC00       ¸ÓTable¥Ñ²Ä´XÄæ¶}©lŪ¨ú(±q1¶}©l)
    
15. '===bRd00       ¸Ó¸ê®Æ¬O§_¿é¥X
    
16. Dim nR00%, nC00%, sTemp() As String, oXml As Object, oDoc As Object, oE As Object, tt As Date
    
17.     Set oXml = CreateObject("MSXML2.XMLHTTP.6.0")
    
18.     Set oDoc = CreateObject("HTMLFile")
    
19.     bRd00 = True
    
20. rSend:
    
21.     tt = Now() + TimeValue("0:00:20")
    
22.     With oXml
    
23.         .Open "Get", sURL00, True
    
24.         .setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    
25.         .send
    
26.         On Error Resume Next
    
27.         Do While .ReadyState <> 4 Or .Status <> 200
    
28.             DoEvents
    
29.             If Now > tt Then GoTo rSend
    
30.         Loop
    
31.         On Error GoTo 0
    
32.         oDoc.write .responseText
    
33.     End With
    
34.     If oDoc.all.tags("Table")(nTT00 - 1) Is Nothing Then bRd00 = False: GoTo Err1
    
35.     Set oE = oDoc.all.tags("Table")(nTT00 - 1)
    
36.     With oE
    
37.         ReDim sTemp(.Rows.Length - nRR00, .Rows(nRR00 - 1).Cells.Length - nCC00)
    
38.         For nR00 = 0 To .Rows.Length - nRR00
    
39.             For nC00 = 0 To .Rows(nR00 + nRR00 - 1).Cells.Length - nCC00
    
40.                 sTemp(nR00, nC00) = .Rows(nR00 + nRR00 - 1).Cells(nC00 + nCC00 - 1).innerText
    
41.             Next nC00
    
42.         Next nR00
    
43.     End With
    
44. Err1:
    
45.     GetWebTb1 = sTemp
    
46.     oXml.abort
    
47.     oDoc.Close
    
48.     Set oXml = Nothing
    
49.     Set oDoc = Nothing
    
50.     Set oE = Nothing
    
51. End Function

½Æ»s¥N½X

jsleee

¦^´_ 28# prin.huang


    ¤£¦n·N«ä
    ½Ð°Ý prin.huang ¥ý¶i
    ³o­ÓFunction ­n¦p¦ó¹B¥Î¡H

prin.huang

¦^´_ 27# jsleee
¦Û¤v¼gªºfunctionµ¹§A°Ñ¦Ò¡A·|¦^¶Ç¤@­Óstring°}¦C

1. Private Function GetWebTb1(sURL00$, nTT00%, nRR00%, nCC00%, bRd00 As Boolean)
   
2. '===sURL00      ¬°Â^¨úºô§}
   
3. '===nTT00       ¬°Åª¨ú²Ä´X­ÓTable(±q1¶}©l)
   
4. '===nRR00       ¸ÓTable¥Ñ²Ä´X¦C¶}©lŪ¨ú(±q1¶}©l)
   
5. '===nCC00       ¸ÓTable¥Ñ²Ä´XÄæ¶}©lŪ¨ú(±q1¶}©l)
   
6. '===bRd00       ¸Ó¸ê®Æ¬O§_¿é¥X
   
7. Dim nR00%, nC00%, sTemp() As String, oXml As Object, oDoc As Object, oE As Object, tt As Date
   
8.     Set oXml = CreateObject("MSXML2.XMLHTTP.6.0")
   
9.     Set oDoc = CreateObject("HTMLFile")
   
10.     bRd00 = True
    
11. rSend:
    
12.     tt = Now() + TimeValue("0:00:20")
    
13.     With oXml
    
14.         .Open "Get", sURL00, True
    
15.         .setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    
16.         .send
    
17.         On Error Resume Next
    
18.         Do While .ReadyState <> 4 Or .Status <> 200
    
19.             DoEvents
    
20.             If Now > tt Then GoTo rSend
    
21.         Loop
    
22.         On Error GoTo 0
    
23.         oDoc.write .responseText
    
24.     End With
    
25.     If oDoc.all.tags("Table")(nTT00 - 1) Is Nothing Then bRd00 = False: GoTo Err1
    
26.     Set oE = oDoc.all.tags("Table")(nTT00 - 1)
    
27.     With oE
    
28.         ReDim sTemp(.Rows.Length - nRR00, .Rows(nRR00 - 1).Cells.Length - nCC00)
    
29.         For nR00 = 0 To .Rows.Length - nRR00
    
30.             For nC00 = 0 To .Rows(nR00 + nRR00 - 1).Cells.Length - nCC00
    
31.                 sTemp(nR00, nC00) = .Rows(nR00 + nRR00 - 1).Cells(nC00 + nCC00 - 1).innerText
    
32.             Next nC00
    
33.         Next nR00
    
34.     End With
    
35. Err1:
    
36.     GetWebTb1 = sTemp
    
37.     oXml.abort
    
38.     oDoc.Close
    
39.     Set oXml = Nothing
    
40.     Set oDoc = Nothing
    
41.     Set oE = Nothing
    
42. End Function

½Æ»s¥N½X