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

[µo°Ý] µLªk¥Î¥~³¡¸ê®Æ¶×¤Jªººô¯¸¯à¥ÎVBAŪ¨ú¸ê®Æ¶Ü¡H

[µo°Ý] µLªk¥Î¥~³¡¸ê®Æ¶×¤Jªººô¯¸¯à¥ÎVBAŪ¨ú¸ê®Æ¶Ü¡H

ªñ¤é¦b°µ¤@¨Ç°lÁa¬ã¨s¡A»Ý­n©w´Á¨ì¤U­±ºô¯¸Åª¨ú©Ò¦³¤½¥qªº¦WºÙ¡B¦a§}©M¦a§}Ãþ«¬
https://eservices.customs.gov.hk/MSOS/wsrh/001s1?searchBy=B

§Ú¸Õ¹L¥Î¡u¥~³¡¸ê®Æ¶×¤J¡v©M¿ý»s¥¨¶°¨Ó³B²z¡A¦ýEXCEL¬Ý¨ÓŪ¤£¨ì¨º¨ÇTABLEªº¸ê®Æ¡C
½Ð°ÝVBA¯à°÷³B²z¶Ü¡HÁÂÁ¤j®a¡C

  1. Sub GetData()


  2. URL = "https://eservices.customs.gov.hk/MSOS/wsrh/001s1?searchBy=A"

  3. With CreateObject("InternetExplorer.Application")
  4.   .Visible = True     '  ¬O§_Åã¥Ü IE
  5.   .Navigate URL

  6. Do While .ReadyState <> 4 Or .Busy

  7. DoEvents

  8. Loop

  9. xlHtm = .Document.body.innerHTML
  10. End With

  11. End Sub
½Æ»s¥N½X
§Ú¸Õ¤F¥ÎCREATEOBJECT¡A¦ý¦³¨Ç¸ê®Æ¦h¹L¤@­¶®É¡A½Ð°Ý¦p¦óÂà­¶¡H

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-6-7 18:23 ½s¿è

¦^´_ 2# ¤p«L«È
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Const FormDLL = "FM20.DLL"
  3. Sub IE¤U¤@­¶()
  4.     Dim URL As String, A As Object, Table As Object, i As Integer, pubSrch As Object, Pages As Integer
  5.     Dim Sh As Worksheet, B As String
  6.     Set_FormDLL
  7.     Set Sh = ActiveSheet
  8.     Sh.Cells.Clear
  9.     URL = "https://eservices.customs.gov.hk/MSOS/wsrh/001s1?searchBy=A"
  10.     With CreateObject("InternetExplorer.Application")
  11.         .Visible = True     '  ¬O§_Åã¥Ü IE
  12.         .Navigate URL
  13.         Do While .ReadyState <> 4 Or .Busy
  14.             DoEvents
  15.         Loop
  16.         Set A = .Document.getElementsByTAGName("A")
  17.         For i = 0 To A.Length - 1
  18.             If A(i).innertext = "ALL" Then
  19.                 A(i).Click
  20.                 Exit For
  21.             End If
  22.         Next
  23.         Do While .ReadyState <> 4 Or .Busy
  24.             DoEvents
  25.         Loop
  26.         Do
  27.             Set A = .Document.getElementsByTAGName("TD")
  28.         Loop Until Not A Is Nothing And A.Length = 84
  29.         For i = 0 To A.Length - 1
  30.             If A(i).ID = "grid-table-pubSrch_center" Then
  31.                 Pages = Val(Replace(A(i).innertext, "Page  of ", ""))        'Á`­¶¼Æ
  32.             End If
  33.             If A(i).ID = "next_grid-table-pubSrch" Then Set pubSrch = A(i)                         '¤U¤@­¶«öÁä
  34.         Next
  35.         Set Table = .Document.getElementsByTAGName("table")
  36.         For i = 1 To Pages
  37.             If i >= 2 Then
  38.                 pubSrch.Click
  39.                 Do While .ReadyState <> 4 Or .Busy:        Loop
  40.                 Set Table = .Document.getElementsByTAGName("table")
  41.                 Do:  Loop Until B <> Table(6).outerHTML
  42.             End If
  43.             Ep Sh, Table(6).outerHTML
  44.             B = Table(6).outerHTML
  45.         Next
  46.         .Quit
  47.     End With
  48.     With Sh
  49.         .Cells.WrapText = False
  50.         Intersect(Range("b:b").SpecialCells(xlCellTypeBlanks).EntireRow, .Range("c:c").SpecialCells(xlCellTypeBlanks).EntireRow).Delete
  51.         .Cells.EntireRow.AutoFit
  52.         .Range("a1").Activate
  53.     End With
  54.     Remove_FormDLL

  55. End Sub
  56. Private Sub Set_FormDLL()   '·s¼W¤Þ¥Î Microsoft Forms 2.0 Object Library
  57.     On Error Resume Next
  58.     ThisWorkbook.VBProject.References.AddFromFile "C:\windows\system32\" & FormDLL
  59.   '  On Error GoTo 0
  60. End Sub
  61. Private Sub Remove_FormDLL() '§R°£¤Þ¥Î Microsoft Forms 2.0 Object Library
  62.     Dim D As Object
  63.     For Each D In ThisWorkbook.VBProject.References
  64.         If UCase(D.fullpath) Like "*" & FormDLL Then
  65.             ThisWorkbook.VBProject.References.Remove D
  66.         End If
  67.     Next
  68. End Sub
  69. Private Sub Ep(Sh As Worksheet, S As String)
  70.     Dim D As New DataObject, E As Shape, FormDLL As String ', Rng As Range
  71.     'DataObject ª«¥ó ¦b¶i¦æÂà´«°Ê§@®É¡A°µ¬°®æ¦¡¤Æ¤å¦r¸ê®Æªº¼È¦s°Ï°ì¡C¨ä¤]¥i¥H¼È¦s©MÀx¦s¦b DataObject ªº¤å¦r¤ù¬q¬ÛÃöªº®æ¦¡¡C
  72.     '«Å§i Dim D As New DataObject '¶·¦b¤u¨ã-> ³]©w¤Þ¥Î¶µ¥Ø¥[¤J ·s¼W¤Þ¥Î Microsoft Forms 2.0 Object Library ,±M®× ¥[¤J¤@ªí³æ§Y¥i
  73.     With D
  74.         .SetText S
  75.         .PutInClipboard
  76.         With Sh
  77.              .Cells(.Rows.Count, "b").End(xlUp).Offset(1, -1).Select
  78.             .PasteSpecial Format:="Unicode ¤å¦r"
  79.         End With
  80.     End With
  81. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ ¤p«L«È ©ó 2014-6-10 12:08 ½s¿è

ÁÂÁÂGBKEE¤j¤j¡A¦n¹³¤w¸g¥i¥H¤F¡I
¤Ó§Q®`¡A§Ú¦A¸Õ¸Õ¬Ý¡C
¥N½X¦³«Ü¦h¬Ý¤£À´ªºªF¦è¡A§Æ±æ¥J²Ó¬Ý«á¯à§Ë²M·¡·í¤¤­ì²z¡AÁÂÁ¡C

TOP

¥»©«³Ì«á¥Ñ ¤p«L«È ©ó 2014-6-10 14:19 ½s¿è

¹ï¤£°_¡AÃö©óGBKEE¤j¤À¨ÉªºCODING¡A§Ú¦³¨ÇºÃ°Ý¡G

1) "FM20.DLL" ¬OMicrosoft Forms 2.0 Object Library ¡A¦ý¦pªG§Ú·Q¤F¸Ñ¥¦ªº¥\¯à©M¨Ï¥Î¤è«K¡AÀ³¸Ó¦p¦ó¤J¤â¡H

2) "grid-table-pubSrch_center"¡B"next_grid-table-pubSrch"¡A³o¨Çkeyword¬O¦p¦ó§ä¥X¨Ó¡H§Ú¦b­ì©lÀɧ䤣¨ì......

3) §Ú¹ïCreateObject("InternetExplorer.Application")ªº¾Þ§@¤£¤F¸Ñ¡A¦³¨S¦³¬ÛÃö¸ê®Æ¥i¥H°Ñ¦Ò¡H

ÁÂÁ¤j®a
ÁÂÁ¡C

TOP

¦^´_ 5# ¤p«L«È
1) "FM20.DLL" ¬OMicrosoft Forms 2.0 Object Library ¡A¦ý¦pªG§Ú·Q¤F¸Ñ¥¦ªº¥\¯à©M¨Ï¥Î¤è«K¡AÀ³¸Ó¦p¦ó¤J¤â¡H
Private Sub Ep(Sh As Worksheet, S As String)
  Dim D As New DataObject ,VBA¤¤­n¤Þ¥Î¶µ¥ØMicrosoft Forms 2.0 Object Library
¬¡­¶Ã¯ ¤¤¥u­n¦³¨Ï¥Î¨ì MSFormsªº±±¨î¶µ VBA³£·|¦Û°Ê¦a¤Þ¥ÎMicrosoft Forms 2.0 Object Library

2) "grid-table-pubSrch_center"¡B"next_grid-table-pubSrch"¡A³o¨Çkeyword¬O¦p¦ó§ä¥X¨Ó¡H§Ú¦b­ì©lÀɧ䤣¨ì......
½Ð°Ñ¦Ò Private Sub ºô­¶ªº¤¸¯À()

3) §Ú¹ïCreateObject("InternetExplorer.Application")ªº¾Þ§@¤£¤F¸Ñ¡A¦³¨S¦³¬ÛÃö¸ê®Æ¥i¥H°Ñ¦Ò¡H
Google CreateObject("InternetExplorer.Application") ¬Ý¬Ý
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  ¤p«L«È
1) "FM20.DLL" ¬OMicrosoft Forms 2.0 Object Library ¡A¦ý¦pªG§Ú·Q¤F¸Ñ¥¦ªº¥\¯à©M¨Ï¥Î¤è«K ...
GBKEE µoªí©ó 2014-6-12 08:33




¦³ÂI§Ë¤£²M·¡¡Aª©¤j¥i§_²¤¶¡uMicrosoft Forms 2.0 Object Library¡v¦b¦¹µ{¦¡ªº§@¥Î¡H
¬O§âºô­¶¤Wªº¸ê®Æ·í¦¨FORM¨ÓŪ¨ú¶Ü¡H

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-6-14 20:44 ½s¿è

¦^´_ 7# ¤p«L«È
§âºô­¶¤Wªº¸ê®Æ·í¦¨FORM¨ÓŪ¨ú


Ex.rar (2.21 KB)


ÀɮפU¸ü«á,¦p¹Ï¥Ü¶×¤J ªí³æ



·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  ¤p«L«È





ÀɮפU¸ü«á,¦p¹Ï¥Ü¶×¤J ªí³æ
GBKEE µoªí©ó 2014-6-14 16:47



    ¸g¹L¼Æ¤Ñªº¬ã¨s¡A§Ú¶}©l¦³ÂI¤F¸Ñ
¦ý§Ú¹ï¤j¤j¤§«e´£¨Ñªº¥N½X¤´µM¤£¤Ó²M´·¡A¥i¥H½Ð§Aµy§@¸ÑĶ¶Ü¡H
  1.         Do
  2.             Set A = .Document.getElementsByTAGName("TD")
  3.         Loop Until Not A Is Nothing And A.Length = 84
½Æ»s¥N½X
§Ú¬d¨ìgetElementsByTAGName("TD")¬O«ü¡uªð¦^±a¦³«ü©w¼Ðñ¦Wªº¹ï¶H¶°¦X¡v
§Y¬OA = ¡uªð¦^±a¦³TD¼Ðñ¦Wªº¹ï¶H¶°¦X¡v
A.Length ¬O«ü³o­Ó¶°¦XªºÁ`¼Æ
¦ý§Ú°Ñ¦Ò ¡uPrivate Sub ºô­¶ªº¤¸¯À()¡v
§Úµo²{¥u¦³54­ÓTD ¡A¬°¤°»ò¤j¤j·|¼g84?(A.length¯uªº¬O84¡A¥u¬O§Ú¤£©ú¥Õ¬°¦ó¬O84¡A¦Ó¥B¦p¦ó±oª¾¬O84)

¥t¥~¡A¦pªG§Ú­Ì¬O§Æ±æ±q"TD"¶°¦X¤¤§ä¥X A(i).ID = "grid-table-pubSrch_center"
  1.         Do
  2.             Set A = .Document.getElementsByTAGName("TD")
  3.         Loop Until Not A Is Nothing And A.Length = 84
½Æ»s¥N½X
¥i¤£¥i¥H¼g¦¨
  1. Set A = .Document.getElementsByTAGName("TD")
½Æ»s¥N½X
¦]¬°§Ú¤£¤Ó©ú¥Õ³o­ÓLOOPªº§@¥Î
ÁÂÁ¤j¤j«ü±Ð

TOP

¦^´_ 9# ¤p«L«È
  1. Do
  2.             Set A = .Document.getElementsByTAGName("TD")
  3.         Loop Until Not A Is Nothing And A.Length = 84
  4.         MsgBox A.Length  '¥i¬Ý¬Ý­Ë©³¬O¦h¤Ö
½Æ»s¥N½X
Set A = .Document.getElementsByTAGName("TD")
¤£©ñ¦b Do   Loop °j°é¤]¥i¥H,
¦ý¦]ºô­¶¸ê®Æ¤U¸ü³t«×¤ñµ{¦¡³t«×°õ¦æºC,¾É­P  A Is Nothing
¥u±o¥Î  Do   Loop  ª½¨ì Not A Is Nothing
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ®É®É¦n¤ß´N¬O®É®É¦n¤é¡C
ªð¦^¦Cªí ¤W¤@¥DÃD