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

kuhsuanchieh

¤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)

»¡©úÀÉ
¤U¸ü¦¸¼Æ: 5

2015-9-24 18:45

GBKEE

¦^´_ 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

kuhsuanchieh

¦^´_ 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

GBKEE

¦^´_ 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

­ã´£³¡ªL

¶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)

¤U¸ü¦¸¼Æ: 14

2015-9-25 15:42

¸üÂI¢±¡Ghttp://www.funp.net/840179

GBKEE

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

·R»¡¯º¤Fª©¥Dµ{¦¡½X,§Ö¤Ó¦h¤F.
CreateObject("MSXML2.XMLHTTP")¥»¤å¶Ç°e§¹,ieÁÙ¦b¶}±Òµ¥­Ô¤¤...

kuhsuanchieh

¦^´_ 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