如何下載SGX NLT 期貨及選擇權資料

　暱稱: joey0415

1^# 跳轉到 »

發表於 2015-1-26 00:06 | 顯示全部帖子

回復 1# chwqk

post的內容太多，改用另一種方法

Sub SGX_NLT_期貨及選擇權資料()
Dim URL As String, shts As Worksheet
Dim x As Variant, xi As Integer, A As Object, xlHtm
Set shts = ActiveSheet ' '("工作表2")
shts.Cells.Clear
URL = "http://www.sgx.com/wps/portal/sgxweb/home/marketinfo/derivatives/Nlt"
With CreateObject("InternetExplorer.Application")
.Visible = True ' 是否顯示 IE
.Navigate URL
Do While .ReadyState <> 4 Or .Busy
DoEvents
Loop
Application.Wait Now + TimeValue("00:00:06")
xlHtm = .Document.body.innerHTML '儲存
Set A = .Document.getElementsBytagname("table")
For xi = 116 To 118 Step 2
.Document.body.innerHTML = A(xi).outerHTML
.ExecWB 17, 2 ' Select All
.ExecWB 12, 2 ' Copy selection
With shts
.Range("A" & .[A1048576].End(xlUp).Row + 1).Select
.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
End With
.Document.body.innerHTML = xlHtm '還原
Next xi
shts.Cells.EntireColumn.AutoFit ' 自動調整欄寬
.Quit
End With
End Sub

複製代碼

　暱稱: joey0415

2^#

發表於 2015-1-29 12:29 | 顯示全部帖子

回復 3# chwqk

舊版改成

.Range("A" & .[A65535].End(xlUp).Row + 1).Select

　暱稱: joey0415

3^#

發表於 2015-6-20 21:48 | 顯示全部帖子

回復 6# chwqk

Sub SGX_NLT_1()
Dim URL As String, shts As Worksheet
Dim x As Variant, xi As Integer, A As Object, xlHtm
Set shts = ActiveSheet ' '("工作表2")
shts.Cells.Clear
URL = "http://www.sgx.com/wps/portal/sgxweb/home/marketinfo/derivatives/Nlt"
With CreateObject("InternetExplorer.Application")
.Visible = True ' 是否顯示 IE
.Navigate URL
Do While .ReadyState <> 4 Or .Busy
DoEvents
Loop
Application.Wait Now + TimeValue("00:00:06")
xlHtm = .Document.body.innerHTML '儲存
Set A = .Document.getElementsBytagname("table")
For xi = 74 To 74
.Document.body.innerHTML = A(xi).outerHTML
.ExecWB 17, 2 ' Select All
.ExecWB 12, 2 ' Copy selection
With shts
.Range("A" & .[A1048576].End(xlUp).Row + 1).Select
.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
End With
.Document.body.innerHTML = xlHtm '還原
Next xi
shts.Cells.EntireColumn.AutoFit ' 自動調整欄寬
.Quit
End With
End Sub

複製代碼

Sub SGX_NLT_2()
Dim URL As String, shts As Worksheet
Dim x As Variant, xi As Integer, A As Object, xlHtm
Set shts = ActiveSheet ' '("工作表2")
shts.Cells.Clear
URL = "http://www.sgx.com/wps/portal/sgxweb_ch/home/marketinfo/derivatives/Nlt"
With CreateObject("InternetExplorer.Application")
.Visible = True ' 是否顯示 IE
.Navigate URL
Do While .ReadyState <> 4 Or .Busy
DoEvents
Loop
Application.Wait Now + TimeValue("00:00:06")
xlHtm = .Document.body.innerHTML '儲存
Set A = .Document.getElementsBytagname("table")
For xi = 113 To 115 Step 2
.Document.body.innerHTML = A(xi).outerHTML
.ExecWB 17, 2 ' Select All
.ExecWB 12, 2 ' Copy selection
With shts
.Range("A" & .[A1048576].End(xlUp).Row + 1).Select
.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
End With
.Document.body.innerHTML = xlHtm '還原
Next xi
shts.Cells.EntireColumn.AutoFit ' 自動調整欄寬
.Quit
End With
End Sub

複製代碼