將多個 Excel 活頁簿 (Workbook) 合併 為一個

31

最近有格友在問,有沒有將多個 Excel 活頁簿 (Workbook) 合併 成一份的 VBA 程式碼 ?
嗯 ~ 關於這個問題,在那當時果編手邊還真的沒有現成的程式碼可提供 ●﹏●

Excel 活頁簿 (Workbook) 合併

但格友的內心呼喊 ~ 果編可是有記在心裏面,也有列成工作項喔 (*・ω< )
最近果編終於找到了點時間,所以趕緊先弄了支簡易版的程式碼提供給有這樣需求的人參考

Excel 活頁簿 (Workbook) 合併

操作步驟

  1. 首先將要進行合併的 Excel 活頁簿 (Workbook),統統的放進去 D:\temp 資料夾下面,如上圖所示
  2. 開啟 EXCEL 應用程式,並新增一個空白活頁簿(合併後的內容會放在這本活頁簿中)
  3. 接著按下快鍵盤快速鍵 ALT + F11,接著會開啟 Microsoft Visual Basic for Applications 視窗
  4. 再來點擊上方選單的 插入(I) > 模組(M),接著把下方的程式碼貼入程式碼的視窗中
  5. 最後按下鍵盤上的 F5 按鍵,會跳出一個巨集儲存的視窗,最後按下 ⌈執行⌋ 按鍵,就會將C:\temp 資料夾下面的所有活頁簿 (Workbook) 內容都合併到這個活頁簿了喔

 

Sub CombineSheets()
    '活頁簿存放路徑,可自行修改存放路徑
    Path = "D:\temp\"
    Filename = Dir(Path & "*.xl*")
    
    Do While Filename <> ""
        Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
        
        For Each Sheet In ActiveWorkbook.Sheets
            Sheet.Copy After:=ThisWorkbook.Sheets(1)
            ActiveSheet.Name = Filename
        Next Sheet
        
        Workbooks(Filename).Close
        Filename = Dir()
    Loop
End Sub

 

注意事項

  1. 所有的Excel 活頁簿 (Workbook) 中的資料都會被複制到執行程式碼的活頁簿之中
  2. 複制過來的Excel 活頁簿 (Workbook)內容,工作表會以該 EXCEL 活頁簿的檔名重新命名
  3. 該段 VB 的巨集程式碼,不適用指定活頁簿 (Workbook)中特定工作表(Worksheet)的複制
Share.

31 篇迴響

  1. wendy Windows other version Google Chrome 56.0.2924.87 on

    你好,
    不好意思, 我發現是我誤會了
    我以為編輯VBA是ctrl+F11, 結果是Alt+F11
    我想ctrl+F11應該是原本內建的快速鍵, 那就沒問題了^^;
    另外想請教, 如何把上述的程式嗎存為巨集呢?
    謝謝.

    • Hi wendy,
      忙中難免會有點小差錯 ^^
      VBA本身你存檔時,就會跟著EXCEL一起存起來了

  2. 你好, 謝謝你的分享.
    貼了上述的程式碼後, 成功的合併不同sheet資料
    但是關掉excel再重開, 在巨集就找不到此功能
    覺得應該是沒存檔, 就在VBA存檔, 結果不知道怎麼存的…
    現在在excel裡按ctl+F11,會跑出新的一個sheet名稱為巨集1
    再按一次ctl+F11, 會再跑出新的一個sheet名稱為巨集2
    且都沒有合併功能
    有檢查過巨集裡, 沒有新增的巨集
    我完全不會用VBA,也不知道怎麼把這個功能刪除, 請問可以幫幫我嗎? 謝謝

  3. 黃騰 Windows other version Google Chrome 54.0.2840.99 on

    假如我的檔案為1-5 希望進入之後也是1-5 可是進去之後 變成5-1

    • Hi 黃騰,
      目前用的函式Dir,沒有提供排序的選項
      所以需要自己刻一個排序的函式來達到

  4. 米夏 Windows other version Google Chrome 51.0.2704.103 on

    JASON翰 你好
    請問根據上面的語法,有辦法將所有excel合併到指定的活頁簿嗎?其實就是希望不要跟巨集放在同一個活頁簿!
    不知道有沒有甚麼辦法?
    謝謝

    • Hi 米夏,
      蘋果米目前想到最快的方式,就是將執行完的檔案中的巨集直接移除

  5. Sibyl Mac OS X Safari 9.0 on

    Hi Jason翰,
    有一個問題要請你幫忙,如果檔案裡面有2個以上的sheet,他就會有錯誤。因為這個vba在合併檔案時,sheet name是原來檔案的名稱,所以同一個檔案有多個sheet,他會因為同樣的sheet name而失敗!
    請大大幫幫忙~~
    謝謝

    • Hi Sibyl,
      可以將第11行的程式碼註解掉或刪掉
      這樣應該就能解決你的問題了

  6. hello…檔案開啟後一起跑….變的非常慢無法完整打開…請問是怎麼了….?

    • Hi 12,
      可能開啟檔案時,要重新計算每個儲存格的值
      所以才會變的這麼慢吧

  7. Sibyl Mac OS X Safari 9.0 on

    Hi Jason翰,
    謝謝你的範例,可是有一個問題,如果合併的檔案中有一樣sheet name的話,它就會失敗!
    其實我是希望所以檔案裡面的資料最後是合併在同一個sheet的裡面的~~
    再麻煩你幫幫忙了!

    • Hi Sibyl,
      還是請你給個範例檔,讓果編了解一下你的問題在那
      這樣果編要幫忙也才施的上力 XDDD

    • Hi 12,
      我就是有看到這個情況,所以把公式先刪掉了
      看起來這個問題只能寫程式下去跑了
      公式還找不到有合適的做法 XD

  8. 我想請問 假設我工作表1的資料是A欄到H欄,B欄主要是分鐘時間資料但是是間斷資料,但我想要的是每一分鐘都有資料,因此在工作表2做了一個完整的連續時間順序資料,請問該怎麼將兩個工作表合併,讓工作表1有資料的地方跑到工作表2,而工作表1時間沒資料的部分在工作表2仍然是沒資料?

    謝謝

歡迎您發表迴響唷!