- ©«¤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
        
|
¦^´_ 5# lalalada
¸Õ¸Õ¬Ý- Option Explicit
- Sub ÃÒ¥æ©Ò_¶R½æ¥æ©ö¤é³øªí()
- Dim Stock_NO As String, Sh As Worksheet, URL As String, WB As Workbook
- Do
- Stock_NO = InputBox("ªÑ²¼¸¹½X")
- If Stock_NO = "" Then Exit Sub
- Loop Until Len(Stock_NO) >= 4
- Set Sh = ActiveWorkbook.Sheets.Add
- Application.DisplayAlerts = False
- On Error GoTo Er
- Sh.Name = Stock_NO
- Application.StatusBar = "¤U¸ü¸ê®Æ¤¤.... "
- Application.ScreenUpdating = False
- URL = "http://bsr.twse.com.tw/bshtm/bsContent.aspx?StartNumber=" & Stock_NO & "&FocusIndex=All_" & ³øªí¶¼Æ(Stock_NO)
- Set WB = Workbooks.Open(URL)
- With WB.Sheets(1)
- If .UsedRange.Count = 1 Then GoTo xOUT 'ªÑ²¼¨S¦³¸ê®Æ
- .UsedRange.Columns("A:Q").ClearFormats
- .UsedRange.Columns("B:H").Delete Shift:=xlToLeft
- .UsedRange.Columns("B:B").SpecialCells(xlCellTypeBlanks).Offset(, -1).Delete xlShiftUp
- .UsedRange.Columns("B:E").SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
- .UsedRange.Columns("F:J").SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
- .UsedRange.Columns("F:J").Cut .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
- .Rows("1:2").Delete
- With .UsedRange
- .Value = .Value
- .Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes
- .Offset(3).Replace "§Ç", "=XXX", xlWhole
- .SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
- End With
- .UsedRange.Copy Sh.[A1]
- Sh.UsedRange.EntireColumn.AutoFit
- End With
- WB.Close False
- Application.ScreenUpdating = True
- Application.StatusBar = False
- Exit Sub
- Er:
- Sheets(Stock_NO).Delete
- Resume
- xOUT:
- WB.Close False
- Sheets(Stock_NO).Delete
- MsgBox "¨S¦³ªÑ²¼ " & Stock_NO
- Application.StatusBar = False
- Application.ScreenUpdating = True
- End Sub
- Private Function ³øªí¶¼Æ(Sstock_N0 As String) As String
- Dim element As Object
- With CreateObject("InternetExplorer.Application")
- .Navigate "http://bsr.twse.com.tw/bshtm/bsMenu.aspx"
- Do While .Busy Or .ReadyState <> 4
- DoEvents
- Loop
- .Document.getElementsByName("txtTASKNO")(0).Value = Sstock_N0
- .Document.getElementsByName("btnOK")(0).Click
- Do While .Busy Or .ReadyState <> 4
- DoEvents
- Loop
- ³øªí¶¼Æ = .Document.getElementsByName("sp_ListCount")(0).innertext
- .Quit
- End With
- End Function
½Æ»s¥N½X |
|