GBKEE

¦^´_ 4# jewayy

¤U¸ü (62.16 KB)

2013-11-17 07:16

1. Option Explicit
   
2. Dim IE As Object
   
3. Sub ¥X¤f³ø³æ©ñ¦æ¸ê®Æ¬d¸ß()
   
4.     Dim ¥X¤f³ø³æ¸¹½X As String, n As Object
   
5.         ¥X¤f³ø³æ¸¹½X = InputBox("¥X¤f³ø³æ¸¹½X", "¥X¤f³ø³æ©ñ¦æ¸ê®Æ¬d¸ß", "BE  02XE580024")
   
6.         If ¥X¤f³ø³æ¸¹½X = "" Then Exit Sub
   
7.         With CreateObject("InternetExplorer.Application")
   
8.            .Visible = True
   
9.             .Navigate "http://portal.sw.nat.gov.tw/APGQ/GB315?request_locale=zh_TW&declNo=" & ¥X¤f³ø³æ¸¹½X
   
10.             Do While .Busy = True
    
11.         DoEvents
    
12.         Loop
    
13.         For Each n In .document.getelementsbytagname("INPUT")
    
14.            If n.Value = "¬d¸ß" Then
    
15.                 n.Click                        'ºô­¶«ö¤U ¬d¸ß
    
16.                 Exit For
    
17.            End If
    
18.         Next
    
19.         Application.Wait (Time + TimeValue("0:00:03"))  '¨Ìºô­¶¤U¸ü³t«×½Õ¾ãµ¥«Ý¬í¼Æ
    
20.         Set IE = .document
    
21.         ¬d¸ßµ²ªG
    
22.         .Quit
    
23.     End With
    
24. End Sub
    
25. Private Sub ¬d¸ßµ²ªG()
    
26.     Dim Ar(1 To 7), SH As Worksheet
    
27.     '***** ºô­¶ªº­ì©lÀɮתº¥»¤å
    
28.     '<tbody><tr><td colspan="4" class="resultHeader">¬d¸ßµ²ªG</td></tr>
    
29.     '<td class="resultHeader">®üªÅ¹B§O</td><td class="result" id="transTypeCd">®ü</td>
    
30.     '<td class="resultHeader" width="25%">¥X¤f³ø³æ¸¹½X</td><td width="25%" class="result" id="declNo">BE  02XE580024</td>
    
31.     '<td class="resultHeader" width="25%">³ø³æÃþ§O</td><td width="25%" class="result" id="declType">G5</td>
    
32.     '<td class="resultHeader" width="25%">Á`¥ó¼Æ</td><td width="25%" class="result" id="totPackQty">32</td>
    
33.     '<td class="resultHeader" width="25%">¥Øªº°ê®a¥N½X</td><td width="25%" class="result" id="destCd">VNCLI</td>
    
34.     '<td class="resultHeader" width="25%">Á`¥ó¼Æ³æ¦ì</td><td width="25%" class="result" id="totPackQtyUnit">PLT</td>
    
35.     '<td class="resultHeader" width="25%">³ø³æ©ñ¦æµù°O</td><td width="25%" class="result" id="examRelNote">Y</td>
    
36.     '<td class="resultHeader" width="25%">Á`¤ò­«</td><td width="25%" class="result" id="totGrossWeight">49376</td>
    
37.     '<td class="resultHeader" width="25%">©ñ¦æ¤é´Á</td><td width="25%" class="result" id="relDate">102/09/17</td>
    
38.     '<td class="resultHeader" width="25%">²î²í¦WºÙ(®ü)/¯è¾÷¦WºÙ(ªÅ)</td><td width="25%" class="result" id="vslName">UNI-PROSPER                        </td>
    
39.     '<td class="resultHeader" width="25%">¾P¿µµù°O</td><td width="25%" class="result" id="marketMftNote">Y</td>
    
40.     '<td class="resultHeader" width="25%">²î²í¯è¦¸(®ü)/¯è¾÷¯Z¦¸(ªÅ)</td><td width="25%" class="result" id="voyageFlightNo">1084-186S</td>
    
41.     '<td class="resultHeader" width="25%">²î²í©I¸¹(®ü)</td><td width="25%" class="result" id="vslSign">BKHC</td>
    
42.     '<input id="statusMsg" type="text" class="msgText uppercase" readonly="readonly" style="width: 775px; color: red;">
    
43.      Set SH = ActiveSheet                                 '«ü©wÅã¥Ü¸ê®Æªº¤u§@ªí 'ActiveSheet->§@¥Î¤¤ªº¤u§@ªí
    
44.     If InStr(IE.getElementById("statusMsg").Value, "[°õ¦æ¦¨¥\]") = 0 Then
    
45.         SH.[b2].Resize(7, 1) = Application.WorksheetFunction.Transpose(Ar)
    
46.         SH.[d2].Resize(7, 1) = Application.WorksheetFunction.Transpose(Ar)
    
47.         Exit Sub
    
48.     End If
    
49.     Ar(1) = IE.getElementById("transTypeCd").innertext      '®üªÅ¹B§O
    
50.     Ar(2) = IE.getElementById("declNo").Value               '¥X¤f³ø³æ¸¹½X
    
51.     Ar(3) = IE.getElementById("totPackQty").innertext       'Á`¥ó¼Æ
    
52.     Ar(4) = IE.getElementById("totPackQtyUnit").innertext   'Á`¥ó¼Æ³æ¦ì
    
53.     Ar(5) = IE.getElementById("totGrossWeight").innertext   'Á`¤ò­«
    
54.     Ar(6) = IE.getElementById("vslName").innertext          '²î²í¦WºÙ (®ü) / ¯è¾÷¦WºÙ(ªÅ)
    
55.     Ar(7) = IE.getElementById("voyageFlightNo").innertext   '²î²í¯è¦¸ (®ü) / ¯è¾÷¯Z¦¸(ªÅ)
    
56.     SH.[b2].Resize(7, 1) = Application.WorksheetFunction.Transpose(Ar)
    
57.     Ar(1) = ""
    
58.     Ar(2) = IE.getElementById("declType").innertext         '³ø³æÃþ§O
    
59.     Ar(3) = IE.getElementById("destCd").innertext           '¥Øªº°ê®a¥N½X
    
60.     Ar(4) = IE.getElementById("examRelNote").innertext      '³ø³æ©ñ¦æµù°O
    
61.     Ar(5) = IE.getElementById("totGrossWeight").innertext   '©ñ¦æ¤é´Á
    
62.     Ar(6) = IE.getElementById("marketMftNote").innertext    '¾P¿µµù°O
    
63.     Ar(7) = IE.getElementById("vslSign").innertext          '²î²í©I¸¹ (®ü)
    
64.     SH.[d2].Resize(7, 1) = Application.WorksheetFunction.Transpose(Ar)
    
65.     SH.Columns.AutoFit
    
66. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 9# c_c_lai
EXCEL 2003, IE 8  ©Î³\¬Oª©¥»¤£¦P  declNo=>tagname("INPUT") ­n¥Î VALUE
IE8   Ar(2) = IE.getElementById("declNo").innertext      '¥X¤f³ø³æ¸¹½X  ·|¶Ç¦^ ªÅ¦r¦ê
­n³s¦P¨C­Ó¼ÐÃD¶Ü §Ú@*#@*#@*#

°Ñ¦Ò7# joey0415  µ¹ªººô§} http://portal.sw.nat.gov.tw/APGQ/GB315!query?declNo=BE++02XE580024
¥i±Ë¥h8#ªºµ{¦¡½X ,  ¦p¦b 8# ¹Ï¤ùªº¤u§@ªí,³oµ{¦¡½X´N²«K¤F.

1. Option Base 1
   
2. Sub Ex()
   
3.     Dim Ar, AA(), ¥X¤f³ø³æ¸¹½X As String, Sh As Worksheet
   
4.     ¥X¤f³ø³æ¸¹½X = InputBox("¥X¤f³ø³æ¸¹½X", "¥X¤f³ø³æ©ñ¦æ¸ê®Æ¬d¸ß", "BE  02XE580024")
   
5.     If ¥X¤f³ø³æ¸¹½X = "" Then Exit Sub
   
6.     Set Sh = ActiveSheet                                 '«ü©wÅã¥Ü¸ê®Æªº¤u§@ªí 'ActiveSheet->§@¥Î¤¤ªº¤u§@ªí
   
7.     With CreateObject("Microsoft.XMLHTTP")
   
8.        .Open "GET", "http://portal.sw.nat.gov.tw/APGQ/GB315!query?declNo=" & ¥X¤f³ø³æ¸¹½X, False
   
9.         .send
   
10.         Ar = Split(Replace(.responsetext, """", ""), ",")
    
11.         AA = Array(1, 8, 4, 7, 2, 14, 11)           'Aø󪺼ÐÃD¤º®e Ar¤¤°}¦C¹ïÀ³¤§¯Á¤Þ­È
    
12.         On Error GoTo Er                            '¥X¤f³ø³æ¸¹½X ¤£¥¿½T·|¦³¿ù»~:
    
13.         For i = 1 To UBound(AA)
    
14.             Sh.Cells(1 + i, "B") = Split(Ar(AA(i)), ":")(1)  'Bøó
    
15.        Next
    
16.        AA = Array(5, 3, 10, 6, 12, 9)                 'Cø󪺼ÐÃD¤º®e Ar¤¤°}¦C¹ïÀ³¤§¯Á¤Þ­È
    
17.        For i = 1 To UBound(AA)
    
18.             Sh.Cells(2 + i, "D") = Split(Ar(AA(i)), ":")(1)  'Døó
    
19.             If AA(i) = 6 Then Cells(2 + i, "D") = Replace(Cells(2 + i, "D"), "\/", "/")
    
20.        Next
    
21.     End With
    
22.     Exit Sub
    
23. Er:
    
24.     Sh.[b2].Resize(7, 1) = ""
    
25.     Sh.[d2].Resize(7, 1) = ""
    
26. End Sub
    
27. End Sub

½Æ»s¥N½X

GBKEE

¬O¬°¤F¤è«K«ü©wAR°}¦C¤¤«ü©wªº¤¸¯À¡A¦b©ñ¶i·Q­nªºCELLS¤¤¶Ü?
¦pªG­n§å¦¸§ä100­È©ñ¶i¥h¶]¦^°é¡A¤¤¶¡¦³¿ùªº¸Ü
­n©ñ on error resume next¶Ü¡H

¦^´_ 13# joey0415
¨S¿ù¬O­n©ñ¶i·Q­nªºCELLS.

¦pªG¹w´Á·|¦³¿ù»~ªºµ{¦¡½X¤§«e ¼g¤W on error resume next ,µ{¦¡´N¤@ª½°õ¦æ¤U¥h,¦p¯u¦³¿ù»~§A¬O·|¤£ª¾¹Dªº
¦^´_ 11# jewayy

¤U¸ü (131.93 KB)

2013-11-17 15:49

1. Option Explicit
   
2. Option Base 1
   
3. Sub ¤f³ø³æ³qÃö¬yµ{¬d¸ß()
   
4.     Dim ¥X¤f³ø³æ¸¹½X As String, Rng As Range, AR, S As Variant, E As Variant, i As Integer, W As String, II As Integer
   
5.     Dim Sh As Worksheet
   
6.     ¥X¤f³ø³æ¸¹½X = InputBox("¥X¤f³ø³æ¸¹½X", "¥X¤f³ø³æ©ñ¦æ¸ê®Æ¬d¸ß", "BE  02XE580024")
   
7.     If ¥X¤f³ø³æ¸¹½X = "" Then Exit Sub
   
8.     Set Sh = ActiveSheet
   
9.     '«ü©wÅã¥Ü¸ê®Æªº¤u§@ªí 'ActiveSheet->§@¥Î¤¤ªº¤u§@ªí
   
10.     Set Rng = Sh.Range("b2:B9, D3:D7, D9, B11, D11")
    
11.     '**AR¤º®e: °Ñ·Ó **** ¥X¤f³ø³æ³qÃö¬yµ{¬d¸ß(GB309) ºô­¶ªº­ì©lÀÉ*****
    
12.     AR = Array("transTypeCd", "vslRegNo", "declNo", "brokerBoxNoName", "mawb", "hawb", "declType", "relCondSubCd" _
    
13.     , "soNo", "custCd", "carrierAgencyCd", "arrangeNo", "examMethod", "debitMark", "firstSendDate", "lastSendDate")
    
14.     With CreateObject("Microsoft.XMLHTTP")
    
15.        .Open "GET", "http://portal.sw.nat.gov.tw/APGQ/GB309!query?&choice=D&declNo=" & ¥X¤f³ø³æ¸¹½X, False
    
16.         .send
    
17.         S = Replace(.responsetext, """", "")
    
18.     End With
    
19.     i = 1
    
20.     '*********** ¬d¸ßµ²ªG *****
    
21.     For Each E In Rng
    
22.         E = ""
    
23.         If InStr(S, AR(i)) Then
    
24.             W = Mid(Split(Mid(S, InStr(S, AR(i)) + Len(AR(i))), ",")(0), 2)
    
25.             E = IIf(InStr(LCase(W), "null"), "", W)
    
26.         End If
    
27.         i = i + 1
    
28.     Next
    
29.     '***************³qÃö¬yµ{**********
    
30.     AR = Split(S, "data:[")(1)                          'ÄdºI "data:[" «áªº¦r¦ê
    
31.     AR = Split(AR, "]")(0)                              'ÄdºI "[" «eªº¦r¦ê
    
32.     AR = Replace(Mid(AR, 2, Len(AR) - 2), "null", " ")  '´À´« "null" ¬° " "
    
33.     AR = Replace(AR, "T", " ")                          '´À´«  "T"   ¬° " "
    
34.     AR = Split(AR, "},{")                               '¥H "},{" ¤À³Î¬°°}¦C
    
35.     S = Array(4, 2, 0, 1, 3)
    
36.     Sh.Range("A13").CurrentRegion.Offset(1) = ""
    
37.     For i = 0 To UBound(AR)
    
38.         For II = 0 To UBound(S) - 1
    
39.             E = Split(AR(i), ",")(S(II + 1))            '¥H S(II + 1)ªº­È ¨ú±o Split(AR(i), ",")°}¦Cªº¯Á¤Þ­È
    
40.             Sh.Cells(i + 14, "A").Offset(, II) = Mid(E, InStr(E, ":") + 1)
    
41.         Next
    
42.     Next
    
43. End Sub
    
44. '¥X¤f³ø³æ³qÃö¬yµ{¬d¸ß(GB309) ºô­¶ªº­ì©lÀÉ
    
45. '<td class="resultHeader">®üªÅ¹B§O</td><td id="transTypeCd" class="result">
    
46. '<td class="resultHeader" width="25%">®üÃö³qÃö¸¹½X</td><td width="25%" id="vslRegNo" class="result">
    
47. '<td class="resultHeader" width="25%">¸Ë³f³æ½s¸¹</td><td width="25%" id="soNo" class="result">
    
48. '<td class="resultHeader">³ø³æ¸¹½X</td><td id="declNo" class="result">
    
49. '<td class="resultHeader">Ãö°Ï¥N½X</td><td id="custCd" class="result">
    
50. '<td class="resultHeader">³øÃö·~ªÌ½c¸¹</td><td id="brokerBoxNoName" class="result">
    
51. '<td class="resultHeader">¹B¿é·~ªÌ/¥N²z¦æ¥N½X</td><td id="carrierAgencyCd" class="result">
    
52. '<td class="resultHeader">°U¹B³æ¥D¸¹</td><td id="mawb" class="result">
    
53. '<td class="resultHeader">²z³æ¸¹½X</td><td id="arrangeNo" class="result">
    
54. '<td class="resultHeader">°U¹B³æ¤À¸¹</td><td id="hawb" class="result">
    
55. '<td class="resultHeader">¥Ó½Ð¼fÅç¤è¦¡</td><td id="examMethod" class="result">
    
56. '<td class="resultHeader">³ø³æÃþ§O</td><td id="declType" class="result">
    
57. '<td class="resultHeader">©ñ¦æªþ±a±ø¥ó</td><td id="relCondSubCd" class="result">
    
58. '<td class="resultHeader">¬O§_¬°¨R°hµ|e¤Æ³ø³æ</td><td id="debitMark" class="result">
    
59. '<tr><td colspan="4" class="resultHeader">¶Ç°eÁ`ÃB¥æ¬d¦Ü°]µ|¤¤¤ßªº¤é´Á</td>
    
60. '<td class="resultHeader">²Ä¤@¦¸¶Ç°e¤é´Á</td><td id="firstSendDate" class="result">
    
61. '<td class="resultHeader">³Ì«á¤@¦¸¶Ç°e¤é´Á</td><td id="lastSendDate" class="result">

½Æ»s¥N½X

GBKEE

¦^´_ 18# stillfish00

1. .document.body.innerHTML = .document.getElementById("queryResult").outerHTML

½Æ»s¥N½X

³o©Û¨ü±Ð¤F

GBKEE

¦^´_ 20# joey0415
.document(¤å¥ó).body(¥»Åé).innerHTML(¥N½X,¤å¦r) = .document.getElementById("queryResult").outerHTML(¿é¥Xªº:¥N½X,¤å¦r)
¥i¦b³o¦æµ{¦¡½X³]¤U¤¤Â_ÂI,¬Ý¤@¤Uºô­¶«e«áªºÅܤÆ

GBKEE

¦^´_ 22# c_c_lai

1. For Each x In .document.getElementsByTagName("input")
   
2.              If x.Name = "declNo" Then x.Value = sID
   
3.             If x.Value = "¬d¸ß" Then x.Click
   
4.         Next

½Æ»s¥N½X

³o¦æ¤]­n­×§ï =0

1. If InStr(sStatus, "[°õ¦æ¦¨¥\]") = 0 Then .Quit: MsgBox sStatus: Exit Sub

½Æ»s¥N½X

GBKEE

¦^´_ 25# stillfish00
·PÁ¸ɨ¬»¡©ú
¦^´_ 24# c_c_lai
¦pªG³o¨Ç ¥X¤f³ø³æ©ñ¦æ³æ¬d¸ßªººô­¶¬ÛÃþ¦üªº¥i¦p¦¹ (§ï¤@¤Ustillfish00ªºµ{¦¡½X)

1. Option Explicit
   
2. Sub ¥X¤f¬d¸ß()
   
3.     Dim sID As String, sStatus As String, URL As String
   
4.     Dim x
   
5.     URL = InputBox("1:¥X¤f³ø³æ©ñ¦æ¸ê®Æ¬d¸ß(²£ÃÒ±M¥Î)(GB315)" & vbLf & "2:¥X¤f³ø³æ³qÃö¬yµ{¬d¸ß(GB309)", "¥X¤f¸ê®Æ¬d¸ß", 1)
   
6.     If URL = "" Or (URL <> "1" And URL <> "2") Then Exit Sub
   
7.     URL = IIf(URL = "1", "GB315", "GB309")
   
8.     sID = InputBox("¥X¤f³ø³æ¸¹½X", "¥X¤f³ø³æ©ñ¦æ¸ê®Æ" & URL & "¬d¸ß", "BE  02XE580024")
   
9.     If sID = "" Then Exit Sub
   
10.     URL = "http://portal.sw.nat.gov.tw/APGQ/" & URL & "?&declNo=" & sID   
    
11.     With CreateObject("InternetExplorer.Application")
    
12.         .Visible = True     '  ¬O§_Åã¥Ü IE
    
13.         .Navigate URL
    
14.         Do While .readyState <> 4
    
15.             DoEvents
    
16.         Loop
    
17.         For Each x In .document.getElementsByTagName("input")
    
18.             If x.Value = "¬d¸ß" Then x.Click: Exit For
    
19.         Next
    
20.         Do While .document.getElementById("statusMsg").Value = ""
    
21.             DoEvents
    
22.         Loop
    
23.         sStatus = .document.getElementById("statusMsg").Value
    
24.         If InStr(sStatus, "[°õ¦æ¦¨¥\]") <= 0 Then .Quit: MsgBox sStatus: Exit Sub
    
25.                               
    
26.         .document.body.innerHTML = .document.getElementById("queryResult").outerHTML
    
27.         .execwb 17, 2       '  Select All
    
28.         .execwb 12, 2       '  Copy selection
    
29.         With ActiveSheet
    
30.             .Cells.Clear
    
31.             .[A1].Select
    
32.             .PasteSpecial Format:="HTML"
    
33.         End With
    
34.         .Quit
    
35.     End With
    
36. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 28# joey0415
¦³¨Ç¦³id»Ptagname ¦³¨Ç¥u¦³id ¡A¦ý¤@©w·|¦³tagname
ºK¨úºô­¶­ì¨ÏÀɤ@¬q¤º®e <input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="" />
getElementsBytagname("input")     '·|¬O­ì¨ÏÀÉ©Ò¦³<input type=???  .....>  ªº¶°¦X
getElementsByName("__VIEWSTATE")  '°ß¤@ªº Name
getElementById("__VIEWSTATE")     '°ß¤@ªº Id

1. Option Explicit
   
2. Sub TEST11()
   
3.     Dim x
   
4.     With CreateObject("InternetExplorer.Application")
   
5.         .Visible = True '¬O§_Åã¥ÜIE
   
6.         .Navigate "http://www.cnyes.com/twstock/Institutional/1101.htm"
   
7.         Do While .readyState <> 4: DoEvents: Loop
   
8.         Set x = .document.getElementById("a_itrust")
   
9.         x.Click ' Âà¨ì ºô­¶: 'http://www.cnyes.com/twstock/itrust/1101.htm            '
   
10.         Do While .readyState <> 4: DoEvents: Loop
    
11.         ' .document.body.innerHTML = .document.getElementById("tabvl").outerHTML  'ºô­¶¤å¥ó¤¸¯À¨S³o Id="tabvl"
    
12.         .document.body.innerHTML = .document.getElementsBytagname("table")(1).outerHTML  'tagname¦¨­û±q 0 ¶}©l
    
13.         .execwb 17, 2 'Select All
    
14.         .execwb 12, 2 'Copy selection
    
15.         ActiveSheet.[A1].Select
    
16.         ActiveSheet.PasteSpecial Format:="HTML" ', NoHTMLFormatting:=True
    
17.         .Quit
    
18.     End With
    
19. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 32# joey0415
IQY ¬d¸ßÀɪº¤º®e(°ÆÀɦW¬° IQY ,¦s©ó°O¨Æ¥»,¤p§@®a.)

1. WEB
   
2. 1
   
3. http://www.cnyes.com/twstock/itrust/1101.htm
   
4. 
   
5. '§ë«H¶i¥Xºô­¶ 'Selection => tableªº¯Á¤Þ­È ,IQY¤å¥ó¥Ñ1¶}©lºâ°_, VBAµ{¦¡ ¥Ñ0 ¶}©lºâ°_
   
6. Selection=2        
   
7. 
   
8. Formatting=None
   
9. PreFormattedTextToColumns=True
   
10. ConsecutiveDelimitersAsOne=True
    
11. SingleBlockTextImport=False
    
12. DisableDateRecognition=False
    
13. DisableRedirections=False

½Æ»s¥N½X

GBKEE

¦^´_ 34# joey0415
§Ú§â¥¦©ñµÛ¨S¦³[­«·s¾ã²z¦¨­ì¨Óªºµe­±]
­Y­n¥Î¤W­±ªº¤èªk§ì¨ú®É¡A³Ì«á¤£­nquit,¤£quit­n¦ó§@¥Î.