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

¦p¦ó§ä¥X¤U¸üCSVÀɪº°Ñ¼Æ

¦p¦ó§ä¥X¤U¸üCSVÀɪº°Ñ¼Æ

½Ð°ª¤â«üÂI¤W¥«¶R½æ¤é³øªí¬d¸ß¨t²Îhttp://bsr.twse.com.tw/bshtm/
¦p¦ó§ä¥X¤U¸üCSVÀɪº°Ñ¼Æ

¦^´_ 1# chihminyang88
°Ñ¼Æ¬O¨S¦³¥Îªº¡A­«ÂI¬O¹Ï§Î»{ÃÒ

TOP

¦^´_ 2# joey0415

·PÁ´£¿ô,¦]¦³¥Î  " ½Ð°Ý¦p¦ó±Nºô­¶ªº¹Ï¤ù¦sÀÉ " ·j´M¨ì¥»ºô¯¸G¤jªºVB½d¨Ò
©Ò¥H¹Ï¤ù½X°ÝÃD¤w¸Ñ¨M,¦ý¨S§ä¨ì¤W¥«¤U¸üCVSÀɪº°Ñ¼Æ, ¤j¤j±z­Yª¾±x¥i§_«ü
ÂI¤@¤U,·P®¦ !

TOP

¦^´_ 3# chihminyang88

©Ò¥H§A­n¤â°Ê¤@Àɤ@ÀÉkey¤U¸ü

TOP

¦^´_ 3# chihminyang88

½d¨Ò¸Õ¸Õ¬Ý


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

TOP

¦^´_ 4# joey0415
¦]¬ã¨sVB®É¶¡¤£¤[,©Ò¥H¥ý¨D¦³¦A·Q¿ìªk¨D¦n,¤j¤j¥i¦³·Qªk©Î«Øij´£¨Ñ ,ÁÂÁÂ!

TOP

¦^´_ 5# GBKEE
ÁÂÁÂG¤j´£¨Ñªºµ{¦¡,¥ý¦¬¤U ºCºC¬ã¨sµ{¦¡½X¤¤,·PÁÂ!

TOP

¦^´_ 7# chihminyang88

ÂI¼Æ¤£¨¬.½Ð°Ý¥i¥H¶}©ñµ{¦¡¶Ü..ÁÂÁÂ

TOP

¦^´_ 8# abba9817
¤£¤F¸Ñ¥i°Ñ¦Ò³o¸Ì



¤W¥«¤u§@ªí¼Ò²Õªºµ{¦¡½X
  1. Public ie As Object, Msg As Boolean
  2. Const ¹Ï§Î = "d:\ÅçÃÒ¹Ï.jpg"
  3. Const ÃÒ¨é¥N¸¹ = "F2"
  4. Const ÅçÃÒ½X = "F4"
  5. Private Sub Worksheet_Change(ByVal Target As Range)
  6.     Range(ÃÒ¨é¥N¸¹).Interior.ColorIndex = IIf(Range(ÃÒ¨é¥N¸¹).Value = "", 2, 36)
  7.     With Target.Cells(1)
  8.          If .Address(0, 0) = ÅçÃÒ½X Then .Interior.ColorIndex = IIf(Len(Trim(.Cells)) = 5, 36, 2)
  9.          If .Address(0, 0) = ÅçÃÒ½X And Len(Trim(.Cells)) = 5 And Range(ÃÒ¨é¥N¸¹).Value <> "" Then
  10.             If ie Is Nothing Then
  11.                 Target = ""
  12.                 Msg = True
  13.                 ¹Ï§Î§ó·s
  14.                 Exit Sub
  15.             End If
  16.             Application.EnableEvents = False
  17.             Åª¨ú¤é³øªíºô­¶
  18.             Target = ""
  19.             Me.Activate
  20.             Application.EnableEvents = True
  21.         End If
  22.     End With
  23. End Sub
  24. Private Sub Ū¨ú¤é³øªíºô­¶()
  25.     Dim S As String
  26.     Application.EnableEvents = True
  27.     If ie Is Nothing Then
  28.         ¹Ï§Î§ó·s
  29.         MsgBox "ÅçÃҹϤw§ó·s"
  30.         Exit Sub
  31.     End If
  32.     With ie
  33.         .navigate "http://bsr.twse.com.tw/bshtm/bsMenu.aspx"
  34.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  35.         .Document.All("TextBox_Stkno").Value = Range(ÃÒ¨é¥N¸¹)
  36.         .Document.All("CaptchaControl1").Value = Range(ÅçÃÒ½X)
  37.         .Document.All("btnOK").Click
  38.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  39.         If InStr(.Document.body.Innertext, "¬dµL¸ê®Æ") Then
  40.         S = Range(ÃÒ¨é¥N¸¹) & " ¬dµL¸ê®Æ"
  41.         ElseIf InStr(.Document.body.Innertext, "ÅçÃÒ½X¿ù»~!") Then
  42.             S = "ÅçÃÒ½X¿ù»~!"
  43.         Else
  44.             ¤é³øªí¤U¸ü
  45.             ¤é³øªí_¾ã²z_¦sÀÉ
  46.             S = "¤U¸ü " & Range(ÃÒ¨é¥N¸¹) & " CSV OK"
  47.             With Cells(Rows.Count, 1).End(xlUp)
  48.                 If .Row < 6 Then
  49.                     Range("A6") = S
  50.                 Else
  51.                     .Cells(2) = S
  52.                 End If
  53.             End With
  54.         End If
  55.         [A1] = S
  56.     End With
  57.     ¹Ï§Î§ó·s
  58. End Sub
  59. Private Sub ¤é³øªí¤U¸ü()
  60.       With CreateObject("InternetExplorer.Application")
  61.         .Visible = True
  62.         .navigate "http://bsr.twse.com.tw/bshtm/bsContent.aspx?v=t"
  63.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  64.         .ExecWB 17, 2
  65.         .ExecWB 12, 2
  66.          .Quit
  67.       End With
  68. End Sub
  69. Private Sub ¤é³øªí_¾ã²z_¦sÀÉ()
  70.     Dim Rng As Range, E As Range
  71.     With Sheet2
  72.         .Activate
  73.         .UsedRange.Clear
  74.         .Range("A1").Select
  75.         .PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
  76.         .UsedRange.Offset(10).Columns(1).Replace "¥æ©ö¤é´Á", "=aaa", xlWhole
  77.         With .UsedRange
  78.             For Each E In .SpecialCells(xlCellTypeFormulas, xlErrors).Areas
  79.                 If Rng Is Nothing Then
  80.                     Set Rng = E.Offset(-1).Resize(5, 16)
  81.                 Else
  82.                     Set Rng = Union(Rng, E.Offset(-1).Resize(5, 16))
  83.                 End If
  84.             Next
  85.              .SpecialCells(xlCellTypeBlanks).Delete xlShiftToLeft
  86.         End With
  87.         Rng.Delete
  88.         .Copy          '¤u§@½Æ»s
  89.     End With
  90.     Application.DisplayAlerts = False
  91.     With ActiveWorkbook
  92.         .Sheets(1).Name = Range(ÃÒ¨é¥N¸¹)
  93.         '*******"D:\TEST\" ¥i­×§ï ************************
  94.         .SaveAs "D:\TEST\" & Range(ÃÒ¨é¥N¸¹).Text & ".CSV"
  95.         '*************************************************
  96.         .Close True
  97.     End With
  98.     Application.DisplayAlerts = True
  99. End Sub
  100. Private Sub ¹Ï§Î§ó·s()
  101.     Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8"  'Clear_IE_Temp_Files
  102.     If Not ie Is Nothing Then ie.Quit:  Set ie = Nothing
  103.     Set ie = CreateObject("InternetExplorer.Application")
  104.     If Msg Then MsgBox "ÅçÃÒ¹Ï §ó·s§¹²¦"
  105.     Msg = False
  106.     With ie
  107.         .navigate "http://bsr.twse.com.tw/bshtm/bsMenu.aspx"
  108.         .Visible = True
  109.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
  110.         ºô¸ô¹Ï¤ù¦sÀÉ .Document.All.TAGS("IMG")(1).href
  111.     End With
  112.     Sheet1.Shapes("ÅçÃÒ¹Ï").Fill.UserPicture ¹Ï§Î
  113. End Sub
  114. Private Sub ºô¸ô¹Ï¤ù¦sÀÉ(img As String)
  115.     Dim xml As Object     '¥Î¨Ó¨ú±oºô­¶¸ê®Æ
  116.     Dim stream            'As ADODB.stream   '¥Î¨ÓÀx¦s¤G¶i¦ìÀÉ®×
  117.     Set xml = CreateObject("Microsoft.XMLHTTP")
  118.     Set stream = CreateObject("ADODB.stream")
  119.     xml.Open "GET", img, 0
  120.     xml.send
  121.     With stream
  122.         .Open
  123.         .Type = 1
  124.         .write xml.responseBody
  125.         If Dir(¹Ï§Î) <> "" Then Kill ¹Ï§Î
  126.         .SaveToFile (¹Ï§Î)
  127.         .Close
  128.     End With
  129. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 9# GBKEE

¤Ó·PÁ¤F.....

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD