• 74

(2/4小更新)使用VBA解決 excel web 查詢無法匯入、匯入太慢的股市資料

這篇技術文實在太讚了, 過去我用Query Request雖然簡單, 每天定期下載證券交易資料, 幾個月後Excel檔案膨脹好幾MB, 查了好幾天才知道原來Query Request會將Http的連結自動定義, 結果名稱管理員裏面上萬條都是這種無用的定義, 躲在後台嚴重拖慢了Excel的執行效率; 今天在版主21樓的詳細解說下, 改用XMLHTTP的方法, 速度快, 而且沒有Quesy Request的後遺症, 實在太棒了.
ashiliwang wrote:
Query Request會將Http的連結自動定義...(恕刪)


可參考麻辣論譠,那邊很多query table範例
刪連線數,可參考481樓有麻辣的連結
snare wrote:
可參考麻辣論譠,那...(恕刪)

剛剛試了一下, 真的, 就少了Delete的指令, 這麼簡單, 但是我看過很多使用Query的程式, 包含老外寫的, 都沒有Delete的指令. 只能說複製貼上太容易, 沒有讀書還是有差...
snare wrote:
簡單搜尋幾個網址,請參考...(恕刪)


(1)
1.
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON/parse
基本需要了解

2.
https://github.com/VBA-tools/VBA-JSON
這比較好懂,而且還有影片教學如何使用

3.
http://exceldevelopmentplatform.blogspot.com/2018/01/vba-parse-json-safer-with-jsonparse-and.html
https://raw.githubusercontent.com/douglascrockford/JSON-js/master/json2.js
有機會可以看懂,不過可能要花很多時間

4.
https://coderwall.com/p/pbxsyw/vba-web-requests
雖然不懂,不過有第二項連結就很好用了。



snare wrote:
JsonParse,應該說是function 名稱,您喜歡怎麼改都可以
document.aaa or document.bbb
您就把它理解成 vba 的function,只是我用JsonParse來命名...(恕刪)

(2)
雖然樓主說把它理解成VBA 的 Function,但
Jsondata.write ”<script>document.JsonParse=function (s) {return eval(’(’ + s + ’)’);}</script>”
不是應該是要理解成寫在HTML內的Javascript(有錯請更正)

因為很像javascript,還想改寫成比較常看到的型式,如下
Jsondata.write ""
但沒辦法執行,請問是否理解錯了。



snare wrote:
嗯~我猜,您大概是以為
1=>裡面的程式碼,會把結果輸出到=>2 吧...(恕刪)


樓主寫得 VBA 很清楚,所以理解與樓主一致,xmlhttp send 只是把原始碼文字下載過來而以
不過 CreateObject("HtmlFile") 竟然認得 eval 可以直接用。但竟然不認得 JSON.parse



snare wrote:
document. 這個我猜您可能也誤會了
那是java程式碼的語法...(恕刪)


我的認知是當作 Document Object Model,例如:
document.getElementById('xxxyyyzzz')

所以對於 document.JsonParse=function (s),就沒辦法理解。



snare wrote:
關於這2行取值的程式碼
我總算搞懂您在問什麼了...(恕刪)

對對對,就是想問這個。
因為直覺上就是想以這樣的型式寫
Debug.Print DecodeJson.msgArray["1"].ch

但不知道為什麼沒辦法執行示,所以其實沒google,由其
Debug.Print DecodeJson.queryTime.sysTime
這個寫法是成功的

感謝樓主提供詳細的說明

justinyutw wrote:
不是應該是要理解成寫在HTML內的Javascript(有錯請更正)
...(恕刪)


我是想說 .JsonParse,就像 vba 的 function 名稱一樣,可任意自訂

您理解是正確的,是我文字敘述不夠清楚


justinyutw wrote:
因為很像javascript,還想改寫成比較常看到的型式,如下
Jsondata.write ""
...(恕刪)


"" 空白 ??
var= ?? or other


大部份的javascript程式碼,vba都可以用
如果您對java有基本的認識,當然可以多試試其它的寫法


甚至用字典物件,配合htmlfile物件
CreateObject("Scripting.Dictionary")
CreateObject("htmlfile")
利用
htmlfile物件中的parentWindow.execScript
也可以做出功能相同、甚至更強大的javascript

不過,這裡是mobile01不是程式論譠,當範例還是簡單易懂一點的好
技術成份太高的,就不多做介紹了


justinyutw wrote:
CreateObject("HtmlFile") 竟然認得 eval 可以直接用。但竟然不認得 JSON.parse
...(恕刪)


其實還是可以的
但在不外掛api、lib...的情況下,且excel(32、64位元)通用的"純vba"
程式碼會變很複雜

不是靠寫程式吃飯的,不用了解到那麼深入
同上題,技術成份太高的,就不多做介紹


justinyutw wrote:
我的認知是當作 Document Object Model,例如:
document.getElementById('xxxyyyzzz')

所以對於 document.JsonParse=function (s),就沒辦法理解。

...(恕刪)



因為是vba+javascript,如果不是2種語言都會,大概會一頭霧水
不知這樣您是否看得懂


document.getElementById()
就像vba的
worksheetfunction.match()
是固定的程式語法

document.JsonParse
就像vba的自訂義funtction副程式
其中JsonParse,是我在javascript中自訂義function名稱

document.JsonParse=function (s)……… (整行)
是利用vba 中的 htmlfile物件,寫入javascript,做出一個名稱叫jsonparse的function


htmlfile物件的document屬性,詳細功能可參考
https://www.w3schools.com/jsref/dom_obj_document.asp

因為yahoo股市改版,表格位置變動,yahoo 股市相關範例無法正確執行

提示:
Set Table = HTMLsourcecode.all.tags("table")(?).Rows

請試著自行練習修改程式碼




謝謝Snare大的一系列分享,小弟默默爬了一陣子,覺得對於需要用VBA爬蟲的人來說真的有很大幫助!!

雖然目前只學會一些基本的爬蟲,但很感謝各高手在此分享與討論。

先前常用的雅虎的即時報價爬蟲忽然無法使用,但小弟才疏學淺無法解決,

想請問各位大大是否知道問題是什麼?

謝謝各位參考!!

(也順便提供檔案給各位)
------------------------------------------------------------------------------------------

程式碼如下:

Sub Z及時股價()

Dim myXML As Object
Set myXML = CreateObject("Microsoft.XMLHTTP")

Dim myHTML As Object
Set myHTML = CreateObject("HTMLFile")

On Error Resume Next
i = 2
With myXML

Do While Cells(2 + QQ, 1) <> ""

.Open "GET", "https://tw.stock.yahoo.com/q/q?t=" & Timer & "&s=" & Cells(2 + QQ, 1), False


'以下這3行避免抓到暫存資料
.setRequestHeader "Cache-Control", "no-cache"
.setRequestHeader "Pragma", "no-cache"
.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
.send

'把傳回值轉成標準htmlfile
myHTML.body[removed] = .responseText

Set myTable = myHTML.getElementsByTagName("table")(6)

For j = 1 To 10

Cells(i, j + 1) = myTable.Rows(1).Cells(j).innerText

Next

i = i + 1

'將股票代號導入CMoney超連結
Cells(2 + QQ, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"http://www.cmoney.tw/follow/channel/stock-" & Cells(2 + QQ, 1) & "?chart=d"

QQ = QQ + 1
Loop

End With

'釋放記憶體
Set myXML = Nothing
Set myHTML = Nothing

Var = MsgBox("更新完成", vbOKOnly, "訊息")

End Sub

附加壓縮檔: 201811/mobile01-c209e26e747e94b724897cfbf8a2780d.zip
"生命的價值不在於能活多少天,而在於我們如何利用這些日子。"

f006116 wrote:
謝謝Snare大的...(恕刪)



各位好!
感謝Snare大的提示,原來是網頁改版問題,目前問題已經解決~
再次謝謝!!
"生命的價值不在於能活多少天,而在於我們如何利用這些日子。"
不好意思版主 我想請問一下

我目前用您的編碼進行查閱

HTMLsourcecode.body.innerhtml = .responsetext
Set Table = HTMLsourcecode.all.tags("table")(8 - 1).Rows
For i = 0 To Table.Length - 1
For j = 0 To Table(i).Cells.Length - 1
ActiveSheet.Cells(i + 1, j + 1) = Table(i).Cells(j).innertext

我想請問一下 如果我用這些編碼查到了我要的特定資料的位置 在Item14 那這樣位置算得出來嗎?
clothk73713 wrote:
如果我用這些編碼查到了我要的特定資料的位置 在Item14 那這樣位置算得出來嗎?...(恕刪)



請參考21樓的說明
如果不用迴圈,只拿某一格的資料
直接指定列、欄的位置即可
提示:只要張數那一格的數字
Cells(1, 1) = Table(1).Cells(6).innertext



  • 74
限制級
您即將進入之討論頁 需滿18歲 方可瀏覽。
根據「電腦網路內容分級處理辦法」修正條文第六條第三款規定,已於該限制級網頁,依台灣網站分級推廣基金會規定作標示。
評分
複製連結
請輸入您要前往的頁數(1 ~ 74)