µù¥U
µn¿ý
º¶
½×¾Âª©³W
¸T¤î¦Cªí
»¡©ú
¦a¹Ï
¼Æ¦ì®Ñ»
¨p¤H®ø®§ (0)
¤½¦@®ø®§ (0)
½×¾Â¥ô°È (0)
¨t²Î®ø®§ (0)
¦n¤Í®ø®§ (0)
©«¤l®ø®§ (0)
³Â»¶®a±Ú°Q½×ª©ª©
»
Excelµ{¦¡°Ï
» VBAºô¸ôª¦ÂÎ-XMLHTTPª«¥ó
ªð¦^¦Cªí
¤U¤@¥DÃD
¤W¤@¥DÃD
µo©«
[ì³Ð]
VBAºô¸ôª¦ÂÎ-XMLHTTPª«¥ó
iamaraymond
µoµu®ø®§
¥[¬°¦n¤Í
iamaraymond
(Raymond Chien)
·í«eÂ÷½u
UID
30076
©«¤l
109
¥DÃD
12
ºëµØ
0
¿n¤À
177
ª÷¿ú
177
ÂI¦W
0
§@·~¨t²Î
win10
³nÅ骩¥»
office2013
¾\ŪÅv
20
©Ê§O
¨k
¨Ó¦Û
°ª¶¯
¦b½u®É¶¡
241 ¤p®É
µù¥U®É¶¡
2017-11-19
³Ì«áµn¿ý
2023-7-5
¡@¼ÊºÙ:
Raymond Chien
¤¤¾Ç¥Í
©«¤l
109
¥DÃD
12
ºëµØ
0
¿n¤À
177
ÂI¦W
0
§@·~¨t²Î
win10
³nÅ骩¥»
office2013
¾\ŪÅv
20
©Ê§O
¨k
¨Ó¦Û
°ª¶¯
µù¥U®É¶¡
2017-11-19
³Ì«áµn¿ý
2023-7-5
1
#
¸õÂà¨ì
»
˧Ǭݩ«
¥´¦L
¦rÅé¤j¤p:
t
T
µoªí©ó 2018-5-9 13:59
|
Åã¥Ü¥þ³¡©«¤l
[ì³Ð]
VBAºô¸ôª¦ÂÎ-XMLHTTPª«¥ó
VBA
,
ºô¸ôª¦ÂÎ
¥»©«³Ì«á¥Ñ 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®É¡AY¹ï¤è¨S¤Ä¿ï´N·|¥X¿ù
¤j®a¤@¯ë¬Ý¨ìªº¡u¨Ï¥ÎªÌ¦Ûq«¬ºA©|¥¼©w¸q¡v¡A³oÓ¿ù»~´N¬O¥Nªí§A¨S¦³¤Ä¿ï§An¥Îªº¶µ¥Ø
¦]¦¹Ó¤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¬Onª¦ÂΪº¸Ü¡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¼Ë
Sub test()
Cells.Clear
'stockno = InputBox("¿é¤JªÑ²¼¥N¸¹")
Dim t: t = Timer
Dim myArr(1 To 1500, 1 To 6)
'*******************************************
'Part1 ¨Ï¥ÎLate Binding
Dim myXML As Object
Set myXML = CreateObject("Microsoft.XMLHTTP")
'*******************************************
'Part2 §ì¨ú¸ê®Æ
With myXML
.Open "GET", "http://jdata.yuanta.com.tw/Z/ZB/ZBH/CZKC0.djbcd?a=EB09999&b=W&c=1440", False
.send
myText = .responseText
End With
'*******************************************
'Part3 ¾ã²z¸ê®Æ
myText1s = Split(myText, " ")
j = 1
For Each myText1 In myText1s
i = 1
myText2s = Split(myText1, ",")
For Each myText2 In myText2s
If j > 6 Then Exit For
myArr(i, j) = myText2
i = i + 1
Next
j = j + 1
Next
[A2:F2] = Array("¤é´Á", "¶}", "°ª", "§C", "¦¬", "¦¨¥æ¶q")
[A3].Resize(1500, 6).Value = myArr
Set myXML = Nothing
Debug.Print Format(Timer - t, "0.00’")
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/
¼Ó¥D¼ö©«
¶°«O¸ê®Æ(§ïª©«á)
Excel VBA ¿ù»~³B²z¬yµ{(¥þ¹Ï¸Ñ)
VBAºô¸ôª¦ÂÎ(ºô¸ô¸ê®Æ»`¶°)
Excel VBA ¦Û°Ê§ì¨ú´Á¥æ©Ò ´Á³f¨C¤é¥æ©ö¦æ±¡
[±Ð¾Ç¤å] §Q¥ÎVBA§ìªÑ»ù¸ê®Æ--§ä¥Xºô¯¸¸ê®Æ
Excel VBA ´Á¥æ©Ò¸ê®Æ¦Ûq¤é¼Æ¬d¸ß
ªí³æ°ÝÃD ½Ð°Ý¦p¦ó±±¨îcheckbox¼Æ¶q
½Ð°Ý³oÓ¥I¶O¥\¯à¬O¦p¦ó³]©wªº©O?
·Q½Ð±Ð¨Ï¥ÎFindWindow«ö¤U¹ï¸Ü®Øªº"Àx¦s"
¦¬ÂÃ
¤À¨É
Excel VBAºô¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html
iamaraymond
µoµu®ø®§
¥[¬°¦n¤Í
iamaraymond
(Raymond Chien)
·í«eÂ÷½u
UID
30076
©«¤l
109
¥DÃD
12
ºëµØ
0
¿n¤À
177
ª÷¿ú
177
ÂI¦W
0
§@·~¨t²Î
win10
³nÅ骩¥»
office2013
¾\ŪÅv
20
©Ê§O
¨k
¨Ó¦Û
°ª¶¯
¦b½u®É¶¡
241 ¤p®É
µù¥U®É¶¡
2017-11-19
³Ì«áµn¿ý
2023-7-5
¡@¼ÊºÙ:
Raymond Chien
¤¤¾Ç¥Í
©«¤l
109
¥DÃD
12
ºëµØ
0
¿n¤À
177
ÂI¦W
0
§@·~¨t²Î
win10
³nÅ骩¥»
office2013
¾\ŪÅv
20
©Ê§O
¨k
¨Ó¦Û
°ª¶¯
µù¥U®É¶¡
2017-11-19
³Ì«áµn¿ý
2023-7-5
2
#
µoªí©ó 2018-5-13 10:05
|
Åã¥Ü¥þ³¡©«¤l
·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«ä¦Ò¤@¤Un¦p¦ó¼g¤ñ¸û¦n¡A¤]·PÁÂ2¦ì@¤ßµ¥Ô
Excel VBAºô¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html
TOP
ÀR«ä¦Û¦b :
¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí
¤U¤@¥DÃD
¤W¤@¥DÃD
¤@¯ë¹q¸£³nÅé°Q½×
EXCEL±MÄÝ°Q½×°Ï
Excel£¸¯ë°Ï
Excelµ{¦¡°Ï
¶i¶¥À³¥Î±M°Ï
OFFICE ¨t¦C
Word
PowerPoint
Access
Office¤£¤À°Ï
µ{¦¡»y¨¥
VB »P VB.Net
C »P C#
Java »P J#
µ{¦¡³]p¤£¤À°Ï
¸ê®Æ®w
ORACLE
My SQL
MS SQL
ºô¶³]p
ASP »P ASP.NET
PHP
PHP+MySQL ¤Jªù¹ê§@
JavaScript
FLASH / ActionScript
HTM/ HTML/ CSS
ºô¶³]p¤£¤À°Ï
¹q¸£»P§@·~¨t²Î
¹q¸£¦UºØµwÅé°Q½×
¤@¯ë¹q¸£³nÅé°Q½×
½×¾Â¨Æ°È
ºÞ²z¤½§i
§ë¶D¤Ï¬M
·s¤â´ú¸Õ
·R ¡E ¥Í¬¡
¤½¯q§G§iÄæ
¥Í¬¡»P·P°Ê
[¦¬Âæ¹¥DÃD]
[Ãöª`¦¹¥DÃDªº·s¦^´_]
[³q¹L QQ¡BMSN ¤À¨Éµ¹ªB¤Í]
¥Ó½Ð¤Í±¡Ãì±µ
Facebook¯»µ·