- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-6-7 18:23 ½s¿è
¦^´_ 2# ¤p«L«È
¸Õ¸Õ¬Ý- Option Explicit
- Const FormDLL = "FM20.DLL"
- Sub IE¤U¤@¶()
- Dim URL As String, A As Object, Table As Object, i As Integer, pubSrch As Object, Pages As Integer
- Dim Sh As Worksheet, B As String
- Set_FormDLL
- Set Sh = ActiveSheet
- Sh.Cells.Clear
- URL = "https://eservices.customs.gov.hk/MSOS/wsrh/001s1?searchBy=A"
- With CreateObject("InternetExplorer.Application")
- .Visible = True ' ¬O§_Åã¥Ü IE
- .Navigate URL
- Do While .ReadyState <> 4 Or .Busy
- DoEvents
- Loop
- Set A = .Document.getElementsByTAGName("A")
- For i = 0 To A.Length - 1
- If A(i).innertext = "ALL" Then
- A(i).Click
- Exit For
- End If
- Next
- Do While .ReadyState <> 4 Or .Busy
- DoEvents
- Loop
- Do
- Set A = .Document.getElementsByTAGName("TD")
- Loop Until Not A Is Nothing And A.Length = 84
- For i = 0 To A.Length - 1
- If A(i).ID = "grid-table-pubSrch_center" Then
- Pages = Val(Replace(A(i).innertext, "Page of ", "")) 'Á`¶¼Æ
- End If
- If A(i).ID = "next_grid-table-pubSrch" Then Set pubSrch = A(i) '¤U¤@¶«öÁä
- Next
- Set Table = .Document.getElementsByTAGName("table")
- For i = 1 To Pages
- If i >= 2 Then
- pubSrch.Click
- Do While .ReadyState <> 4 Or .Busy: Loop
- Set Table = .Document.getElementsByTAGName("table")
- Do: Loop Until B <> Table(6).outerHTML
- End If
- Ep Sh, Table(6).outerHTML
- B = Table(6).outerHTML
- Next
- .Quit
- End With
- With Sh
- .Cells.WrapText = False
- Intersect(Range("b:b").SpecialCells(xlCellTypeBlanks).EntireRow, .Range("c:c").SpecialCells(xlCellTypeBlanks).EntireRow).Delete
- .Cells.EntireRow.AutoFit
- .Range("a1").Activate
- End With
- Remove_FormDLL
- End Sub
- Private Sub Set_FormDLL() '·s¼W¤Þ¥Î Microsoft Forms 2.0 Object Library
- On Error Resume Next
- ThisWorkbook.VBProject.References.AddFromFile "C:\windows\system32\" & FormDLL
- ' On Error GoTo 0
- End Sub
- Private Sub Remove_FormDLL() '§R°£¤Þ¥Î Microsoft Forms 2.0 Object Library
- Dim D As Object
- For Each D In ThisWorkbook.VBProject.References
- If UCase(D.fullpath) Like "*" & FormDLL Then
- ThisWorkbook.VBProject.References.Remove D
- End If
- Next
- End Sub
- Private Sub Ep(Sh As Worksheet, S As String)
- Dim D As New DataObject, E As Shape, FormDLL As String ', Rng As Range
- 'DataObject ª«¥ó ¦b¶i¦æÂà´«°Ê§@®É¡A°µ¬°®æ¦¡¤Æ¤å¦r¸ê®Æªº¼È¦s°Ï°ì¡C¨ä¤]¥i¥H¼È¦s©MÀx¦s¦b DataObject ªº¤å¦r¤ù¬q¬ÛÃöªº®æ¦¡¡C
- '«Å§i Dim D As New DataObject '¶·¦b¤u¨ã-> ³]©w¤Þ¥Î¶µ¥Ø¥[¤J ·s¼W¤Þ¥Î Microsoft Forms 2.0 Object Library ,±M®× ¥[¤J¤@ªí³æ§Y¥i
- With D
- .SetText S
- .PutInClipboard
- With Sh
- .Cells(.Rows.Count, "b").End(xlUp).Offset(1, -1).Select
- .PasteSpecial Format:="Unicode ¤å¦r"
- End With
- End With
- End Sub
½Æ»s¥N½X |
|