如何高效讀寫（VBA）千餘個Excel文件？是否必須藉助其他工具？

01-08

我將上證A股的每一隻股票（共1219隻，見圖1）通過軟體的「數據導出」功能，形成數據文件，文件格式為Excel（見圖2）。
圖1：

圖2：
現在我新建一個Excel文件（文件名為analysis.xls），做一個控制項按鈕，用VBA編寫一小段代碼：
按文件名的順序，逐個股票數據文件（共1219個）打開，計算「10日均線」和「偏離率」（見圖3），將「偏離率」的正負兩個極值寫入analysis.xls中（見圖4），再把股票數據文件關閉。
圖3：

圖4：
我的問題是：
1219個股票文件，打開、計算、寫入至analysis.xls、關閉，耗時比較長。我如果再加入MACD、布林帶、KDJ等其他技術指標的計算，時間會更長，因此我想請教一下，在Excel下有沒有比較高效的方法來進行處理上千個文件的數據計算？如果在Excel下，這樣逐個文件做起來無法提高效率的話，是否可以通過Access或者SQL Server等其他工具，能夠高效的完成這項工作？

我做過很多個類似功能的vba，

比如讀取一年份的報表Excel數據，

並遍歷整個報表，將符合要求的數據寫入一個新的報表。

我公司電腦i5配置，運行365張表也就1分鐘左右。

具體說到1000多張表，

根據你的vba的代碼演算法和機器配置會有一點點運算速度上的區別。

我想應該也就最多運行10分鐘-20分鐘左右。

可以在寫的程序中加一個進度條，

或者設計userform中加入完成百分比。

就可以看到運行的進度了。

用timer也可以做一個計時，最後看看跑了多久。

手機碼字，將就著看看吧。

寫一下思路和大概可能會用到的vba函數。

sub getdata（）

t=timer

用fso.getfolder讀取地址，

然後外層的循環

for f in ff files遍歷同一個文件夾所有報表（最好加一個規則，比如instr函數判斷一下是否包含sh），

open打開，

讀取同名的sheet，

加一個max/min函數取極值

（這裡看自己需要，我覺得函數取極值比較快，

可能根據題主還有其他要求,那就具體情況具體分析了。）

將結果複製到統計的表格

然後close。

（關閉報表這步很重要，

如果忘記了，你的程序會同時打開1000多張報表。應該直接就死機了。

所以循環務必記得開一張再關閉一張的步驟）

t=timer-t

msgbox 「主子，奴才辦完了，一共用了」 t 「秒」

end sub

excel2016里的查詢（power query）就是專門用來干你說這種事情的，用起來比VBA舒服多了

我來回答一下這個問題吧

幾千個文件，數據量累計起來也是不少了，而且還需要計算，用excel 做沒問題，但是效率畢竟不如資料庫里搞速度快，不如換個思路

1、通過vba 程序將這幾千張表灌入到資料庫中，server 或者access 都行

2、通過資料庫存儲過程進行，處理計算，這回比vba 效率高很多，這樣日後也可以應用到其他系統平台比如Web，app

3、編寫vba 程序查詢顯示計算的結果，這塊你想怎麼搞都可以了。

時間上也會減少很多，我估計5分鐘內應該可以，當然看你vba 灌入的程序優化度，建議使用數組字典。

標準方案當然是用pandas啊

買性能更好的計算機。

優化vba代碼，例如：先把數據放數組或字典里，然後一次性寫入文件；不要打開文件直接讀取數據；使用dir（）和字典遍歷別用文件名一個個去讀；關閉自動運算和屏幕刷新；別用select增加操作。

暫時就想起這些?

明明有股票分析軟體可以直接使用。。。。。。。。。。沒必要浪費那個時間啊，有空看看我分享的文章吧。