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

Snare版主大大:

我的程式又發生了1126樓的相同問題(上回推測可能是假日網站維護),

再麻煩請您幫我看看,有沒有什麼比較穩定的方式,可以正常下載,感謝

錯誤訊息如下,程式附件在1126樓。Dylan

Dylan67 wrote:
我的程式又發生了1126樓的相同問題(上回推測可能是假日網站維護)


連續測試30次正常

(點我看大圖)



您圖片中停在=>沒有設定with區塊…原因很多
您可以在出錯時,按下偵錯,看看程式碼是中斷在那一行

這時不要結束程式,直接在即時運算視窗輸入(enter)
?myHTML.body.innertext

看看網站回傳了什麼訊息


如果是不定期發生,也可加入重試功能,讓程式自動重新下載
相關範例,可參考773樓 Sub Get_xml() 副程式的寫法
相同的程式,這次沒這麼順利,我的還是錯誤了,

有沒有可能跟我在大陸工作,使用翻牆下載有關,

程式停止在這行代碼:RowCount = myTable.Rows.Length,

我擷取了部分訊息,看看有沒有機會借助您的經驗判斷,感謝。

(1) 即時運算視窗:?myHTML.body.innertext

??⊥??瑁?
THE PAGE CANNOT BE ACCESSED!

?摰?扯€?嚗?€?瑁????Y瘜??橘?隢??€汗?典???岫嚗??舐窗摰X??隤支誨蝣潛嚗/A
FOR SECURITY REASONS, THIS PAGE CAN NOT BE ACCESSED!

?典隞?A HREF="history.back()">??銝€??/A>銝衣匱蝥隞?雿???誑銝閰梢€??嚗?02)8101-5932, (02)8101-5955??BR> YOU COULD GO BACK TO PREVIOUS PAGE OR CALL US FOR FURTHER HELP: (02)8101-5932, (02)8101-5955.

(2) xmlhttp.ResponseText

: ResponseText : "

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">


頁面無法執行

THE PAGE CANNOT BE ACCESSED!



因為安全性考量,您所執"

Dylan67 wrote:
有沒有可能跟我在大陸工作,使用翻牆下載有關,
程式停止在這行代碼:RowCount = myTable.Rows.Length,


因為語系的關係,您那堆亂碼,大意就是英文寫的禁止存取

程式碼停在那行是因為網站禁止存取,網頁中沒資料(表格),所以出錯
twse有流量限制,而翻牆用的代理本來就很容易被擋,無解
但這只是暫時的,稍後下載就行

您可以用773樓on error的寫法跳過錯誤,或是在
myHTML.body.innerhtml = convertraw(.responsebody)
下面加上if 暫時中斷程式、停止下載

if instr(myHTML.body.innertext,"THE PAGE CANNOT BE ACCESSED!") >0 then
exit sub
end if

或是改用遠端搖控台灣家中的電腦開excel下載
如果家中的ip分享器有VPN+NAT功能
也可自建私人的vpn server連線,連電腦都不用開
Dylan67
謝謝版主的回覆,有您真好,因為數據量不是很大,網頁又還可以開,我今天先用QueryTable方法解決了,就是有點不太甘心,Google不到其他有用的訊息,找不到突破口,只好暫時先頂著用了。
snare
不能下載的同時,網頁還可以開?那可能是您不只有對twse下載這個網頁?觸發了防爬蟲?可用773樓自動重新下載,順便補上.setRequestHeader "User-Agent",…模擬瀏覽器版本試試
Snare版主大神:
附件是VBA讀取2個不同庫的測試速度(程式碼幾乎一致),雖說都不足1秒,但差距4倍,
我嘗試過以下功效甚微的作法:
1. Set → Open 語法
2. InnoDB改MyISAM存储引擎
Google+百度,發現多是指向MySQL ODBC 或 my.ini等環境設置的問題,
現在很傷腦筋讓我猶豫的是:
1. 原有Access庫雖然已拆分庫並加上了壓縮程序,可是部分庫又快突破2G上限
(好奇怪,怎麼一下子2G就花完了)
2. 若是一舉改成MySQL庫,光讀就這麼慢,那寫入不就更慢
(我完整的程序及數據在Access原庫上就要跑個15分鐘)
故想請您給我點指導及方向,怎麼順利轉換到MySQL庫上,拜謝。Dylan

[點擊下載]
snare
您那個trr=rs.getrows是要在陣列裡面計算,還是讀到陣列後,再放入excel表格,如果不計算只是要放入表格,可以用這種方式Cells(1, 1).CopyFromRecordset RS
差點忘了,附上我的my.ini提供更多線索,我不太敢亂改,就只加上了編碼語句及開放文件存取權。Dylan

[點擊下載]
Dylan67 wrote:
故想請您給我點指導及方向,怎麼順利轉換到MySQL庫上


access 轉 mysql ? (搞錯了,這是sql server用的)
微軟有提供SSMA程式直接轉
https://support.microsoft.com/zh-tw/office/將-access-資料庫移轉至-sql-server-7bac0438-498a-4f53-b17b-cc22fc42c979

SQL Server 移轉小幫手
https://docs.microsoft.com/zh-tw/sql/ssma/sql-server-migration-assistant?view=sql-server-ver15

vba 中的 mysql 還沒試,因為要先灌visual stidio軟體好幾十G
才能裝mysql-installer-community-8.0.27.0
有空再試


20211108 10:43補充
本想試試您的mysql,但裝完mysql之後
我休閒用的遊戲馬上就出問題…
遊戲內的商店打不開,登出後無法登入



反覆測試了幾次visual stidio(自訂套件)+mysql,只要一裝遊戲就壞,解除就正常
目前試出visual stidio(基本安裝)+mysql正常
不好意思,暫時放棄測試您的mysql,休閒第一、回答問題第2



202111109 20:30補充…
Dylan67 wrote:
附件是VBA讀取2個不同庫的測試速度(程式碼幾乎一致),雖說都不足1秒,但差距4倍,


稍微玩了一下,測試的結果是access比較快
這種資料量沒辦法發揮出mysql的特長

您完整的程序15分鐘中,能分的出讀取、計算,各佔多少時間嗎?
也許改善計算的程式碼,可以比改善讀取方式,還增加更多的效能
Dylan67
謝謝Snare,這兩天把my.ini檔翻成中文後,我才發現調教MySQL環境,這簡直是門藝術,對您也挺不好意思的,問了個不容易聚焦的問題,有篇文章說JET比ODBC少了一層,我想應該是這個問題...
Dylan67
我確實把所有的運算集中在vba,庫只拿來存儲,少查找,我試過,在access庫查找後把結果返回,不如select * from搬回vba運算,您說的對,這種組合沒法顯出mysql的特長...
請教snare大神, 這應該是個很笨的問題~~~XD

我抓取以下網站
https://mops.twse.com.tw/mops/web/ajax_t163sb19?encodeURIComponent=1&step=1&firstin=1&TYPEK=sii&code=&year=110&season=01

抓到1個table的資料後,就無法繼續往下抓了
想過用for loop, 但也是會中途斷掉~~~
我是用71樓temparray的方法~~

還有想到一個方法就是把網址的code= 一個一個跑過一遍去抓每個分類
不過這個方法好像太笨了~~XD

請問snare大神 該如何解決這類的問題


謝謝
rainbowsperm wrote:
https://mops.twse.com.tw/mops/web/ajax_t163sb19?encodeURIComponent=1&step=1&firstin=1&TYPEK=sii&code=&year=110&season=01

抓到1個table的資料後,就無法繼續往下抓了
想過用for loop, 但也是會中途斷掉~~~


這個網頁,不建議用另存csv的方式,因為網頁開著不理它
過一下再下載,按下另存csv按鈕時,常常出現檔案不存在
直接抓網頁表格就好





請參考1044樓範例




主要需修改幾個地方,其它小地方請自行處理
一、post 改成 get
二、不需要 Referer
三、…5.1… 改成 CreateObject("msxml2.xmlhttp")
四、改用 .responsetext
五、使用逐格寫入方式,加上for ... next 迴圈

For i = 0 To HTMLsourcecode.all.tags("table").Length - 1
Set Table = HTMLsourcecode.all.tags("table")(i).Rows
Call Cell_by_Cell("工作表1", Table)
Next i

六、如果要用陣列寫入,請自行修改Cell_by_Cell()副程式

snare wrote:
這個網頁,不建議用另(恕刪)


謝謝snare大神, 已成功
不過看來該換台電腦了
我的時間是您的4倍多 哈哈
關閉廣告
文章分享
評分
評分
複製連結
請輸入您要前往的頁數(1 ~ 143)

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