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

[­ì³Ð] Excel VBA ¦Û°Ê§ì¨ú´Á¥æ©Ò ´Á³f¨C¤é¥æ©ö¦æ±¡

[­ì³Ð] Excel VBA ¦Û°Ê§ì¨ú´Á¥æ©Ò ´Á³f¨C¤é¥æ©ö¦æ±¡

¤å³¹¨Ó·½:https://raymondchiendtrt.blogspot.com/2018/05/excel-vba_29.html

³o¦¸ªºµ{¦¡¬O¥i¥H¨Ì¾Ú¨Ï¥ÎªÌ¿é¤Jªº¤é´Á
¨Ó§ì¨ì·í¤Ñªº´Á³f¸ê®Æ

³o¦¸µ²¦X¤F´X­Ó§Þ¥©
1.³]©wRequestheader
¥H§Ú¤§«eÆ[¹î¨ìªº¡A¦pªG¬O¥ÎMicrosoft.XMLHTTP
§Y¨Ï³]©wrequestheader¤]¬OµL®Äªº
©Ò¥H¥²¶·§ï¥ÎWinHttp.WinHttpRequest.5.1

2.Âà½X
§Ú¥­±`¯à¤£¥ÎWinHttp´N¤£¥Î
¦]¬°¨Ï¥Î³o­Óª«¥ó±`±`´N·|Åܦ¨¶Ã½X
¥²¶·¦h¨Ï¥ÎÂà½Xµ{¦¡¤~¯à¨úªº¸ê®Æ(½Ð¨£¤U¤èªºconvertraw function)

3.¨Ï¥Îclipboard
³o­Óª«¥ó«Ü¦n¥Î¡A·|§â¦bºô¯¸¤Wªº®æ¦¡¤@¨Ö¤U¸ü¤U¨Ó
©Ò¥H¦pªG§A­n§ìªº¸ê®Æ¦³¦X¨ÖÀx¦s®æ(¹³goodinfo)¡A©Î¬O¦³ÃC¦âµ¥µ¥ªº
¥i¥H¥Îclipboard¬Ý¬Ý
¦ý¯ÊÂI´N¬Ocreateobject«ÜÃø°OXD¡A¨C¦¸³£­n¥Î¬dªº
ÁÙ¦³¦ü¥G¤£¬O¨C­Óªí®æ(table)³£§ìªº¨ì¡A­n¸Õ¸Õ¬Ý

4.¨Ï¥Îapplication.wait
³q±`¬F©²ªººô¯¸­Y¬O§ì¤Ó§Ö«Ü®e©ö³QÂêIP
©Ò¥H§Q¥Î¦¹»yªk­°§Cµ{¦¡³t«×
¤@¯ë¨Ó»¡³]3¬í´N¦n¡A¦pªG«Ü©È³QÂê¥i¥H¦A³]¤[¤@ÂIXD

¤j·§¬O³o¼Ë¡A­Y¦³°ÝÃD¤]Åwªï¦b¤U¤è´£¥X
  1. Sub test()

  2. Dim myXML As Object
  3. Set myXML = CreateObject("WinHttp.WinHttpRequest.5.1")

  4. Dim myHTML As Object
  5. Set myHTML = CreateObject("HTMLFile")

  6. Dim clipboard As Object
  7. Set clipboard = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

  8. ReDim myArr(1 To 10, 1 To 20)

  9. dateLR = Cells(Rows.Count, "A").End(xlUp).Row

  10. With myXML
  11.    
  12.     For dateRow = 6 To dateLR
  13.         Application.Wait Now() + TimeValue("00:00:03")
  14.         myM = Format(Month(Cells(dateRow, "A")), "00")
  15.         myD = Format(Day(Cells(dateRow, "A")), "00")
  16.         
  17.         .Open "POST", "http://www.taifex.com.tw/chinese/3/3_1_1.asp", False
  18.         .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
  19.         .send "qtype=2&commodity_id=TX&commodity_id2=&market_code=0&goday=&dateaddcnt=0&DATA_DATE_Y=2018&DATA_DATE_M=05&DATA_DATE_D=22&syear=2018&smonth=" & myM & "&sday=" & myD & "&datestart=2018%2F05%2F15&MarketCode=0&commodity_idt=TX&commodity_id2t=&commodity_id2t2="
  20.         
  21.         myHTML.body.innerHTML = convertraw(.responseBody)
  22.         'Debug.Assert InStr(1, myText, "10368") <> 0
  23.         
  24.         Set myTables = myHTML.getElementsByTagName("table")
  25.         i = 1
  26.         For Each myTable In myTables
  27.             If myTable.getAttribute("width") = 965 Then
  28.             
  29.                 textLR = Cells(Rows.Count, "D").End(xlUp).Row
  30.                 textLR = IIf(textLR = 1, 5, textLR + 5)
  31.                 Cells(textLR, 4).Select
  32.                 Cells(textLR - 1, 4) = myTable.PreviousSibling.innerText
  33.                 Cells(textLR - 1, 4).WrapText = False
  34.                 With clipboard
  35.                     .SetText myTable.outerHTML
  36.                     .PutInClipboard
  37.                 End With
  38.                 Sheets("¤u§@ªí1").PasteSpecial NoHTMLFormatting:=False

  39.                 Exit For
  40.             End If
  41.         Next
  42.         
  43.     Next
  44. End With
  45. Set myXML = Nothing

  46. End Sub
  47. Function convertraw(rawdata)

  48. Dim rawstr
  49. Set rawstr = CreateObject("adodb.stream")
  50. With rawstr
  51. .Type = 1
  52. .Mode = 3
  53. .Open
  54. .Write rawdata
  55. .Position = 0
  56. .Type = 2
  57. .Charset = "UTF-8"
  58. convertraw = .ReadText
  59. .Close
  60. End With
  61. Set rawstr = Nothing

  62. End Function
½Æ»s¥N½X
´Á³f¥æ©ö¦æ±¡¬d¸ß.zip (29.31 KB)

¨Ï¥Î¤è¦¡½Ð°Ñ¦Ò¼v¤ù
https://www.youtube.com/watch?v=Eg-2uYxa0q4
Excel VBAºô­¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html

¤å³¹¨Ó·½:https://raymondchiendtrt.blogspot.com/2018/05/excel-vba_29.html

³o¦¸ªºµ{¦¡¬O¥i¥H¨Ì¾Ú¨Ï¥Î ...
iamaraymond µoªí©ó 2018-5-30 00:51



    ½Ð°Ý¯à§_¦Û°Ê§ì¨ú³Ìªñ¤T¤Q­Ó¥æ©ö¤éªº¸ê®Æ?·PÁÂ

TOP

¦^´_ 2# netfish777


«¢Åo©êºp¡A³o´X¤Ñ¤Ó¦£¡A­è­è¼g¤F¤@­Ó·sÀÉ®×
½Ð°Ñ¦Ò
http://forum.twbts.com/thread-20860-1-1.html
Excel VBAºô­¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html

TOP

µÌ¸z·PÁµL¨p¤À¨É

TOP

        ÀR«ä¦Û¦b : ¦Û¤v®`¦Û¤v¡A²ö¹L©ó¶ÃµoµÊ®ð¡C
ªð¦^¦Cªí ¤W¤@¥DÃD