ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[­ì³Ð] VBAºô¸ôª¦ÂÎ-XMLHTTPª«¥ó

[­ì³Ð] VBAºô¸ôª¦ÂÎ-XMLHTTPª«¥ó

¥»©«³Ì«á¥Ñ iamaraymond ©ó 2018-5-9 14:11 ½s¿è

¤§«eªº¤å:
http://forum.twbts.com/viewthread.php?tid=20748&fromuid=30076

­n¨Ï¥ÎXMLHTTP¡A­º¥ý­n¥ý¤Þ¥ÎXMLHTTPª«¥ó
¤Þ¥Î¬O¤@­Ó¬Æ»ò·§©À©O?
VBA³Ì°ò¥»ªº¥\¯à¤j³¡¤À¬O«]­­¦bExcelªº¤º³¡¾Þ§@¤W
¦Ó¤Þ¥Î«h¬O§âVBA±a¥X¤FExcel¡A¨Ó¨ì¥~­±ªº¥@¬É
Åܱo¥i¥H¾Þ±±¤å¥ó¡B¸ê®Æ§¨¡BIEÂsÄý¾¹µ¥µ¥
´N¹³¬O½Ð¨Ó¤F¤@­Ó±M®a¡A§A¥ÎVBA§i¶D³o­Ó±M®a§Ú­n°µ¬Æ»ò
Á|¤j®a¤ñ¸û¼ôªºIE¬°¨Ò¡A¨ä¹ê·í¤j®a¦b¨Ï¥ÎcreateObject("InternetExplorer.Application")®É
§A´N¹³¸u½Ð¤@¦ì±M®a¡A·í§A¥ÎVBA¼g¤U IE.navigate"ºô§}"¡A´N¬O½Ð¥L§âIEÂsÄý¨ì¬Y­Óºô­¶

¨º¦pªG§Ú­Ì¤µ¤Ñ­n¸u½Ð"Microsoft.XMLHTTP"³o¦ì±M®a®É¸Ó«ç»ò°µ©O?
¤Þ¥Î¦³2ºØ¤èªk:Early Binding ©M Late Binding

Early Binding´N¬O¦b¤u¨ã->³]©w¤Þ¥Î¶µ¥Ø->¤Ä¿ï¡uMicrosoft XML,v6.0¡v
ÀuÂI¬Oµ{¦¡°õ¦æ·|¤ñ¸û§Ö(¦ý§Ú¨S¹ê´ú¹L)¡A½s¼gµ{¦¡®É·|¶]¥X¹w³]¦r
¦ý¯ÊÂI¬O·í§A§âµ{¦¡¤À¨Éµ¹§O¤H®É¡A­Y¹ï¤è¨S¤Ä¿ï´N·|¥X¿ù
¤j®a¤@¯ë¬Ý¨ìªº¡u¨Ï¥ÎªÌ¦Û­q«¬ºA©|¥¼©w¸q¡v¡A³o­Ó¿ù»~´N¬O¥Nªí§A¨S¦³¤Ä¿ï§A­n¥Îªº¶µ¥Ø

¦]¦¹­Ó¤H¤ñ¸û³ßÅw¥ÎLate Binding¡A¤]´N¬O CreateObject("Microsoft.XMLHTTP")
¤ñ¸û¨S¦³¬Û®e©Êªº°ÝÃD¡A¦Ó¥B¹w³]¦r¨ä¹ê¤]¨S¦³«Ü¦h¡A¥´¤[¤F´N·|°O±o
¤@¯ë±`¥Îªº:
1. open "µo°e¤èªk","ºô§}",false
¦bºô¸ô¤Wµo°erequestªº¤è¦¡¦³¦n´XºØ¡A¨Ò¦pGET¡BPOST¡BPUT¡BDelete
¦ý­Y¥u¬O­nª¦ÂΪº¸Ü¡A¥u·|¨Ï¥ÎGET©MPOST¤èªk¡A¤j³¡¤Àºô¯¸³q±`³£¬O¥ÎGET¤èªk¦b¶Ç°e¸ê®Æ¡APOST¤ñ¸û¤Ö

2. send "Request Body"
¦bGET¤èªk¤U¡A¤£»Ý­nRequest Body¡ARequest Body¬O¦bPOST¤~¤ñ¸û·|¥Î¨ì

3. responseText
¥i±o¨ì¤U¸üªº¤º®e

4. responseBody
·íresponseText¥X²{¶Ã½X®É¤~¨Ï¥Î

¥H§Ú¦b http://forum.twbts.com/redirect. ... 3&fromuid=30076 ³o¸Ìªº¦^¤å¬°¨Ò
³q±`µ{¦¡·|¹³³o¼Ë
  1. Sub test()

  2. Cells.Clear

  3. 'stockno = InputBox("¿é¤JªÑ²¼¥N¸¹")

  4. Dim t: t = Timer
  5. Dim myArr(1 To 1500, 1 To 6)

  6. '*******************************************
  7. 'Part1 ¨Ï¥ÎLate Binding

  8. Dim myXML As Object
  9. Set myXML = CreateObject("Microsoft.XMLHTTP")
  10. '*******************************************
  11. 'Part2 §ì¨ú¸ê®Æ
  12. With myXML
  13.     .Open "GET", "http://jdata.yuanta.com.tw/Z/ZB/ZBH/CZKC0.djbcd?a=EB09999&b=W&c=1440", False
  14.     .send
  15.     myText = .responseText
  16. End With
  17. '*******************************************
  18. 'Part3 ¾ã²z¸ê®Æ
  19.     myText1s = Split(myText, " ")
  20.     j = 1
  21.     For Each myText1 In myText1s
  22.         i = 1
  23.         myText2s = Split(myText1, ",")
  24.         For Each myText2 In myText2s
  25.             If j > 6 Then Exit For
  26.             myArr(i, j) = myText2
  27.             i = i + 1
  28.         Next
  29.         j = j + 1
  30.     Next

  31. [A2:F2] = Array("¤é´Á", "¶}", "°ª", "§C", "¦¬", "¦¨¥æ¶q")
  32. [A3].Resize(1500, 6).Value = myArr

  33. Set myXML = Nothing

  34. Debug.Print Format(Timer - t, "0.00’")

  35. End Sub
½Æ»s¥N½X
¦¹µ{¦¡¥i¥H²³æ¤À¦¨3³¡¤À

1.¨Ï¥ÎLate Binding->¤]´N¬OSet myXML = CreateObject("Microsoft.XMLHTTP")
2.§ä¥X¸ê®Æ¨Ó·½¡A¨Ã¤U¸ü¤U¨Ó
3.¾ã²z¤U¸ü¤U¨Óªº¸ê®Æ¡A¦s¶iÀx¦s®æ¤¤

³q±`¤j®a¥dªº³£¬O²Ä2¨BÆJ:¤U¸ü¸ê®Æ¡A¤£ª¾¹D­þ¤@­Órequest¥]§t¤F¸ê®Æ
©Î¬O§Y¨Ï§ä¨ì¸ê®Æ¡A«o¦]¬°ºô¯¸¦UºØ¨¾Å@¾÷¨î¦Ó¤U¸ü¤£¤U¨Ó
¦]¦¹¤U¦¸´N¨Ó±Ð¤j®a¦p¦ó¥h§ä¥XÂ泸ê®Æªºrequest

¥»¨t¦C©«¤l¤]·|¦b§ÚªºBlog§ó·s
https://raymondchiendtrt.blogspot.tw/
Excel VBAºô­¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html

¦^´_ 1# iamaraymond


    «ôŪ¤F

´Á«Ý¤U¤@½Òªº¨ì¨Ó

·P®¦

TOP

·PÁ±оÇ
´Á«Ý·s½Òµ{

TOP

·PÁÂ2¦ì¡A­ì¥»·Qµo¤@­Ó«ç»ò§ä¨ì§O¤H¸ê®Æ®w¦ì¸mªº¤å¡A¥u¬O³Ìªñ¤@¦ì¤]¬O¼gª¦ÂΪº«e½ú´£¿ô§Ú¡A¨ä¹ê¦b½×¾Â¤½¶}§O¤H¸ê®Æ®wªº¦ì¸m´N¤£¬O«Ü¦n¤F¡A§ó¦óªp¬O¦b¤½¶}ªº½×¾ÂµoStep By Step¯}¸Ñªº¤å¡C¡C¡C¡A
¦n§a¡A©Ò¥H¤p§Ì¦b«ä¦Ò¤@¤U­n¦p¦ó¼g¤ñ¸û¦n¡A¤]·PÁÂ2¦ì­@¤ßµ¥­Ô
Excel VBAºô­¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html

TOP

·PÁª©¤j¼ö¤ßµo±Ð¾Ç¤å
¤p§Ì¹ï¸ê®Æ®w¤]¦³¿³½ì
´Á«Ýª©¤jªº§ä¸ê®Æ®w±Ðµ{

TOP

        ÀR«ä¦Û¦b : ¤H¥Í³Ì¤jªº¦¨´N¬O±q¥¢±Ñ¤¤¯¸°_¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD