在Excel中應用VBA大量匯入資料

《Excel與VBA程式設計》最新訊息，預計9月上市

  

1.       問題由來

當一個漂亮MM向你請教如何錄製並修改一個宏，把她每次的實驗資料（幾十個資料檔案）匯入Excel時，你感慨道：“很多Excel專家會錄製一個宏來解決問題，然後每次使用的時候修改代碼並粘貼到需要的地方，對於一個合格的程式員，這是最要命的事情。”

漂亮MM打斷並告訴你，她不是程式員，也不想做程式員，然後命令你開始工作。

2.       通過錄製宏匯入資料

對於這個無法拒絕的MM，你只好垂頭喪氣的開始面對要解決的問題，想著MM幾年後博士畢業，年薪至少5萬，幹個3、5年，年薪10萬，還有項目提成，平時吃飯、打車、買可樂都可以報銷，當然不會像你放棄了自己的專業，做了一個為生計奔波的程式員。

資料檔案是儀器產生一系列文字格式設定的資料檔案，格式完全一樣，目的是要把每個資料檔案匯入到Excel中作為一條記錄，也就是一行。那麼，你想，可以用VBA寫一個程式，然後定位到需要的位置，讀入需要的資訊就可以了[①]。你開啟Excel，開啟VBA編輯器，準備開始寫代碼。

“開始錄宏吧”，MM提醒了心不在焉的你，你沉默了0.1秒，默念了一下VBA的信條：“萬不得已不要寫代碼，盡量使用Office的功能”。於是你啟動Excel開啟這個文字檔，按照彈出的文字檔匯入嚮導對話方塊的步驟，使用固定列寬匯入了需要的資料。資料包括2部分，第一部分是檔案頭，包括一些資料資訊，後面是按行放置的資料，包括結果和誤差，MM要的是後邊的資料，要把每行的資料和誤差放置到相鄰的兩列（見）。

圖 1資料檔案部分和需要在Excel中的結果資料

 

明白了問題，一切就好辦了，開啟Excel，然後開始錄製宏：首先開啟檔案，通過匯入文字檔嚮導，讀入資料，將特定儲存格的資料拷貝到一個目標Excel檔案中，然後關閉這個文字檔，停止錄製宏。

錄製的宏很長，大概包括2部分。第一部分是一句開啟檔案，格式轉換的操作，後邊一部分是啟用不同的檔案，拷貝和粘貼不同的Range。你刪除掉剛才拷貝進來的資料，運行了一下這個宏，很好，需要的資料進來了。

3.       修改宏匯入成批資料

MM提醒你，這個她也能做到，但怎麼樣把所有的資料檔案都匯入進來。你看看她帶來的檔案，檔案名稱是“r20041124001357.txt”、“r20041124001358.txt”、“r20041124001359.txt”、“r20041124001360.txt”之類，大概是時間加序列吧。

你想，嗯，寫一個迴圈就可以了，你開啟了剛才錄製的宏，檢查了一下MM帶來的檔案，檔案名稱最後2位從46到89，你可以寫一個i從1到44的迴圈，把讀入檔案部分的檔案名稱改為：

"r200411240013" & ( i + 45 ) & ".txt"

把粘貼目的地（range）表示行數的數字用i替換。

OK，你按下了執行按鈕，每次關閉檔案的時候，有一個討厭的是否儲存檔案的對話方塊跳出來，其他好像一切正常，還好，點擊了44次滑鼠後[②]，MM得到了需要的資料。

4.       修改VBA代碼實現一個可通用的宏4.1.    指定要匯入的檔案

當MM向你請教如何更改迴圈以匯入不同的檔案的資料時，你程式員的劣根性又開始衝動，你想通過一個開啟檔案對話方塊來指定需要的檔案。你覺得實現應該不複雜，通過一個開啟檔案對話方塊，選擇一系列檔案，然後將檔案全路徑存入一個集合或數組，然後迴圈讀出這些檔案就可以了。

你先建立了一個表單，然後放置了一個按鈕，將CommonDialog控制項引入工程，添加到表單，在按鈕的點擊事件裡加入如下代碼：

 

    Dim strFiles As String, i As Long

   

    With CommonDialog1

        .Flags = &H200& Or &H80000    '可以選擇多個檔案

        .ShowOpen

       

        If .FileName <> "" Then

            strFiles = .FileName

        End If

    End With

 

    '分割傳回值，傳回值為以ASCII碼為0的分割的字串

       '字串第一個為路徑，之後為單個檔案名稱

    Files = Split(strFiles, Chr(0))             

    For i = 1 To UBound(Files) Step 1

        Files(i) = Files(0) & "\" & Files(i)              '串連路徑和檔案名稱，組成檔案數組

    Next i

 

代碼不多，最後的檔案清單儲存在Files數組裡。因為第一次使用CommonDialog控制項開啟多個檔案，尋找出多個檔案的分割符號是ASCII碼為0的字元費了你不少時間。你開始查了文檔，沒有得到資訊；將FileName屬性用Msgbox輸出只有路徑，在調試狀態跟蹤時是一個怪字元分割的；你開始想想應該是Tab或者斷行符號之類的，然後使用這些字元用Split函數分割，沒有成功；只好測試了，你將所有字元使用ASC函數輸出，發現原來是ASCII碼為0的字元。你想，微軟的文檔向來不錯，為什麼這個在協助裡沒有呢？

後面的部分就簡單了。

 

For i = 1 To UBound(Files) Step 1

    strFilename = Files(i)

       DoImport strFilename

Next i

 

把原來的宏修改後儲存在DoImport這個過程裡，傳入檔案名稱即可匯入這個檔案，迴圈匯入所有檔案就可以了。雖然程式功能複雜了，但代碼似乎要有條理了。

4.2.    指定要匯入的位置

聰明絕頂的MM很高興，馬上又舉一反三，提出應該可以指定從第幾行開始匯入。你腦子轉了一下，認為這個需求屬於合理需求[③]，不能不予理會。

給表單加一個RefEdit，點擊開始的地區後返回的將是一個引用位置的字串，使用Range函數得到該地區的引用對象（Range對象），然後就可以得到其開始行數：

Range(Me.RefEdit1.Value).Row

重構一下DoImport這個過程，增加一個mRow參數，將匯入的資料全部寫到第mRow行。上面的調用過程就變成了：

 

dim mRow as long

mRow = Range(Me.RefEdit1.Value).Row

For i = 1 To UBound(Files) Step 1

    strFilename = Files(i)

       DoImport strFilename, mRow

       mRow = mRow + 1

Next i

 

你終於鬆了一口氣，臉上又浮現出了賊賊的笑容。MM也答應要請你吃飯，不過你知道兌現的可能性不大，最後還可能是MM請客你掏錢，不過不要緊，程式員的最起碼的風度和尊嚴還是要維護的。

4.3.    修改匯入規則

你忽然覺得靈感一現，甚至想做一個可以匯入各種格式檔案的通用模組，然後作為載入宏發布，於是很多實驗室都開始用你的程式，你開始狂收註冊費，Gates也坐不住了，要把你的程式買了OEM在Excel裡……

這時，呆在一旁的MM敲了一下你的頭，把手舞足蹈的你拉回了現實。

5.       總結

晚上，你開啟了日誌，寫道“替Girl friend MM解決問題一個”。

你想，今天的問題很簡單，不過，解決的問題好像很管用，這大概就是所謂的VBA之道吧，以前似乎見過一個VB之道的文章，你決定回頭也寫一個VBA之道。那麼，第一條應該是：

錄製宏，但要修改它！

 

 

（2004-11-24 淩晨）

[①] 這是很多程式員的通病，喜歡從輪子造起，你也一樣。

[②] 你知道有一個Sendkeys語句，可以類比鍵盤操作關閉這個對話方塊，不過解決主要問題先。

[③] 你認為合理需求應該滿足以下兩條定律：第一是可以實現，第二是客戶提出的。