- 帖子
- 112
- 主題
- 19
- 精華
- 0
- 積分
- 136
- 點名
- 0
- 作業系統
- window
- 軟體版本
- excel
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2013-3-12
- 最後登錄
- 2022-11-29
|
Dear G大謝謝您的費心，如上所提，我只修改表頭與endsub前的Next DQ，僅此而已，
實際上也看到IE的股票代號有動作，但page2 ~page5 並沒有將資料帶進來??
Sub Ex()
Dim i As Integer, s As Integer, k As Integer, A, ii, j
Dim co_id As String, isnew As String, season As String
Dim DQ as Integer, DQQ As Integer
For DQ = 1 To 5
DQQ = 6
Sheets(DQQ).Select
co_id = Range("A" & DQ).Value
Sheets(DQ).Select
isnew = 1
With CreateObject("InternetExplorer.Application")
.Visible = True
.Navigate "http://mops.twse.com.tw/mops/web/t164sb04"
Do While .Busy Or .ReadyState <> 4: DoEvents: Loop
With .document
For Each A In .getelementsbytagname("INPUT")
If A.Name = "co_id" Then A.Value = co_id
Next
For Each A In .getelementsbytagname("SELECT")
If A.Name = "isnew" Then
A.Value = True
If isnew = "2" Then
A.Focus
Application.Wait Now + #12:00:02 AM#
Application.SendKeys "{DOWN}"
Application.Wait Now + #12:00:02 AM#
Application.SendKeys "{ENTER}"
End If
End If
If A.Name = "year" And isnew = "2" Then A.Value = Split(season, ",")(0)
If A.Name = "season" And isnew = "2" Then A.Value = Split(season, ",")(1)
Next
For Each A In .getelementsbytagname("INPUT")
If Trim(A.Value) = "搜尋" And A.Name <> "rulesubmit" Then A.Click '按下[搜索]鍵
Next
End With
Application.Wait Now + #12:00:10 AM# '等待網頁下載資料
Set A = .document.getelementsbytagname("table")
On Error Resume Next '***有些table沒Rows資料會產生錯誤 不理會它,程式繼續走
With ActiveSheet
.Cells.Clear
'************************
' For ii = 0 To A.Length - 1 '不知道table範圍在何處: 從0開始
'******************************
For ii = 11 To A.Length - 1 ''從11開始 用 Debug.Print ii 找出所要資料的table範圍
For i = 0 To A(ii).Rows.Length - 1 '寫入資料
'Debug.Print ii 可找出所要資料的 table 範圍
k = k + 1
For j = 0 To 5
Cells(k, j + 1) = A(ii).Rows(i).Cells(j).innerText
Next
Next
Next
.Range("C5").Cut Range("D5")
With .Range("B5:C5,D5:E5")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
End With
End With
.Quit '關閉網頁
End With
Next DQ
End Sub |
|
發表於 2013-5-22 18:46
| 
