- 帖子
- 41
- 主題
- 8
- 精華
- 0
- 積分
- 79
- 點名
- 0
- 作業系統
- XP
- 軟體版本
- OFFICE 2003
- 閱讀權限
- 20
- 註冊時間
- 2011-3-8
- 最後登錄
- 2022-4-16
|
想請教這是這是現金流量表的網頁
http://mops.twse.com.tw/mops/web/t164sb05
透過上述程序抓取後在excel表上字串無法呈現表格,請問要如何修改
A = Split(.Document.getelementsbytagname("table")(16).innertext, vbLf)'這行我16改3
A = Split(.Document.getelementsbytagname("table")(3).innertext, vbLf)
Option Explicit
Sub 個別現金流量表()
Dim i, r As Integer, A As Variant, AA As Variant
With CreateObject("InternetExplorer.Application")
.Navigate "http://mops.twse.com.tw/mops/web/t164sb05"
.Visible = True
Do While .Busy Or .ReadyState <> 4: DoEvents: Loop
With .Document
.getelementsbytagname("input")(38).Value = "5349" '這裡修改 股票代號
.getelementsbytagname("input")(41).Click
End With
Do While .Busy Or .ReadyState <> 4: DoEvents: Loop
'******************************
r = 1
Cells.Clear
A = Split(.Document.getelementsbytagname("table")(3).innertext, vbLf)
For i = 0 To UBound(A)
AA = Trim(A(i))
Do While InStr(AA, Space(5))
AA = Replace(AA, Space(5), Space(4))
Loop
AA = Split(AA, Space(2))
Cells(r, 1).Resize(, UBound(AA) + 1) = AA
r = r + 1
Next
.Quit
End With
'******************************
資料整理
End Sub
Private Sub 資料整理()
Dim A, i
Application.DisplayAlerts = False
With ActiveSheet
.Range("B:B,D ").Delete Shift:=xlToLeft
With .Range("B:C")
.Replace Chr(32) & Chr(13), "", xlPart '清除不可見字元
.Replace Chr(41) & Chr(13), ")" '清除不可見字元
.Replace "$", ""
.Replace ",", ""
.NumberFormatLocal = "#,##0_);[紅色](#,##0)"
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
End With
With .Range("A2:C2,A3:C3,A4:C4,A5:C5")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Merge
.Areas(4).HorizontalAlignment = xlRight
End With
.[A6:A7].Insert Shift:=xlToRight
i = 8
Do While .Cells(i, 1) <> ""
If InStr(.Cells(i, 1), String(2, Mid(.Cells(i, 1), 1, 1))) Then
.Cells(i, 1).Resize(, 3).Merge
.Cells(i, 1).Resize(, 3).HorizontalAlignment = xlCenter
ElseIf Len(.Cells(i, 1)) >= 15 Then
.Cells(i, 1).WrapText = True
End If
i = i + 1
Loop
Range("A:A").ColumnWidth = 40
.Range("B:C").EntireColumn.AutoFit
End With
Application.DisplayAlerts = True
End Sub |
|
發表於 2015-5-17 10:19
| 