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

[µo°Ý] ·Q½Ð±Ð¦p¦ó¦bºô­¶¡]«Dªí®æª¬ºA¡^§ì¸ê®Æ¡]¯S©w¦r¦ê¡^¨ìEXCELªí³æ

[µo°Ý] ·Q½Ð±Ð¦p¦ó¦bºô­¶¡]«Dªí®æª¬ºA¡^§ì¸ê®Æ¡]¯S©w¦r¦ê¡^¨ìEXCELªí³æ

¤w¸g¦³«Ü¦h¤j¤j¥æ¹L«ç»ò§âºô­¶¸ê®Æ§ì¨ìEXCELªí³æ¡A¦ý¬O¦n¹³³£¬O§ì©T©wªºªí³æ©Î¬Û¹ïÀ³ªºÄæ¦ì¡A
¥Ø«e¹J¨ì¤@­Ó°ÝÃD´N¬O·Q­n¦b
http://www.tyland.org.tw/pg.asp?theme=11&kinds=2&area=&search=o&review=&model=1&meid=&absolutepage=1
³o­Óºô§}¤¤¡A§ì¨ú·|­ûªº¸ê®Æ¡A¦ý¬O·|­ûªº¸ê®Æ»Ý­n¦AÂI¿ï·|­û©m¦W«á¦A¶i¤J¥t¤@­Ó­¶­±¡A
http://www.tyland.org.tw/view-m.asp?mno=1
¦Ó¥B§Ú¥u·Q­n§ì¨ú©m¦W¡B¹q¸Ü¡B¶Ç¯u¡B¦a§}¡]¶}·~°õ·Ó¦a§}¡^³o4­ÓÄæ¦ì¡A
¥i¥H°Ñ¾\¤@¤Uªþ¥ó¡A¤£ª¾¹D³o¼Ë¬O§_¦³¥i¯à¤@¦¸§ì§¹©Ò¦³·|­ûªº¸ê®Æ¡C


PS:·|­û¸ê®Æ¥i¯à«ùÄò¼W¥[©Î§ó·s¡C

­n§ìªº¸ê®Æºô§}.rar (75.16 KB)

¦^´_ 1# kuhsuanchieh


    ¸Õ¸Õ¬Ý
  1. Option Explicit
  2. '­¶¼Æ
  3. Const ­¶¼Æºô§} = "http://www.tyland.org.tw/pg.asp?theme=11&kinds=2&area=&search=o&review=&model=1&meid=&absolutepage="
  4. '·|¸¹ ID
  5. Const ID = "http://www.tyland.org.tw/view-m.asp?mno="
  6. Dim Sh As Worksheet, Ie As Object
  7. Sub Ex()
  8.     Ex²{¦b·|­û¦W¿ý
  9.     Ex_©Ò¦³·|­û¸ê®Æ
  10. End Sub
  11. Sub Ex²{¦b·|­û¦W¿ý()
  12.     Dim i As Integer, xTable As Object, r As Integer
  13.     Set Ie = CreateObject("InternetExplorer.Application")
  14.     Set Sh = Sheets(1)
  15.     With Sh
  16.         .UsedRange.Clear
  17.         .[A1:E1] = Array("ID", "©m¦W", "¹q¸Ü", "¶Ç¯u", "¦a§}")
  18.         '.[A1:D1] = Array( "©m¦W", "¹q¸Ü", "¶Ç¯u", "¦a§}")
  19.         .Activate
  20.     End With
  21.     With CreateObject("InternetExplorer.Application")
  22.       '  .Visible = True
  23.         For i = 1 To Max_Page
  24.             .Navigate ­¶¼Æºô§} & i
  25.             Do While .Busy Or .readyState <> 4: DoEvents: Loop
  26.            Set xTable = .Document.all.tags("table")(0).Rows
  27.            Application.StatusBar = ­¶¼Æºô§} & i & "  ¸ü¤J..."
  28.             For r = 1 To xTable.Length - 1
  29.                 Ex_²{¦b·|­û¸ê®Æ ID & xTable(r).Cells(1).INNERTEXT
  30.             Next
  31.         Next
  32.         .Quit
  33.     End With
  34.     Ie.Quit
  35.     Set Ie = Nothing
  36. End Sub
  37. Sub Ex_²{¦b·|­û¸ê®Æ(URL As String)
  38.     Dim ID As String, i As Integer, E As Variant, ii As Integer, t As Variant, AR()
  39.     ID = "http://www.tyland.org.tw/view-m.asp?mno="
  40.     AR = Array(0, 1, 2, 3, 6) 'AR = Array( 1, 2, 3, 6) ¤£­n"ID"
  41.     With Ie
  42.         '  .Visible = True
  43.             .Navigate URL
  44.             Do While .Busy Or .readyState <> 4: DoEvents: Loop
  45.             t = Split(.Document.BODY.INNERTEXT, vbLf)  'ºô­¶ªº¤å¦r,vbLf ¤Á³Î¬°°}¦C
  46.             With Sh.Range("A" & Rows.Count).End(xlUp).Offset(1)
  47.                 .Select  '¥i¤£¥Î
  48.                 For ii = 0 To 4
  49.                     .Cells(1, ii + 1) = Split(t(AR(ii)), "¡G")(1)
  50.                 Next
  51.             End With
  52.       
  53.     End With
  54. End Sub
  55. Function Max_Page() As Integer  '¶Ç¦^·|­û¦W¿ýªºÁ`­¶¼Æ
  56.     Dim E As Object
  57.     With CreateObject("InternetExplorer.Application")
  58.        ' .Visible = True
  59.         .Navigate "http://www.tyland.org.tw/pg.asp?theme=11"
  60.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  61.             For Each E In .Document.all.tags("A")
  62.                 If InStr(E.INNERTEXT, "³Ì«á¤@­¶") Then
  63.                     Max_Page = Replace(E.href, ­¶¼Æºô§}, "") 'ºô­¶¦r¦ê³Ì«áªº¼Æ¦r
  64.                     Exit For
  65.                 End If
  66.             Next
  67.         .Quit        'Ãö³¬ºô­¶
  68.     End With
  69. End Function
  70. '****************************************************
  71. Sub Ex_©Ò¦³·|­û¸ê®Æ()
  72.     Dim i As Integer, E As Variant, ii As Integer, t As Variant, AR()
  73.     Dim Sh As Worksheet
  74.     Set Sh = Sheets(2)
  75.     With Sh
  76.         .UsedRange.Clear
  77.         .[A1:E1] = Array("ID", "©m¦W", "¹q¸Ü", "¶Ç¯u", "¦a§}")
  78.         '.[A1:D1] = Array( "©m¦W", "¹q¸Ü", "¶Ç¯u", "¦a§}")
  79.         .Activate
  80.     End With
  81.     AR = Array(0, 1, 2, 3, 6)  'AR = Array( 1, 2, 3, 6) '¤£­n"ID"
  82.     With CreateObject("InternetExplorer.Application")
  83.          ' .Visible = True
  84.         For i = 1 To Max_Id
  85.             .Navigate ID & i
  86.             Do While .Busy Or .readyState <> 4: DoEvents: Loop
  87.             Application.StatusBar = ID & i & "  ¸ü¤J..."
  88.             t = Split(.Document.BODY.INNERTEXT, vbLf)
  89.             If UBound(t) > -1 Then
  90.                 With Sh.Range("A" & Rows.Count).End(xlUp).Offset(1)
  91.                     .Select  '¥i¤£¥Î
  92.                     For ii = 0 To 4   'For ii = 0 To 3  '¤£­n"ID
  93.                         .Cells(1, ii + 1) = Split(t(AR(ii)), "¡G")(1)
  94.                     Next
  95.                 End With
  96.             End If
  97.         Next
  98.         .Quit
  99.     End With
  100. End Sub
  101. Function Max_Id() As Integer '¬d§ä³Ì·s·|­ûªº·|¸¹
  102.     Dim E As Object
  103.     With CreateObject("InternetExplorer.Application")
  104.        ' .Visible = True
  105.         .Navigate "http://www.tyland.org.tw/pg.asp?theme=11"
  106.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  107.             For Each E In .Document.all.tags("A")
  108.                 If InStr(E.INNERTEXT, "³Ì«á¤@­¶") Then
  109.                     E.Click   '«ö¤U "³Ì«á¤@­¶"
  110.                     Exit For
  111.                 End If
  112.             Next
  113.            Do While .Busy Or .readyState <> 4: DoEvents: Loop
  114.            Set E = .Document.all.tags("table")(0).Rows
  115.             Max_Id = E(E.Length - 1).Cells(1).INNERTEXT  '³Ì·s·|­ûªº·|¸¹
  116.         .Quit        'Ãö³¬ºô­¶
  117.     End With
  118. End Function
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE

·PÁ GBKEE ª©¥Dªº»¡©ú¡A¦ý¬O§Ú·Q½Ð±Ð¤¤¶¡¦³Ãö¡u¶Ç¦^·|­û¦W¿ýªºÁ`­¶¼Æ¡v³o­Ó³¡¤À¡A
¦pªG¤£­n¦³³o­Ó°Ê§@¡A¬O§_´NµLªk¥þ¼Æ§ì¦^¨Ó¡H¨º§Ú¥i¥H¤U
i=1 to 9999
³o¼Ë¤l¶Ü¡H

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2015-9-25 15:32 ½s¿è

¦^´_ 3# kuhsuanchieh
  1. For i = 1 To Max_Id
  2.            .Navigate ID & i
½Æ»s¥N½X
Sub Ex_©Ò¦³·|­û¸ê®Æ().¥i¥H§ï¦¨¦p¦¹,
·|­û¸¹½X³Ì«á¬O 1407,9999·|¦h¶]«Ü¤[ªº
  1. For i = 1 To 9999
  2.            .Navigate ID & i
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¶Wª©¹ïºô­¶«D±`±M·~¡Aµ{¦¡¤]·V¨D¡e¥¿½T§¹¾ã¡f¤Î¡e®Ä²v¡f¡A
¨ú±o¡e­¶¼Æ¡f¤Î¡e³Ì¤jID¡f¤~¥i°µ³Ì¤pªº°j°é¡A»{¯u­t³dªº¤uµ{®v¤~·|³o¼Ë¤£¹½³Ò·Ð¡A¨ØªA¡I¡I¡I
¡@
¡@
´£¨Ñ¥t­Ó¤£±M·~°Ñ¦Ò¡G
  1. Sub TEST()
  2. Dim UL$, STR, FN&, j&, k%, Arr, X&, N&, TM
  3. TM = Time
  4. [Sheet1!A:E].ClearContents: [F1] = ""
  5. [Sheet1!A1:E1] = Array("ID", "©m¦W", "¹q¸Ü", "¶Ç¯u", "¦a§}")
  6. ¡@
  7. '¡õ¨ú±o¡eÁ`­¶¼Æ¡f
  8. STR = ºô­¶­ì©l½X("http://www.tyland.org.tw/pg.asp?theme=11")
  9. STR = Split(Split(STR, ">³Ì«á¤@­¶")(0), "absolutepage=")
  10. FN = STR(UBound(STR))
  11. ¡@
  12. '¡õ¨ú±o¡e³Ì¤j¢×¢Ò½s¸¹¡f
  13. UL = "http://www.tyland.org.tw/pg.asp?theme=11&kinds=2&area=&search=o&review=&model=1&meid=&absolutepage="
  14. STR = ºô­¶­ì©l½X(UL & FN)
  15. STR = Split(STR, "view-m.asp?mno=")
  16. FN = Split(STR(UBound(STR)), "'>")(0)
  17. ¡@
  18. '¡õ¶}©lÂ^¨ú¸ê®Æ¡]§t©Ò¦³¸ê®Æ¡^
  19. ReDim Arr(1 To FN, 1 To 5)
  20. For j = 1 To FN
  21. ¡@¡@Application.StatusBar = "¡½¡½¡½¡½¡½¡½¸ê®ÆÂ^¨ú¤¤¡G" & j & "¡þ" & FN
  22. ¡@¡@STR = ºô­¶­ì©l½X("http://www.tyland.org.tw/view-m.asp?mno=" & j)
  23. ¡@¡@If Len(STR) = 0 Then GoTo 101
  24. ¡@¡@X = InStr(STR, "<li>·|¡@¡@¸¹¡G")
  25. ¡@¡@If X = 0 Then GoTo 101
  26. ¡@¡@STR = Split(Mid(STR, X), "</li>")
  27. ¡@
  28. ¡@¡@N = N + 1
  29. ¡@¡@For k = 1 To 4 'ID.©m¦W.¹q¸Ü.¶Ç¯u
  30. ¡@¡@¡@¡@Arr(N, k) = Trim(Split(STR(k - 1), "¡G")(1))
  31. ¡@¡@Next k
  32. ¡@¡@Arr(N, 5) = Trim(Split(STR(6), "¡G")(1)) '¦a§}
  33. 101: Next j
  34. ¡@
  35. If N > 0 Then [A2:E2].Resize(N) = Arr
  36. [Sheet1!F1] = Format(Time - TM, "hh:mm:ss")
  37. Application.StatusBar = False
  38. Beep
  39. End Sub
  40. ¡@
  41. ¡@
  42. '¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×°Æµ{¦¡
  43. Function ºô­¶­ì©l½X(xURL$) As String
  44. On Error Resume Next
  45. With CreateObject("MSXML2.XMLHTTP")
  46.      .Open "POST", xURL, False
  47.      .send
  48.      ºô­¶­ì©l½X = .ResponseText
  49. End With
  50. On Error GoTo 0
  51. End Function
½Æ»s¥N½X
¡@
¸üÂI¢°¡G 20150925Â^¨úºô­¶¤å¦r.rar (12.67 KB)
¸üÂI¢±¡Ghttp://www.funp.net/840179

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2015-9-26 10:35 ½s¿è

¦^´_ 5# ­ã´£³¡ªL

·R»¡¯º¤Fª©¥Dµ{¦¡½X,§Ö¤Ó¦h¤F.
CreateObject("MSXML2.XMLHTTP")¥»¤å¶Ç°e§¹,ieÁÙ¦b¶}±Òµ¥­Ô¤¤...
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 5# ­ã´£³¡ªL


¬Ý§¹¨â¦ìªºµ{¦¡¡A¤p§Ì¥u¯à»¡¤­Åé§ë¦aªº¨ØªA¡A

¦A¥[¤W¨º­Ó®É¶¡¡A§Ú¤£¸T¯º¥X¨Ó¤F¡I

·PÁ¨â¦ì¤j¤jªº«ü¾É¡A§Ú¬Ý§Ú¤S­n¦n¦n¬ã¨s¤@¨â­Ó§«ô¤F¡I

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD