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

snare

3127分

樓主

snare

個人積分：3127分

文章編號：64283791

bioleon69 wrote:
所以可以在每一個sub裡面的最後都 set = nothing 釋放?

這樣對於運行速度是否有影響?
...(恕刪)

Object 類型用set nothing
array 類型用 erase
不影響速度

但嚴格來說set nothing並沒有完全釋放記憶體
想知道更多請google
主要還是要看程式怎麼寫，不是用了就有效

bioleon69 wrote:
(並不是您說的沒什麼用)
...(恕刪)

補上測試結果(單位kB)
測試方式，打開excel跑程式，關掉excel再打開，重覆10次(合計60次)，取平均值
測試網頁(共30個表格)
http://mops.twse.com.tw/nas/t21/sii/t21sc03_106_2_0.html
一、程式第一行記憶體使用量
(這是excel分配的工作記憶體，非程式執行中的變化)
二、程式最後一行記憶體使用量(因還沒結束，所以會偏高)
三、程式中間所有nothing"前"記憶體使用量
四、程式中間所有nothing"後"記憶體使用量
五、編碼轉換中“有”nothing
六、編碼轉換中“無”nothing

執行中記憶體變化(扣掉第一行初始記憶體)

我的舊寫法不會有問題，您不信我也沒辦法
改成新寫法，是因為先 dim 再 set ，然後 set nothing是“標準寫法”
反正大部份都是“複製貼上”，多少了解一些“標準寫法”也好
我是因為程式寫久了，程式能簡化就簡化
常常用一些別人沒想到的方法

bioleon69

63分

42樓

bioleon69

個人積分：63分

文章編號：64289499

snare wrote:
補上測試結果(單位k...(恕刪)

這樣看起來確實好像速度沒差
不過師傅說這是正統寫法的話那應該有它的寫法道理在
那我以後也盡量用這招感謝分享~~哈
辛苦了!

bioleon69

63分

43樓

bioleon69

個人積分：63分

文章編號：64306077

師傅來請教惹

以此連結為例子

http://www.tpex.org.tw/web/bond/publish/publish_download/download.php?l=zh-tw
目前我的方法
手動下載csv,再從excel打開處理資料 (如圖,自動就是EXCEL的格式,妥妥的)

用QT匯入的方法呢
會變成這個樣子.. (各種標點符號,也不是EXCEL的格式)

受了師父影響
現在,想全部自動化,不想多了手動的STEP

QT匯入要怎麼樣才不會有一堆標點符號
感謝師傅!

bioleon69

63分

44樓

bioleon69

個人積分：63分

文章編號：64306397

bioleon69 wrote:
師傅來請教惹以此連...(恕刪)

問題已解決
15151515151511515151515151515

雖然解決了,但是是用QT...
也就是師傅不用的方法

還是想了解
不知道對於這種狀況,師傅xml的處理方法是如何處理~~

snare

3127分

樓主

snare

個人積分：3127分

文章編號：64314740

bioleon69 wrote:
雖然解決了,但是是用QT...
...(恕刪)

query table 下載csv，可用一行代替
但速度和query table一樣，不穩定2秒~3分鐘

反正一樣慢，我寧願用這個代替
Workbooks.Open Filename:="http://www.tpex.org.tw/storage/bond_publish/ISSBD.csv", Format:=2 '

bioleon69 wrote:
xml的處理方法是如何處理~~...(恕刪)

自己加到範例裡面吧，陣列範圍60是隨便設定的，變數名稱也是隨便命名的
不確定夠不夠用，懶得測試
我實在是沒興趣一直在搞這個我平常都不用的功能

Dim csvrow As Variant, csvcol As Variant, csvarray As Variant, csvstring As String
csvstring = convertraw(.ResponseBody)
csvrow = Split(csvstring, vbNewLine)
ReDim csvarray(UBound(csvrow), 60)
For i = 0 To UBound(csvrow)
csvcol = Split(csvrow(i), ",")
j = 0
For Each csvc In csvcol
csvarray(i, j) = Replace(csvc, """", "")
j = j + 1
Next
Next
Range("a1:bh" & UBound(csvrow)) = csvarray

5/29 補充，如果csv檔內有 " = "(等於)這個符號，需特別處理
excel 會把它當成公式，沒辦法正確填入儲存格

bioleon69

63分

46樓

bioleon69

個人積分：63分

文章編號：64327268

snare wrote:
自己加到範例裡面吧，...(恕刪)

哇...xml這部分似乎稍微有複雜到

在請問師傅一下
如果用QT,寫了4個SUB,每一個SUB都匯入一筆外部資料
所以一次執行4個SUB就有四筆外部資料

要一次把這四筆外部資料刪除怎麼寫呢?
這外部資料很佔空間
有些編排後,還是有空白的格子會有外部資料導致excel存檔後檔案變大
還請師傅指導
謝謝

snare

3127分

樓主

snare

個人積分：3127分

文章編號：64327604

bioleon69 wrote:
如果用QT,寫了4個SUB,每一個SUB都匯入一筆外部資料
所以一次執行4個SUB就有四筆外部資料
...(恕刪)

query table 請勿“同一時間”使用太多
同時執行 sub1 + sub2 + sub3 + sub4，後再清除記憶體
程式沒寫好的話容易出現記憶體不足
就算您sub設計精良不會有問題
也會造成多4倍程式碼的缺點，小程式還沒問題
大程式就維護困難

同功能建議改用迴圈
執行完sub後立刻清除記憶體，記憶體使用量剩1/4
(前提是sub有設計好)
for i=1 to 4
...
call sub
...
next

請參考1樓、26樓範例，其它不解釋
老話一句，請勿越級打怪，建議慢慢升級

bioleon69 wrote:
有些編排後,還是有空白的格子會有外部資料導致excel存檔後檔案變大
...(恕刪)

大部份問題，請先google後再發問
google
excel 多餘儲存格
excel 刪除空白

bioleon69

63分

48樓

bioleon69

個人積分：63分

文章編號：64363081

snare wrote:
大部份問題，請先go...(恕刪)

問題搞定了,在網路有找到一段
直接套用即可

Sub 刪除多餘()
Dim a, b, c, d, e
Set a = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not a Is Nothing Then
e = a.Offset(1).Address
b = ActiveSheet.UsedRange.Rows.Count
c = "d" & b
Range(e, c).EntireRow.Delete
End If
ActiveSheet.UsedRange
End Sub

師傅

怎麼把那個轉碼程式帶進來?
這是同樣的情況嗎?

Sub 集保() '集保人數
Set book1 = ActiveSheet
Set bookshow = book1.QueryTables _
.Add(Connection:="TEXT;https://smart.tdcc.com.tw/opendata/getOD.ashx?id=2-8", _
Destination:=book1.Range("a1"))
With bookshow

.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
End Sub

snare

3127分

樓主

snare

個人積分：3127分

文章編號：64371792

bioleon69 wrote:
怎麼把那個轉碼程式帶進來?...(恕刪)

bioleon69 wrote:
問題已解決
15151515151511515151515151515
雖然解決了,但是是用QT......(恕刪)

所以您其實沒用query table 解決???
query table 可自訂編碼，不需要轉碼

而且，這個網站的csv編碼，excel可正確判斷，本來就不用轉
https://smart.tdcc.com.tw/opendata/getOD.ashx?id=2-8
如果要用xmlhttp下載，請合併 45樓 的“部份程式碼”，加到21樓的範例裡面即可
不需要用26樓的轉碼範例

如果解決的話，您會不知道query table要多加一行嗎?
.TextFilePlatform = 65001

您那個集保()，可以用下面這一行代替，速度是一樣的
Workbooks.Open Filename:="https://smart.tdcc.com.tw/opendata/getOD.ashx?id=2-8", Format:=2

bioleon69

63分

50樓

bioleon69

個人積分：63分

文章編號：64373054

snare wrote:
所以您其實沒用query...(恕刪)

首先感謝師傅XML的教學方法
xml部分,直接套用就可以使用了!(45樓是關鍵)
不過程式碼真的超出理解範圍= =...
所以想請教簡單的方法....(還是很感謝師傅提供XML的寫法!!)

Workbooks.Open Filename:="https://smart.tdcc.com.tw/opendata/getOD.ashx?id=2-8", Format:=2
這個雖然是可以實現,但是會另外開了一個workbooks
要如何可以在同樣的工作表裡?

--------------------------------------------------------

之前提到解決亂碼的過程
順便提供給以後跟我一樣有需求的新手參考
只不過就改幾行而已,就可以搞死人= =

Sub 下載CSV() '債券發行資料
Set book1 = ActiveSheet
Set bookshow = book1.QueryTables _
.Add(Connection:="TEXT;http://www.tpex.org.tw/storage/bond_publish/ISSBD5.csv", _
Destination:=book1.Range("A1"))
With bookshow
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
End Sub

我將 Connection:= url 改成text
再改兩個參數就可以直接匯入,也沒亂碼了