標題:
如何下載SGX NLT 期貨及選擇權資料
[打印本頁]
作者:
chwqk
時間:
2015-1-22 19:30
標題:
如何下載SGX NLT 期貨及選擇權資料
http://www.sgx.com/wps/portal/sgxweb/home/marketinfo/derivatives/Nlt
如何以VBA 按"HERE"
Please click HERE to download the text file.
下載NLT_FUT.UNL 及 NLT_OPT.UNL
作者:
joey0415
時間:
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
複製代碼
作者:
chwqk
時間:
2015-1-29 08:43
標題:
RE: 如何下載SGX NLT 期貨及選擇權資料
With shts
.Range("A" & .[A1048576].End(xlUp).Row + 1).Select
.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
End With
經測試 此段執行出錯
請問
.Range("A" & .[A1048576].End(xlUp).Row + 1).Select
其中 1048576 是否因為超出 excel工作表列數 而出錯
作者:
joey0415
時間:
2015-1-29 12:29
回復
3#
chwqk
舊版改成
.Range("A" & .[A65535].End(xlUp).Row + 1).Select
作者:
chwqk
時間:
2015-1-29 20:21
謝謝 JOEY0415
已可以使用
但想了解
For xi = 116 To 118 Step 2
為何是 xi = 116 To 118
另外 Step 2 是甚麼意思 ??
作者:
chwqk
時間:
2015-6-18 00:25
請教最近sgx又改版
http://www.sgx.com/wps/portal/sgxweb/home/marketinfo/derivatives/Nlt
http://www.sgx.com/wps/portal/sgxweb_ch/home/marketinfo/derivatives/Nlt
抓不到資料
如何修正
作者:
joey0415
時間:
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
複製代碼
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
