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

[µo°Ý] ½Ð°Ý³o¥i³]©wµ¥´X¬í¨S¶}ºô­¶©Îºô­¶¿ù»~´N°õ¦æ¤U¤@µ§¶Ü¡AÁÂÁÂ

¦^´_ 16# GBKEE
½Ð±Ð G ¤j¤j
§Ú§ï¤F¤@¨Ç¸ê®Æ ¦³¨Ç¸ê®ÆÁÙ¦¬¤£¨ì
½Ð G ¤j ¬Ý¨º¸Ì­n§ïªº
ÁÂÁÂ
  1. Dim ie As Object   '¼Ò²Õ³Ì³»ºÝ Dim ¨Ñ³o¼Ò²Õªºµ{§Ç¨Ï¥ÎªºÅܼÆ
  2. Sub AllFile()
  3.     Dim i As Integer, v, Y As Integer, S As String
  4.     Set ie = CreateObject("internetexplorer.application")   '¨Ï¥Î¦¹¤è¦¡¥i¥H§K°£ "³]©w¤Þ¥Î¶µ¥Ø"
  5.     With ie 'ÁY¤pIEµøµ¡
  6.         .Visible = True
  7.         .Width = 5
  8.         .Height = 5
  9.     End With
  10.     With ¤u§@ªí1
  11.       Dim AR
  12.         AR = .Range("E1:G1")
  13.         .Range("E:G") = ""
  14.         .Range("E1:G1") = AR

  15. '        .Range("E2").CurrentRegion = ""            '²M°£¤u§@ªí1,¦~«×½d³ò
  16.         For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
  17.             v = .Cells(i, 1).Value
  18.             GetDividend (v)
  19.           .Cells(i, 5).Value = ¤u§@ªí2.Cells(2, 2).Value
  20.            .Cells(i, 6).Value = ¤u§@ªí2.Cells(2, 5).Value
  21.            GetClosePrice (v)
  22.            .Cells(i, 7).Value = ¤u§@ªí3.Cells(2, 8).Value
  23.             
  24.         Next
  25.     End With
  26.     With ie  'IEµøµ¡³Ì¤j¤Æ
  27.         Application.WindowState = xlMaximized
  28.         .Height = Application.Height
  29.         .Width = Application.Width
  30.         .Quit
  31.     End With
  32. End Sub
  33. 'Private Sub GetDividend(ByVal ss As String)


  34. Private Sub GetDividend(ByVal ss As String)     '¨úªÑ§Qºô­¶

  35.     Dim rr As String, T As Date, i, ii, k, j, S As Object
  36.     'On Error Resume Next 'µ{¦¡ªº°õ¦æ¨S¦³¹w´Áªº¿ù»~,¦¹¦æ¥i¤£¥Î.
  37.     T = Time
  38.     rr = "http://dj.mybank.com.tw/z/zc/zcc/zcc_" & ss & ".asp.htm"
  39.     With ie
  40.         .Navigate rr
  41.         Do While .readyState <> 4                          'µ¥«Ýºô­¶¤U¸ü§¹²¦
  42.               DoEvents
  43.               If Time >= T + #12:00:03 AM# Then            'µ¥«Ý8¬í 3¬í¤Ó¤Ö·|»~¿ù§ï8§®
  44.                 DoEvents
  45.                 Application.SendKeys "~"                    'ªÑ²¼¥N¸¹¿ù»~,ºô­¶·|¦³°T®§,¶·«ö½T©w,¤~¥iÄ~Äò¤U­±ªÑ²¼¥N¸¹
  46.                
  47.                 Exit Do
  48.               End If
  49.         Loop
  50.         ''***¤£¬Oµ¥«Ý8¬í 3¬í¤Ó¤Ö·|»~¿ù§ï8§® ***
  51.         Do
  52.         Set S = .Document.getElementsByTagName("table")(3) ' ·sªº table 4
  53.         Loop Until Not S Is Nothing
  54.         '*** ³Ó¤@ ¨S®»¨ì ????
  55.         '*** ¦]µ{¦¡¹B¦æ¤Ó§Ö , S Is Nothing, S ©|¥¼«ü©w¬°.Document.getElementsByTagName("table")(4)
  56.         '*** µ{¦¡¤w¸g°õ¦æ¤U¤@¦æ, With ¤u§@ªí2 ªºµ{¦¡½X

  57.         With ¤u§@ªí2
  58.             .UsedRange.Clear
  59.             For i = 0 To S.Rows.Length - 1      '¼g¤J¸ê®Æ
  60.                 k = k + 1
  61.                 'For j = 0 To S.Rows(i).Length - 1  '³o¦æ¬O¿ù»~ªº ¤]¬O¦h¾lªº°j°é
  62.                    '¥Î On Error Resume Next ¨Ïµ{¦¡Ä~Äò°õ¦æ
  63.                     For ii = 0 To S.Rows(i).Cells.Length - 1  ' S.Rows(i).Cells.Length - 1 ¤~¬O¥¿½T
  64.                     .Cells(k, ii + 1) = S.Rows(i).Cells(ii).innerText
  65.                     DoEvents
  66.                     Next
  67.                 'Next
  68.             Next
  69.         End With
  70.     End With
  71. End Sub

  72. Private Sub GetClosePrice(ByVal ss As String) ' ¨ú¦¬½L»ùºô­¶
  73.     Dim rr As String, T As Date, i, ii, k, j, S As Object
  74.     'On Error Resume Next 'µ{¦¡ªº°õ¦æ¨S¦³¹w´Áªº¿ù»~,¦¹¦æ¥i¤£¥Î.
  75.     T = Time
  76.     rr = "https://djinfo.cathaysec.com.tw/Z/ZC/ZCX/ZCXNEWCATHAYSEC.DJHTM?A=" & ss
  77.     With ie
  78.         .Navigate rr
  79.         Do While .readyState <> 4                          'µ¥«Ýºô­¶¤U¸ü§¹²¦
  80.               DoEvents
  81.               If Time >= T + #12:00:03 AM# Then            'µ¥«Ý8¬í 3¬í¤Ó¤Ö·|»~¿ù§ï8§®
  82.                 DoEvents
  83.                 Application.SendKeys "~"                    'ªÑ²¼¥N¸¹¿ù»~,ºô­¶·|¦³°T®§,¶·«ö½T©w,¤~¥iÄ~Äò¤U­±ªÑ²¼¥N¸¹
  84.                
  85.                 Exit Do
  86.               End If
  87.         Loop
  88.         ''***¤£¬Oµ¥«Ý8¬í 3¬í¤Ó¤Ö·|»~¿ù§ï8§® ***
  89.         Do
  90.         Set S = .Document.getElementsByTagName("table")(2) ' ·sªº table 4
  91.         Loop Until Not S Is Nothing
  92.         '*** ³Ó¤@ ¨S®»¨ì ????
  93.         '*** ¦]µ{¦¡¹B¦æ¤Ó§Ö , S Is Nothing, S ©|¥¼«ü©w¬°.Document.getElementsByTagName("table")(4)
  94.         '*** µ{¦¡¤w¸g°õ¦æ¤U¤@¦æ, With ¤u§@ªí2 ªºµ{¦¡½X

  95.         With ¤u§@ªí3
  96.             .UsedRange.Clear
  97.             For i = 0 To S.Rows.Length - 1      '¼g¤J¸ê®Æ
  98.                 k = k + 1
  99.                 'For j = 0 To S.Rows(i).Length - 1  '³o¦æ¬O¿ù»~ªº ¤]¬O¦h¾lªº°j°é
  100.                    '¥Î On Error Resume Next ¨Ïµ{¦¡Ä~Äò°õ¦æ
  101.                     For ii = 0 To S.Rows(i).Cells.Length - 1  ' S.Rows(i).Cells.Length - 1 ¤~¬O¥¿½T
  102.                     .Cells(k, ii + 1) = S.Rows(i).Cells(ii).innerText
  103.                     DoEvents
  104.                     Next
  105.                 'Next
  106.             Next
  107.         End With
  108.     End With
  109. End Sub
½Æ»s¥N½X

°t®§´ú¸Õ-3.rar (29.12 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-9-4 16:25 ½s¿è

¦^´_ 21# wufonna
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Dim ie As Object   '¼Ò²Õ³Ì³»ºÝ Dim ¨Ñ³o¼Ò²Õªºµ{§Ç¨Ï¥ÎªºÅܼÆ
  3. Dim AR()
  4. Sub AllFile()
  5.     Dim i As Integer
  6.     Set ie = CreateObject("internetexplorer.application")   '¨Ï¥Î¦¹¤è¦¡¥i¥H§K°£ "³]©w¤Þ¥Î¶µ¥Ø"
  7.     With ¤u§@ªí1
  8.         AR = .Range("E1:G1")
  9.         .Range("E:G") = ""
  10.         .Range("E1:G1") = AR
  11.         For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
  12.            ReDim AR(1 To 3)
  13.            Application.StatusBar = .Cells(i, 1) & "  " & .Cells(i, 2) & " Ū¨ú¤¤..."
  14.             GetDividend .Cells(i, 1), 3
  15.             GetDividend .Cells(i, 1), 2
  16.             .Range("E1:G1").Offset(i - 1) = AR
  17.         Next
  18.     End With
  19.     ie.Quit
  20. End Sub
  21. Private Sub GetDividend(ByVal ss As String, ByVal table As Integer)
  22.     Dim rr As String, S As Object
  23.     If table = 3 Then
  24.         rr = "http://dj.mybank.com.tw/z/zc/zcc/zcc_" & ss & ".asp.htm"                'ªÑ§Qºô­¶
  25.     ElseIf table = 2 Then
  26.         rr = "https://djinfo.cathaysec.com.tw/Z/ZC/ZCX/ZCXNEWCATHAYSEC.DJHTM?A=" & ss '¦¬½L»ùºô­¶
  27.     End If
  28.     With ie
  29.         .Navigate rr
  30.         Do While .readyState <> 4 Or .Busy                        'µ¥«Ýºô­¶¤U¸ü§¹²¦
  31.               DoEvents
  32.         Loop
  33.         With .document.BODY
  34.            If InStr(.INNERTEXT, "­ÓªÑ¥N½X¿ù»~") Or InStr(.INNERTEXT, "µL¦¹ªÑ²¼¸ê®Æ") Then
  35.                 MsgBox .INNERTEXT
  36.                 Exit Sub
  37.            End If
  38.         End With
  39.         Do
  40.         Set S = .document.getElementsByTagName("table")(table)   ' ·sªº table 4
  41.         Loop Until Not S Is Nothing
  42.         If table = 3 Then
  43.             AR(1) = S.Rows(1).Cells(1).INNERTEXT            '²{ª÷ªÑ§Q
  44.             AR(2) = S.Rows(1).Cells(4).INNERTEXT            'ªÑ²¼ªÑ§Q
  45.         ElseIf table = 2 Then
  46.             AR(3) = S.Rows(1).Cells(7).INNERTEXT            '¦¬½L»ù
  47.         End If
  48.     End With
  49. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 22# GBKEE

·PÁÂ GBKEE ¤j
µ{¦¡¶]¤F´X¦¸³£¯à®»¨ì
µ{¦¡½X§Ú¬ã¨s¬Ý¬Ý ¤£·|¦A¦V GBKEE ¤j¤j½Ð±Ð
ÁÂÁÂ ^_^

TOP

¦^´_ 1# wufonna
§ï¤Fµ{¦¡¤º®e

½Ð±Ðµ{¦¡¨S¥[¤J³o¬q On Error Resume Next '¤U¦æ·|¥X¿ù¡A¥[¤J³o¦æ¡A¥¼ª¾­ì¦]¡C ·|¿ù»~¬Oºô­¶¦³ªÅ®æªºÃö·Ë¶Ü?
½Ð±Ð¤j¤j¦p¦ó­×§ï ÁÂÁÂ
  1. Option Explicit
  2. Dim ie As Object   '¼Ò²Õ³Ì³»ºÝ Dim ¨Ñ³o¼Ò²Õªºµ{§Ç¨Ï¥ÎªºÅܼÆ
  3. Sub AllFile()
  4.     Dim i As Integer, v, Y As Integer, S As String
  5.     Dim z As Integer

  6.     With ¤u§@ªí1
  7.         Dim AR
  8.            AR = .Range("C1:J1")
  9.           .Range("C:J") = ""
  10.           .Range("C1:J1") = AR
  11.           z = 0
  12.         For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
  13.      
  14.            v = .Cells(i, 1).Value
  15.             GetDividend (v)
  16.              .Cells(i, 3).Resize(1, 7).Value = ¤u§@ªí2.Cells(7, 1).Resize(1, 7).Value

  17.              If ¤u§@ªí2.Cells(7, 5).Value > 0 Then
  18.                .Cells(i, 10).Value = 1
  19.                z = z + 1
  20.               Else
  21.                .Cells(i, 10).Value = 0
  22.               End If
  23.                             If ¤u§@ªí2.Cells(7, 5).Value > 0 And ¤u§@ªí2.Cells(8, 5).Value > 0 And ¤u§@ªí2.Cells(9, 5).Value > 0 Then 'K(À禬³s3­Ó¤ë¥¿¦¨ªø)
  24.                 .Cells(i, 11).Value = 1
  25.               Else
  26.                 .Cells(i, 11).Value = 0
  27.               End If
  28.         Next
  29. '            MsgBox "¦@¦³" & z & "®a¥¿¦¨ªø"
  30. .Cells(1, 10).Value = "¥h¦~¦P´Á¦~¼W²v" & Split(Date, "/")(1) - 1 & "¤ë¥÷" & .Range("A" & .Rows.Count).End(xlUp).Row & "®a¦@¦³" & z & "®a¥¿¦¨ªø"
  31.    
  32.     End With

  33. End Sub

  34. Public Function MyFile(v As Integer, i As Integer)
  35.   '   Dim i As Integer, v, Y As Integer, S As String

  36.     With ¤u§@ªí1
  37.            .Range("C" & v & ":J" & v) = "" '²M°£¤u§@ªí1,¦~«×½d³ò
  38.            v = .Cells(i, 1).Value
  39.             GetDividend (v)
  40.              .Cells(i, 3).Resize(1, 7).Value = ¤u§@ªí2.Cells(7, 1).Resize(1, 7).Value

  41.              If ¤u§@ªí2.Cells(7, 5).Value > 0 Then
  42.                .Cells(i, 10).Value = 1

  43.               Else
  44.                .Cells(i, 10).Value = 0
  45.               End If
  46.               If ¤u§@ªí2.Cells(7, 5).Value > 0 And ¤u§@ªí2.Cells(8, 5).Value > 0 And ¤u§@ªí2.Cells(9, 5).Value > 0 Then 'k (À禬³s3­Ó¤ë¥¿¦¨ªø)
  47.                 .Cells(i, 11).Value = 1
  48.               Else
  49.                 .Cells(i, 11).Value = 0
  50.               End If
  51.               
  52.     End With

  53. End Function



  54. Private Sub GetDividend(ByVal ss As String)     '¨úªÑ§Qºô­¶ '2022/2/22 ´«³o¬qµ{¦¡½X ¦b https://www.mobile01.com/topicdetail.php?f=511&t=4737630&p=3 ªº21¼Ó
  55. Dim URL, HTMLsourcecode, GetXml, Table
  56. Dim i As Integer, j As Integer
  57. Set HTMLsourcecode = CreateObject("htmlfile")
  58. Set GetXml = CreateObject("msxml2.xmlhttp")
  59. URL = "http://pscnetinvest.moneydj.com.tw/z/zc/zch/zch_" & ss & ".djhtm"
  60. With GetXml
  61. .Open "GET", URL, False
  62. .setRequestHeader "Cache-Control", "no-cache"
  63. .setRequestHeader "Pragma", "no-cache"
  64. .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
  65. .send

  66. HTMLsourcecode.body.innerhtml = .responsetext
  67. On Error Resume Next '¤U¦æ·|¥X¿ù¡A¥[¤J³o¦æ¡A¥¼ª¾­ì¦]¡C
  68. Set Table = HTMLsourcecode.all.tags("table")(2).Rows
  69. For i = 0 To Table.Length - 1
  70. For j = 0 To Table(i).Cells.Length - 1
  71. ¤u§@ªí2.Cells(i + 1, j + 1) = Table(i).Cells(j).innertext
  72. Next j
  73. Next i
  74. End With
  75. Set HTMLsourcecode = Nothing
  76. Set GetXml = Nothing
  77. End Sub
½Æ»s¥N½X

Annualrate-2022.rar (105.34 KB)

TOP

¥»©«³Ì«á¥Ñ quickfixer ©ó 2022-2-12 21:01 ½s¿è

¦^´_ 24# wufonna


    ª±¤F¤@¤U,·|¥X¿ù¬O¨S§ì¨ì¸ê®Æ,¦n¹³¬Oµ{¦¡¶]¤Ó§Ö,¬y¶q­­¨îªº°ÝÃD,¥i¬O¨S¾×ip?
µ{¦¡¨S°ÝÃD,¥t¥~°µ¤@­Ócommandbutton,¥þ³¡½s¸¹¶]§¹«á,¦A­«§ì¦³ªÅ¥Õªº¸ê®Æ
Sub test()
    With ¤u§@ªí1
        For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
¥u§ì¨S¸ê®Æªº½s¸¹
           If .Cells(i, 3) = "" Then
            v = .Cells(i, 1).Value
            GetDividend (v)
'³o´X¦æcode ¦P AllFile ,®¤§R
           End If
        Next
    End With
End Sub

TOP

¦^´_ 25# quickfixer


    ÁÂÁ ¤j¤j ¡A§Ú¦³¥Î
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2.     Application.EnableEvents = False
  3.         If Target(1).Column = 1 And Target(1).Address(0, 0) <> "A1" Then          '¦b²Ä1¦C
  4.             If Target(1).Value <> "" Then
  5.             
  6. '         MsgBox Target(1).Address(0, 0) & vbCrLf & vbCrLf & Target(1).Value & vbCrLf & vbCrLf & Target(1).Row
  7.             Call MyFile(Target(1).Value, Target(1).Row)
  8.             End If
  9.         End If
  10.     Application.EnableEvents = True

  11. End Sub
½Æ»s¥N½X
§ì¨ú­Ó§Oªº¸ê®Æ
·Q½Ð±Ð¦³µ{¦¡¨ú¥N
On Error Resume Next '¤U¦æ·|¥X¿ù¡A¥[¤J³o¦æ¡A¥¼ª¾­ì¦]¡C
³o¬qµ{¦¡½X¶Ü

TOP

¥»©«³Ì«á¥Ñ quickfixer ©ó 2022-2-12 22:45 ½s¿è

¦^´_ 26# wufonna

¨Sª`·N¨ìSelectionChange¸Ì­±¦³­«§ìªºµ{¦¡½X

¥X¿ù®É    debug.print HTMLsourcecode.body.innerhtml ¥X²{³o­Ó,¨S¦³§ì¨ì¸ê®Æ


google httpcode=500
¦øªA¾¹ºÝ¿ù»~¦^À³
500 Internal Server Error
¦øªA¾¹ºÝµo¥Í¥¼ª¾©ÎµLªk³B²zªº¿ù»~¡C

¥i¯àµ{¦¡¶]¤Ó§Ö,¦P­Óip½Ð¨D¤Ó¦h,ºô­¶¨Ó¤£¤Î³B²z

TOP

¥»©«³Ì«á¥Ñ quickfixer ©ó 2022-2-12 23:14 ½s¿è

¦^´_ 27# quickfixer

§Ú°Ñ¦Ò§Aµ¹ªº01ºô§}686f,¥[¤J­«·s¤U¸ü¥\¯à,¥i¥þ³¡¶]§¹
  1. Private Sub GetDividend(ByVal ss As String)     '¨úªÑ§Qºô­¶ '2022/2/22 ´«³o¬qµ{¦¡½X ¦b https://www.mobile01.com/topicdetail.php?f=511&t=4737630&p=3 ªº21¼Ó
  2. Dim URL, HTMLsourcecode, GetXml, Table
  3. Dim i As Integer, j As Integer, r As Integer
  4. Set HTMLsourcecode = CreateObject("htmlfile")
  5. Set GetXml = CreateObject("msxml2.xmlhttp")
  6. URL = "http://pscnetinvest.moneydj.com.tw/z/zc/zch/zch_" & ss & ".djhtm"

  7. r = 0
  8. retry:
  9. On Error Resume Next

  10. With GetXml
  11. .Open "GET", URL, False
  12. .setRequestHeader "Cache-Control", "no-cache"
  13. .setRequestHeader "Pragma", "no-cache"
  14. .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
  15. .send

  16. HTMLsourcecode.body.innerhtml = .responsetext
  17. Set Table = HTMLsourcecode.all.tags("table")(2).Rows

  18. If Err.Number <> 0 Then

  19. If r > 3 Then
  20. '¶W¹L3¦¸,§ï§ì¤U¤@µ§,ÁקKµL­­loop
  21. Exit Sub
  22. End If
  23. Debug.Print Err.Description
  24. r = r + 1
  25. On Error GoTo -1
  26. Err.Clear
  27. 'µ¥0.5¬í
  28. Delaytick (0.5)
  29. GoTo retry

  30. End If
  31.             
  32. For i = 0 To Table.Length - 1
  33. For j = 0 To Table(i).Cells.Length - 1
  34. ¤u§@ªí2.Cells(i + 1, j + 1) = Table(i).Cells(j).innertext
  35. Next j
  36. Next i
  37. End With
  38. Set HTMLsourcecode = Nothing
  39. Set GetXml = Nothing
  40. End Sub



  41. Sub Delaytick(setdelay As Single)
  42.    
  43.     Dim StartTime As Double, NowTime As Double
  44.     StartTime = Timer * 100
  45.     setdelay = setdelay * 100
  46.     Do
  47.       NowTime = Timer * 100
  48.       DoEvents
  49.     Loop Until NowTime - StartTime > setdelay
  50.    
  51. End Sub
½Æ»s¥N½X
¦ý¬O§Aallfile¸Ì­±¨º­ÓSplit(Date, "/")(1) - 1
¥i¯à­n§ï¤@¤U,¦³¤Hexcel¤é´Á¬O¥Î-¤£¬O/
¥Îmid¥i¯à·|¤ñ¸û¦n¤@¨Ç

TOP

¥»©«³Ì«á¥Ñ wufonna ©ó 2022-2-13 17:20 ½s¿è

¦^´_ 28# quickfixer


    ÁÂÁÂ ¤j¤j
¤§«eµ{¦¡¥i¶]§¹¡A¥i¯à³o¨â¤ÑÀ禬§ó·s¡Aºô­¶¤~¦³¯Ê¤Ö¸ê®Æ¡Aµ¥ºô­¶§ó·s§¹¦b¤U¸ü¬Ý¬Ý¡C
¥[¤Fµ{¦¡½Xµ{¦¡¦³¶]§¹¡C

Image 1.jpg (145.4 KB)

Image 1.jpg

TOP

¦^´_ 28# quickfixer


    ÁÂÁÂ ¤j¤j
­×§ï
  1. Sub test()
  2. Debug.Print Date
  3. '­×§ï1¤ëªº«e¤@­Ó¤ë«K0
  4. If Split(Date, "/")(1) - 1 = 0 Then
  5. Debug.Print 12
  6. Else
  7. Debug.Print Split(Date, "/")(1) - 1
  8. End If

  9. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : §g¤l¥ß«í§Ó¡A¤p¤H«í¥ß§Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD