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

[µo°Ý] ½Ð±Ðºô­¶®»¨úªº°ÝÃD¡H CreateObject("msxml2.xmlhttp") ªº°ÝÃD?

¥»©«³Ì«á¥Ñ wufonna ©ó 2022-1-6 19:04 ½s¿è

¦^´_ 1# wufonna

ª÷¿Ä«OÀI·~ §O ªºªí®æ®æ¦¡¤£¤@¼Ë §ì¤£¨ì¥¿½Tªº¸ê®Æ
§Ú¬O¨ú
ROE=·l¯qªí-µ|«e²b§Q(67)/¸ê²£­t¶ÅªíªÑ-ªFÅv¯qÁ`ÃB(95)
.Cells(i, 9).Value = ¤u§@ªí4.Cells(67, 2).Value / ¤u§@ªí5.Cells(95, 2).Value 'ROE%
¨D§ï¥¿¤èªk ÁÂÁÂ

ªþÀɬO§ì¨ìªº¸ê®Æ

GetDividend-2022-1-6.rar (230.62 KB)

TOP

¦^´_ 1# wufonna
  1. Sub test()
  2. Debug.Print "test"


  3. With Sheet1
  4.   Debug.Print .Columns("A").Find("µ|«e²b§Q").Row
  5.   Debug.Print .Range("b" & Sheet1.Columns("A").Find("µ|«e²b§Q").Row).Value
  6.   
  7.   Debug.Print .Rows("67").Find(-336).Column
  8.   Debug.Print .Rows("67").Find("-336").Column
  9.   Debug.Print .Rows("67").Find("52").Column
  10.             
  11.   Debug.Print .Rows("67").Find(0).Column '§ä¨ì101¤ºªº0

  12. End With

  13. End Sub
½Æ»s¥N½X
¬ö¿ý¤@¤U

testest.rar (17.45 KB)

TOP

  1. '          .Cells(i, 9).Value = ¤u§@ªí4.Cells(67, 2).Value / ¤u§@ªí5.Cells(95, 2).Value 'ROE%
  2.            .Cells(i, 9).Value = ¤u§@ªí4.Range("b" & ¤u§@ªí4.Columns("A").Find("µ|«e²b§Q").Row).Value / ¤u§@ªí5.Range("b" & ¤u§@ªí5.Columns("A").Find("ªÑªFÅv¯qÁ`ÃB").Row).Value 'ROE% ¤£©T©w¦ì¤l
½Æ»s¥N½X
¦^´_ 1# wufonna

­×¸Ó¤ù¬q

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2022-1-11 17:12 ½s¿è

¦^´_ 7# wufonna
­n¤F¸Ñºô­¶½s½X,¤~¯à±o¤ßÀ³¤â
  1. Private Sub GetIncome() '¨ú·l¯qªí(¦~ªí)ºô­¶
  2. Dim Url, HTMLsourcecode, GetXml, TableG, i, j
  3. Set HTMLsourcecode = CreateObject("htmlfile")
  4. Set GetXml = CreateObject("msxml2.xmlhttp")
  5. Url = "http://kgieworld.moneydj.com/z/zc/zcq/zcqa/zcqa_2330.djhtm"
  6. With GetXml
  7. .Open "GET", Url, False
  8. .Send
  9. HTMLsourcecode.body.innerhtml = .Responsetext
  10. Set TableG = HTMLsourcecode.getelementsbyclassname("table-row")
  11. '***ºô­¶ªº½s½X**********************************
  12. '<div class="table-row">   ¬°©Ò­nªº¸ê®Æ ªººô­¶¤¸¯À
  13. '<span class="t2 table-cell">´Á§O</span>
  14. '<span class="t2 table-cell">2020</span>
  15. '<span class="t2 table-cell">2019</span>
  16. '<span class="t2 table-cell">2018</span>
  17. '<span class="t2 table-cell">2017</span>
  18. '<span class="t2 table-cell">2016</span>
  19. '<span class="t2 table-cell">2015</span>
  20. '<span class="t2 table-cell">2014</span>
  21. '<span class="t2 table-cell">2013</span>
  22. '</div>************************************
  23. For i = 0 To TableG.Length - 1
  24.     For j = 0 To TableG(i).all.tags("span").Length - 1
  25.        Cells(i + 1, j + 1) = TableG(i).all.tags("span")(j).innertext
  26.     Next j
  27. Next i
  28. End With
  29. Set HTMLsourcecode = Nothing 'ÄÀ©ñ°O¾ÐÅé
  30. Set GetXml = Nothing
  31. End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
½Æ»s¥N½X
ROE=·l¯qªí-µ|«e²b§Q(67)/¸ê²£­t¶ÅªíªÑ-ªFÅv¯qÁ`ÃB(95)
.Cells(i, 9).Value = ¤u§@ªí4.Cells(67, 2).Value / ¤u§@ªí5.Cells(95, 2).Value 'ROE%
#11 ªº°ÝÃD³o¤½¦¡À³¥i¸Ñ¨M
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2022-1-11 15:54 ½s¿è

¦^´_ 11# wufonna
Pchome ªº½u«¬¨«¶Õ ºô­¶  VBA °Ñ¦Ò ¬Ý¬Ý
  1. Option Explicit
  2. Dim Rng As Range, ¸ê°T_Msg As Boolean, ie As New InternetExplorer
  3. Sub AllFile()    '­«·s§ó·s©Ò¦³¸ê®Æ
  4.     Dim i As Integer
  5.     With Sheets("ªÑ²¼")
  6.         .UsedRange.CurrentRegion.Offset(0, 1).Clear
  7.         ¸ê°T_Msg = True
  8.         For i = 1 To .Range("A" & .Rows.Count).End(xlUp).Row
  9.             µ{¦¡°õ¦æ Cells(i, 1)
  10.         Next
  11.         ¸ê°T_Msg = False
  12.         If ¸ê°T_Msg = False Then ie.Quit
  13.     End With
  14. End Sub
  15. Sub ex()
  16.     µ{¦¡°õ¦æ [B1]
  17. End Sub
  18. Private Sub µ{¦¡°õ¦æ(xRng As Range)  '³æµ§§ó·s «ü©wAÄ檺¸ê®Æ
  19.     If xRng.Column > 1 Then MsgBox xRng & " ¤£¦b AÄæ": Exit Sub
  20.    ' If ie Is Nothing Then Set ie = New InternetExplorer
  21.     Set Rng = xRng
  22.     ªÑ²¼¸ê°T
  23.     Rng.Select
  24.     If ¸ê°T_Msg = False Then ie.Quit
  25. End Sub
  26. Sub ªÑ²¼¸ê°T()
  27.     Dim E As Object, Ar(), A As Integer, R As Integer, C As Integer
  28.     With Rng
  29.         A = .Parent.UsedRange.Columns.Count - 1
  30.         If ¸ê°T_Msg = False Then
  31.         .Offset(, 1).Resize(, A).Select
  32.         .Offset(, 1).Resize(, A).Clear
  33.         End If
  34.         If .Row = 1 Then .Cells(1, 2) = "ªÑ²¼¦WºÙ ": .Cells(1, 3) = "ªÑ²¼»ù®æ "
  35.     End With
  36. On Error GoTo Beerr
  37. Be:
  38.     With ie
  39.     DoEvents
  40.         .Navigate "https://pchome.megatime.com.tw/stock/sid" & Rng & ".html" '
  41.        '   .Visible = True
  42.          Do While .Busy Or .readyState <> 4: DoEvents: Loop
  43.          Do: DoEvents
  44.             If InStr(.Document.body.innertext, "¬dµL") Then  '¬dµL ªÑ²¼¥N¸¹
  45.                 Rng.Offset(, 1) = "¬dµL " & Rng: Exit Sub
  46.                 ie.Quit
  47.             End If
  48.          Loop Until Not InStr(.Document.body.innertext, "¬dµL")
  49.          If Rng.Row > 1 Then
  50.             Do: DoEvents
  51.                 Set E = .Document.querySelector("em[class='corp-name']")  '«ü©w³o¤¸¯À  <em>  Åª¨ú   (ªÑ²¼¦WºÙ, ¥N¸¹)
  52.                    '<em class="corp-name">¥x¡@ªd<span class="stock-code">&nbsp;&nbsp;(1101)</span> </em>
  53.             Loop Until TypeName(E) = "HTMLPhraseElement"
  54.             If Trim(E.ALL.TAGS("SPAN")(0).innertext) <> "(" & Rng & ")" Then
  55.                 Rng.Offset(, 1) = "¬dµL " & Rng: Exit Sub
  56.             Else
  57.                 Rng.Cells(, 2) = Trim(Split(E.innertext, "(")(0))  'Split ¨ç¼Æ  ¶Ç¦^¤@­Ó³¯¦C¯Á¤Þ±q¹s¶}©lªº¤@ºû°}¦C , ¥¦¥]§t«ü©w¼Æ¥Øªº¤l¦r¦ê
  58.             End If
  59.             Set E = .Document.getelementbyid("stock_info_data_a").ALL.TAGS("SPAN")(0)    'ªÑ²¼»ù®æ    id="stock_info_data_a"
  60.              '<div class="price s-down fadein_black" id="stock_info_data_a">
  61.             '<span class="data_close s-down">18.95</span> **TAGS("SPAN")(0)   's-down ªÑ»ù¤U­°
  62.             '<span class="data_diff s-down">¡¿-0.10</span> **TAGS("SPAN")(1)
  63.             '<span class="data_diff s-down">-0.52%</span>  **TAGS("SPAN")(2)
  64.            '<span class="data_total">18.79<em>»õ</em></span></div>  **TAGS("SPAN")(3)
  65.             Rng.Cells(, 3) = E.innertext
  66.         End If
  67.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  68.         Do: DoEvents
  69.             Set E = .Document.ALL.TAGS("table")(1)
  70.         Loop Until TypeName(E) = "HTMLTable"
  71.          If Rng.Row > 1 Then Ar = Array(3, 5, 7, 9) Else Ar = Array(2, 4, 6, 8)
  72.                     'Rng.Row > 1 >>±q²Ä2¦C¶}©l¤U¸ü¸ê®Æ
  73.                      'Array(2, 4, 6, 8) ¬°¼ÐÃDªºRow  **Ar = Array(3, 5, 7, 9 )¬°¸ê®ÆªºRow
  74.         A = 4  '³]©w¸ê®ÆÄæ¦ìªº°_©lÄæ¦ì,«e¦³ 1ªÑ²¼¥N½XÄæ,2ªÑ²¼¦WºÙÄæ,3ªÑ²¼»ù®æÄæ
  75.        For R = 0 To UBound(Ar) - 1
  76.            For C = 0 To E.Rows(Ar(R)).Cells.Length - 1
  77.             Rng.Cells(, A) = E.Rows(Ar(R)).Cells(C).innertext
  78.             A = A + 1  '¸ê®ÆÄæ¦ì+1
  79.            Next
  80.        Next
  81.     End With
  82. Exit Sub
  83. Beerr:
  84. Set ie = New InternetExplorer
  85. GoTo Be
  86. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 14# GBKEE


    ÁÂÁ ª©¥D  ±q ¤¤¾Ç¨ì«Ü¦h¡C

TOP

¦^´_ 15# GBKEE


    ÁÂÁ 娰¥D µ{¦¡ ¡A ¾Ç²ß¤¤ ¡C
googs.jpg

TOP

¦^´_ 14# GBKEE
  1. Sub GetIncome() '¨ú·l¯qªí(¦~ªí)ºô­¶
  2. Dim Url, HTMLsourcecode, GetXml, TableG, i, j
  3. Set HTMLsourcecode = CreateObject("htmlfile")
  4. Set GetXml = CreateObject("msxml2.xmlhttp")
  5. Url = "http://kgieworld.moneydj.com/z/zc/zcq/zcqa/zcqa_2330.djhtm"
  6. With GetXml
  7. .Open "GET", Url, False
  8. .Send
  9. HTMLsourcecode.body.innerhtml = .Responsetext
  10. Set TableG = HTMLsourcecode.getelementsbyclassname("table-row")
  11. '***ºô­¶ªº½s½X**********************************
  12. '<div class="table-row">   ¬°©Ò­nªº¸ê®Æ ªººô­¶¤¸¯À
  13. '<span class="t2 table-cell">´Á§O</span>
  14. '<span class="t2 table-cell">2020</span>
  15. '<span class="t2 table-cell">2019</span>
  16. '<span class="t2 table-cell">2018</span>
  17. '<span class="t2 table-cell">2017</span>
  18. '<span class="t2 table-cell">2016</span>
  19. '<span class="t2 table-cell">2015</span>
  20. '<span class="t2 table-cell">2014</span>
  21. '<span class="t2 table-cell">2013</span>
  22. '</div>************************************
  23. For i = 0 To TableG.Length - 1
  24.     For j = 0 To TableG(i).all.tags("span").Length - 1
  25.        Cells(i + 1, j + 1) = TableG(i).all.tags("span")(j).innertext
  26.     Next j
  27. Next i
  28. End With
  29. Set HTMLsourcecode = Nothing 'ÄÀ©ñ°O¾ÐÅé
  30. Set GetXml = Nothing
  31. End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
½Æ»s¥N½X
½Ð±Ð ª©¤j §Ú office 2007 ª© ´ú¸Õ µ{¦¡
Set TableG = HTMLsourcecode.getelementsbyclassname("table-row")
³o¦æ·|»~¡A¬O§_ª©¥»ªº°ÝÃD
ÁÂÁ ª©¤j
Image 7.jpg

TOP

¦^´_ 14# GBKEE


    ³o¦¸ºô­¶§ïª©¡A§ì¨úªº¦³ªºªÑ²¼¦æ¼Æ¤£¦P¡A¹³ª÷¿ÄªÑ¨º¤@¬q¨úB67Äæ´N¤£¦b¨º¦æ¡A´N¹Á¸Õ¥Îfind ,¥þ³¡¤W¥«¥i¥H¶]§¹¡C

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2022-1-12 10:00 ½s¿è

¦^´_ 18# wufonna
¦h¼g´X¦æ¸Õ¸Õ¸Õ¸Õ¬Ý
  1. Private Sub GetIncome() '¨ú·l¯qªí(¦~ªí)ºô­¶
  2.     Dim Url, HTMLsourcecode, GetXml, TableG As Object, i As Integer, j As Integer, a As Integer
  3.     Set HTMLsourcecode = CreateObject("htmlfile")
  4.     Set GetXml = CreateObject("msxml2.xmlhttp")
  5.     Url = "http://kgieworld.moneydj.com/z/zc/zcq/zcqa/zcqa_2330.djhtm"
  6.     With GetXml
  7.         .Open "GET", Url, False
  8.         .send
  9.         HTMLsourcecode.write .Responsetext
  10.         Do  '  µ{¦¡¹B¦æ³t«×§Ö¥Î°j°é,½T«OTableGª«¥ó¤U¸ü§¹¾ã
  11.             Set TableG = HTMLsourcecode.all.tags("div")
  12.             DoEvents
  13.             '**   "JScriptTypeInfo"  ¥i¦b °Ï°ìÅܼƵøµ¡ ¬d¬Ý
  14.             '** ©Î¬O   Debug.Print TypeName(TableG) ±oª¾
  15.         Loop Until TypeName(TableG) = "JScriptTypeInfo"   
  16.           '*****±oª¾ classname="table-row" ªº¦ì¸m
  17.         For a = 0 To TableG.Length - 1
  18.             'Debug.Print a, TableG(a).classname
  19.             If TableG(a).classname = "table-row" Then Exit For   'Ū¨ú¸ê®Æ°_©l¦C¼Æ
  20.         Next
  21.         '***************************************
  22.        With ActiveSheet
  23.             .Cells.Clear
  24.             For i = a To TableG.Length - 1
  25.                 For j = 0 To TableG(i).all.tags("span").Length - 1
  26.                     .Cells(i - (a - 1), j + 1) = TableG(i).all.tags("span")(j).innertext
  27.                     '****** i - ( a-1) >>­×¥¿±q²Ä¤@¦C¶}©l¤U¸ü¸ê®Æ
  28.                 Next j
  29.             Next i
  30.         End With
  31.         '***ºô­¶ªº½s½X**********************************
  32.         '<div class="table-row">   ¬°©Ò­nªº¸ê®Æ ªººô­¶¤¸¯À
  33.         '<span class="t2 table-cell">´Á§O</span>
  34.         '<span class="t2 table-cell">2020</span>
  35.         '<span class="t2 table-cell">2019</span>
  36.         '<span class="t2 table-cell">2018</span>
  37.         '<span class="t2 table-cell">2017</span>
  38.         '<span class="t2 table-cell">2016</span>
  39.         '<span class="t2 table-cell">2015</span>
  40.         '<span class="t2 table-cell">2014</span>
  41.         '<span class="t2 table-cell">2013</span>
  42.         '</div>************************************
  43.     End With
  44.     Set HTMLsourcecode = Nothing 'ÄÀ©ñ°O¾ÐÅé
  45.     Set GetXml = Nothing
  46. End Sub '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD