(不定期更新)使用VBA解決 excel web 查詢無法匯入、匯入太慢的股市資料


snare wrote:
通常來說就是拿資料那個網頁的上一個網頁(一般都是首頁)...(恕刪)

樓主您不但是程式功力深厚,還是個不吝分享及回應問題的高高手,並能站在入門者的角度看問題及適時回應問題,謝謝

snare wrote:
javascript寫到何處=>一個在記憶體中的htmlfile物件...(恕刪)

小弟看了您有關json解析運用…function (s)…. eval('(' + s + ')')這種vba程式的寫法,似乎與網路上有關javascript 自訂函數內運用eval()的寫法有些差異,因不懂,只能死記運用,不過,小弟在google有關eval()解析json字集過程,有許多網站資料均表示eval()解析json可能會產生安全疑慮,建議改用json.parse(),請問樓主eval函數真的不安全嗎?

snare wrote:
callbyname這個特殊函數,剛好可以跳過這個限制...(恕刪)

Callbyname這個函數真的很特殊難懂,小弟會努力去認識它


小弟因不懂Javascript及被callbyname打敗,就嚐試以最單純的切,切,切方式來解析json,雖然最後是順
利切出結果,但過程產生一些問題,請樓主有空看一下,小弟觀念錯誤在那? 完整的程式碼在最後
(1)即時運算視窗程式碼Debug.Print HTMLsourcecode.body.innerhtml 如.圖1
(2)儲存格A1資料 Cells(1, 1) = HTMLsourcecode.body.innerhtml ,如圖 2.
(3). 剪貼簿程式碼功能,如圖 3.





問題:
1.為何(1) 與( 2) 的內容不同,而且是json字集的前段與後段,中間段資料卻不見了?
2.使用剪貼簿功能所產生的資料才是完整的json字集,但(1)+(2)卻不等於(3)?
3.此程式碼不是從無到有,是在透過樓主585樓範例知道結果後,作json字集內容檢視後,以split方式解析json,請教樓主該程式的split方式是否正確?

################### 程式碼 ###################
Sub getbic()

Dim str As Variant, str2 As Variant, r&, n&, Clipboard As Object
Dim HTMLsourcecode, Url, Url_a
Set HTMLsourcecode = CreateObject("htmlfile")
Set Clipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
ttt = Timer
Cells.Clear
Url = "https://index.ndc.gov.tw/n/json/data/eco/indicators"
Url_a = "https://index.ndc.gov.tw/n/zh_tw/data/eco"
On Error Resume Next
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "POST", Url, False
' .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.setRequestHeader "Referer", Url_a
.send
' HTMLsourcecode.body.innerhtml = .responsetext
str = Split(.responsetext, """code"":")
End With
' Debug.Print HTMLsourcecode.body.innerhtml '(1)即時運算視窗

' Cells(1, 1) = HTMLsourcecode.body.innerhtml '(2)儲存格A1資料
' With Clipboard
' .SetText HTMLsourcecode.body.innerhtml '(3)剪貼簿功能資料
' .PutInClipboard
' End With

' With Sheets("sheet2")
' .Cells(1, 1).Select
' .PasteSpecial NoHTMLFormatting:=True
' End With
str2 = Split(str(2), "{""id"":")
n = UBound(str2)
For r = 1 To n
Cells(r, 1) = Split(Split(str2(r), "x"":""")(1), """")(0)
If Split(Split(str2(r), "y"":")(1), "}")(0) = "null" Then
Cells(r, 2) = ""
Else
Cells(r, 2) = Split(Split(str2(r), "y"":")(1), "}")(0)
End If
Next r
MsgBox "使用時間" & Round(Timer - ttt, 2) & "秒", vbOKOnly, "下載完成"
Set HTMLsourcecode = Nothing
Set Clipboard = Nothing
End Sub
activer wrote:
小弟看了您有關json解析運用…function (s)…. eval('(' + s + ')')這種vba程式的寫法,似乎與網路上有關javascript 自訂函數內運用eval()的寫法有些差異
...(恕刪)


是的,不一樣
網路上的都是excel 32位元的寫法(同方法64位元要用,需另外加一堆程式碼)
我的寫法是32、64通用

activer wrote:
因不懂,只能死記運用
...(恕刪)

這個寫法是固定的,您就當在背excel函數就好,index()、match()…


activer wrote:
小弟在google有關eval()解析json字集過程,有許多網站資料均表示eval()解析json可能會產生安全疑慮,建議改用json.parse(),請問樓主eval函數真的不安全嗎?
...(恕刪)


json.parse() 只能用在excel 32位元,在64位元中需另外寫程式碼輔助才能用

eval有安全性問題,是的,因為有可能“順便”執行到惡意程式碼
但是只有拿去用在“不安全”的網站,才有可能不安全

如果真的怕的話,在使用eval()之前,先人工看過一次原始json集合字串
看看是否有出現“不是”json格式的程式碼在裡面
json格式是固定的,有奇怪的東西,很容易一眼就看出來

股市資料這種大型網站,不能不安全,安全性沒問題的,不用擔心


activer wrote:
問題:
1.為何(1) 與( 2) 的內容不同,而且是json字集的前段與後段,中間段資料卻不見了?
2.使用剪貼簿功能所產生的資料才是完整的json字集,但(1)+(2)卻不等於(3)?
3.此程式碼不是從無到有,是在透過樓主585樓範例知道結果後,作json字集內容檢視後,以split方式解析json,請教樓主該程式的split方式是否正確?
...(恕刪)


一、即時除錯視窗有字數限制,超過無法顯示,有時也有亂碼問題(忘了那一樓有提到過)
(自己試看看吧,上限199行)
Sub test()
For i = 200 To 1 Step -1
Debug.Print i
Next i
End Sub


二、同一 + (excel單一儲存格也有最大字數限制),而且剪貼簿,"不是只貼在一格內"
(請參考微軟說明)
https://support.office.com/zh-tw/article/excel-%E7%9A%84%E8%A6%8F%E6%A0%BC%E5%8F%8A%E9%99%90%E5%88%B6-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Office_2007

三、正確,只要資料不多,您想用mid()、left()…等等函數來處理也可以
我有些範例也是用split()來拆json字串的

json 說穿了,就是一大串文字而己,處理方式很多的

樓主你好

先謝謝你無私的奉獻
今早在偶然下看到你的這篇PO文
也 Download 了 449樓的檔案
一開始想改在 delphi 上
但由於我那個年代並不興網路抓取資料的技術
加上對 Delphi 上 idHttp 完全没使用過
嘗試許久都未能成功
只好乖乖的上Youtube 學excel 和 VBA 在你的程式上更改
經過一天的努力還是勉強改成可以抓取全部的資料

不過畢竟年輕時較熟悉的工具是 Delphi (退休不寫程式已有7年)
所以我是想把它改到 Delphi 上

這邊想請教你
以下這段如果要用要用 delphi 的 idHttp
With GetXml
.Open "POST", "https://www.tdcc.com.tw/smWeb/QryStockAjax.do", False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.setRequestHeader "Referer", "http://www.tdcc.com.tw/smWeb/QryStock.jsp"
.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
.send url_a

應該如何處理 ?
再次謝謝樓主
今早不死心
再上網爬文
找到 delphi 上 WinHttp 的 LIB
測試後一切都OK


snare wrote:
一、即時除錯視窗有字數限制,超過無法顯示,有時也有亂碼問題(忘了那一樓有提到過)
(自己試看看吧,上限199行)...(恕刪)

謝謝樓主指出使用工具的基本限制及提供參酌資料

小弟記得樓主曾自述專精組合語言設計,所以有個題外問題想請教樓主,若經windows 7內建壓縮軟體壓縮過的設密檔案,但密碼忘記,因網路資料幾乎都是針對winzip第三方軟體的方式,樓主是否有適用的方式可供參考?
謝謝snare師傅 ~ 小弟我先收下了 ~ 等回到家裡再慢慢學習~
祝您假期愉快~一切順心 !

activer wrote:
若經windows 7內建壓縮軟體壓縮過的設密檔案,但密碼忘記,因網路資料幾乎都是針對winzip第三方軟體的方式,樓主是否有適用的方式可供參考?...(恕刪)


windows 7內建壓縮是 zip 格式,沒有加密功能

您說的加密大概是

BitLocker 加密

efs 加密

BitLocker 請google "BitLocker 忘記密碼"
efs 請google "Advanced EFS Data Recovery"

但前提是,密鑰要找的到


樓主我的程式要抓market information system的資料,如下圖



但我的程式只能抓到標題第一列第一列資料如下:
最近成交價/漲跌價差/當盤成交量…
其它資料皆抓不到,可否看看我的程式是哪有問題,謝謝。
'=============================================================================
Sub test()

Cells.Clear

Dim myXML As Object
Set myXML = CreateObject("WinHttp.WinHttpRequest.5.1")

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

With myXML
.Open "GET", "http://mis.twse.com.tw/stock/group.jsp?ind=01&ex=tse&currPage=0&type=all"
.send
myText = .responseText

i = 1

myHTML.body[removed] = .responseText
Set myTable = myHTML.getElementsByTagName("table")(1)

For Each myrow In myTable.Rows

j = 1

For Each mycell In myrow.Cells

Cells(i, j) = mycell.innerText
j = j + 1

Next

i = i + 1

Next

End With

Set myXML = Nothing
Set myHTML = Nothing

End Sub

'=============================================================================






看到樓主這篇文章
重新燃起寫程式熱誠
不過我是移到Delphi 上作業
雖然工具不同
但道理是相通的
希望跟大家多交流
這邊貼上我的作法
我是先做一個 Get 公用工具
只要輸入 Url 即可
我把 ResponseText 中的Table 丟到TreeView 分析
再目視選擇要的資料
再把選到的資料丟到另一個Memo 做為Coding 用的資料
順手跑了一下樓上要的
也是抓不到那邊的資料
我想那個是java的資料
不知樓主有没有解 ?





kadyysc5044 wrote:
樓主我的程式要抓market...(恕刪)


您好,基本市況資料,樓主在165樓-170樓曾說明及提供範例程式供下載,您不仿前往看看
關閉廣告
文章分享
評分
評分
複製連結
請輸入您要前往的頁數(1 ~ 143)

今日熱門文章 網友點擊推薦!