1 2 3 4 5 6

廣告

網友您好,若是您覺得我寫的工具或文章對您有幫助,
而您或者您的朋友有在博客來購物,可以透過底下連結

如何在博客來購物贊助網站或是點選計數器下方的博客來圖示來讓網站可以永續經營。

2008年7月31日 星期四

如何一次將多個Excel檔案copy到一個檔案內的不同工作表中,並以檔案的檔名作為工作表的名稱

有時候我們會把Excel分類請不同人幫忙輸入,但是輸入完時,都是各別檔案,如果要把它copy成一個檔案就很麻煩,底下這個VBA程式,就是在處理這種情形的工具。
練習檔案:
2009/1/9:新增一個可以匯入txt的功能
2009/5/4:修改程式.除了xls開頭的檔案類型會用excel的方式開啟,其它的會用文字檔方式開啟

Excel 2003 新版下載網址
如何一次將多個Excel檔案copy到一個檔案內的不同工作表中
http://discuz.bestdaylong.com/thread-30584-1-1.html
1.下載ergertosheet.xls把它放在要處理的Excel檔案目錄中 2. 開啟時會出現安全性警告,請按啟用巨集 3.將您要合併的檔案名稱輸入 4.合併完時會出現,己將所有檔案匯入活頁中 5.這是合併後的sheet

96 則留言:

cyc test 提到...

這個方法無法在excel2007中使用,可不可以請版主高手教導我一下,該怎麼處理,因為一直出現無法儲存,謝謝。

一整天 提到...

cyc test您好:
我把程式copy到excel2007中使用,可以正常執行.您是用我提供的練習檔案做練習的嗎?麻煩您先試看看.用我的檔案.把它copy到c:\excel,看可不可以正常執行.如果還是有問題,麻煩您抓一下錯誤畫面,寄到imingho@gmail.com我再幫您看看.

vera 提到...

你好~
你所做的程式很好用....
但是我的1個EXCEL檔資料量非常大,都會超出範圍....上方欄的部份A.B.C....至T
..至於列1.2.3.....至快3000筆,

我想COPY的EXCEL檔高達四..五百多個之多,可以請教你嗎???不好意思麻煩了。

一整天 提到...

vera您好:
我幫您到微軟的網站查看.
http://www.microsoft.com/hk/community/chatroom/2004/ITPro_TechTalk_QA_20040108.aspx

看Q33. 會看到底下.

活頁簿中檢視個數 受限於可用的記憶體 (預設值是 3 個工作表)

白謙 提到...

您好
請問如何把三個EXCEL檔案(統計數量表)
合併並加總 成一個新的檔案
(格式都相同 )

一整天 提到...

白謙您好:
您可以利用我寫的另一支程式.
http://272586.blogspot.com/2008/10/excel_29.html
如何快速將多個Excel活頁簿合併成一個

把合併後的活頁變成一個

白謙 提到...

不好意思!!!
我要的不是 這個合併的
我的 檔案 是 有數量 跟金額
我是要把三個檔案所有金額跟數量 加總
然後合併成一個總金額跟總數量的新檔案

一整天 提到...

白謙您好:
如果您是要寫一個檔案,把其它3個檔案的數量及金額,您可以先把那3個檔案打開,再開一個新檔,新檔的儲存格A1可以直接下=檔案1的名稱!A1+檔案2的名稱!A1+檔案3的名稱!A1,這樣就可以合併結果在同一頁.
ps:如果這不是您要的結果.麻煩您寄檔案給我(處理前跟處理後的情形),我的email:imingho@gmail.com

Chieh Hua 提到...
作者已經移除這則留言。
Chieh Hua 提到...

你好,我常需要將多個txt檔案copy到excel中,我使用你寫的巨集,相當好用,但想請教在開啟txt時,同時自動以tab or 空格相隔在不同欄中呢,謝謝

一整天 提到...

Chieh Hua 您好:
  你可以把txt寄一份給我看看.我再看看要如何修改.

Chieh Hua 提到...

你好,星期五已經檔案寄給你,麻煩您了,謝謝你提供這麼好的blog

chun 提到...

你好
若我的原始資料是多個文字檔不是EXCEL檔 有辦法 匯入通一個EXCEL檔 並以檔名當工作表明 ??

一整天 提到...

chun您好:
我把這程式修改可以讀excel跟txt,您到底下網址下載,使用方法跟舊的一樣,是多一個檔案類型的設定.
http://f272586.googlepages.com/mergertosheet_v2.xls

chun 提到...

你好,
謝謝你寫這個程式,剛剛試run 結果有點問題,我是將txt檔名放在c2 c3 ,合併excel資料後產生boook1 可是裡面卻是空的,我試著放再b2 b3 會出現偵錯 => Workbooks.Open Filename:=Excel.Workbooks(WorkName).Path & "\" & Filename '開啟檔案 麻煩幫我看一下 謝謝 ( btw 剛剛重寫意見給你因密碼有誤所以不知道有沒有送出去 ,若有收到其他封 很抱歉造成您的困擾 .. >_<")

一整天 提到...

chun您好:
檔案是放在b2,b3沒錯,您只是沒把b1的內容改成txt,我已經把說明補上去了.您可以重新再下載.

chun 提到...

感謝你的說明,試run 後都ok了。我在你的blog中找到好多寶,如 excel 特殊填滿技巧就讓我在工作中省下不少時間 超好用的。

chun 提到...

Hello, 想請問 多個Txt file 匯入 Excel 後,資料會自動分行分列排,中文字會變亂碼如 ..擐葛蝳?曆?撓??.. 可以讓資料匯入後只出現在A列 我自己再用資料剖析整理 ? 還有中文出現亂碼要如何調整? 謝謝你

一整天 提到...

chun您好:
我把這程式修改,您到底下網址下載,使用方法跟舊的一樣,目前是多一個是否自動資料剖析的設定.
http://f272586.googlepages.com/mergertosheet_v2.xls

阿亮 提到...

您好:
我想請問有方法可以只挑選要合併的檔案內特定欄位嗎?
例如:ABC三個檔案內皆有姓名、ID、地址、電話等欄位~使用您寫的這個巨集把它合併成一個檔,但檔的內容只要有姓名、ID、地址、電話等欄位就好了..不知道有沒有方法可以做到?
謝謝您的抽空回覆~

一整天 提到...

阿亮您好:
目前這支程式沒有這種功能,建議您在合併完成再把不要的欄位選取刪除即可。

阿亮 提到...

謝謝一整天大的回覆囉~因為有太多工作表要用,而且是每天的例行公事,所以才想問看看有沒有方法解決。在這裡也獲得不少相關的知識,感謝您熱心的分享

阿亮 提到...

一整天您好:
請問一下我在公司用這個程式,但都會出現"找不到指名引數"。公司的EXCEL版本是2000的,請問要怎麼解決呢?謝謝

一整天 提到...

阿亮您好:
我目前手邊沒有excel2000可以測試,可能我要找到才可以測試,可不可以麻煩您把錯誤的畫面抓起來,寄到我的信箱.

另外您的另一個問題.
只要姓名、ID、地址、電話欄位的,可以寄給我您的範例,我在幫你看看要如何修改,(資料用測試的就可).

阿亮 提到...

一整天您好:
我把錯誤訊息和問題的範例上傳到我的空間
http://light128.myweb.hinet.net/TEST.rar

因為我找不到您的EMAIL~不知道怎麼寄給您,若會照成您的不便深感抱歉

一整天 提到...

阿亮您好:
我的Email在首頁的右上方,您是用txt要合併的,因為我那時候是用Excel2003錄製巨集來修改,所以在Excel2000就會有些參數會出問題,所以您把TrailingMinusNumbers:=True這一行跟上一行的最後兩個字元(,_), 一起刪除,再試看看可不可以用.如果有問題,寄email給我會比較快.

提到...

你好..
很感激你寫出如此方便的程式
我想請問假如我想有3個不同的檔..
例如:甲班.xls,乙班.xls,丙班.xls
...等班級檔案、這幾個檔裡面都有個物理、化學、生物...等不同科目,來自於不同學生的成績(例如學號1~100)。
那我要如何將這三個檔合併分開成物理.xls裡面有1~100號的甲班、乙班、丙班...等的成績;化學.xls裡面有1~100號的甲班、乙班、丙班...等的成績...等不同的檔案呢...感謝

一整天 提到...

蘇您好:
您可以寄一份處理前及想要處理後的檔案給我,我再看要怎麼幫您.我的email在首頁右上方.

阿偉 提到...

我要的剛好相反,有那種一個excel檔有多個sheet,卻要分開成數個excel檔,有這種巨集嗎?

一整天 提到...

阿偉您好:
我把另一支程式修改成您要的功能,請參考http://272586.blogspot.com/2009/08/excel_27.html
如何將Excel的多個活頁簿另存為檔案

浮生若夢 提到...
作者已經移除這則留言。
浮生若夢 提到...
作者已經移除這則留言。
浮生若夢 提到...

一整天大您好, 您的這支程式可否修改成只針對[定檔案資料匣]4 [含有特定字元] 檔名的excel檔案,且 只複製檔案內的[特定名稱的sheet]呢 ?


本程式的巨集可否做一個按鍵
[定檔案資料匣] [特定字元的檔名]與[特定名稱的sheet]可否有地方可以直接輸入 而不用跳進VBA中修改

非常感謝一整天大的幫忙 謝謝.

一整天 提到...

浮生若夢您好:
您可以把您想要的Excel寄給我,我改好時再通知您。

益聖 提到...

我想問一下
要怎麼把多個txt檔最後一行數值
合併到一個excel檔頁面

LonelyPlanet 提到...

您好,感謝您提供這些極為有用的VBA,我現在試用的結果,他會去抓排在第一個的活頁簿,但我需要抓的是每個檔案的第二個活頁簿(當然名稱就跟檔名無關了),請問您這支程式可用嗎?若可用,合併後的活頁名稱可以依舊是用檔名取嗎?
謝謝您~

一整天 提到...

LonelyPlanet您好:
我將程式做了修改,可以加入活頁簿的名稱.

程式下載,請到[首頁右上方],[檔案下載],找到mergertosheet_v4.xls下載

石頭 提到...

感謝你的熱心,讓我的工作效率提升了50%

因為最後資料還要整合分析的原因,我必須把每一分頁(sheet)的D欄位的文字都變成分頁名稱(sheet.name),,要怎麼寫replace呢?還是有更好的方法呢..謝謝

一整天 提到...

石頭您好:
您可以寄一個您要呈現的Excel給我,我再依您給的檔案,看要如何修改程式.

C.C. 提到...

一整天大大,

對不懂excel的我來說~~這真的是太棒而且是好用的檔案~~~

謝謝您

一整天 提到...

C.C.您好:
很高興我寫的程式有幫到您。

Chun-chi 提到...

大大您好
不好意思..
我使用了這個範本
會出現新的檔案
工作表卻沒有出現我要合併的資料及名稱
請問是那裡出問題呢
謝謝

一整天 提到...

Chun-chi您好:
您是否將這個檔案跟要copy的檔案放在同一個目錄,或是您沒有將要合併的檔案名稱輸入,才會只開一個新檔案.

Chun-chi 提到...

我有將此檔案跟要copy的檔案放在同一個目錄下,且在檔裡有填寫每個要合併的檔案名稱,但無論我只合併幾個都是有一個新檔案,但沒有內容

一整天 提到...

Chun-chi您好:
您方便寄您的測試檔案給我.方便我查看是那出了問題.
ps:我的Email在首頁右上方就可以看到.

nick 提到...

一整天大您好, 您的這支程式可否修改成只針對一個目錄中的所有excel檔案

非常感謝一整天大的幫忙 謝謝.

一整天 提到...

nick您好:
您可以使用另一支程式達到您要的需求

http://272586.blogspot.com/2010/01/excel_26.html

如何利用Excel讀取單一目錄下的所有檔案名稱

geway 提到...

拜讀您以下文章及擴充程式幫助很多
1 如何一次將多個Excel檔案copy到一個檔案內的不同工作表中,並以檔案的檔名作為工作表的名稱
2 如何快速將Excel多個工作表合併成一個

但我的問題差一點就更完美 情況如下:
總公司有一 Excel檔(單一工作表) 每周收集6個分支機構email 寄來相同格式Excel檔 逐周累積資料 以便做樞紐分析 因此我需要將6個分支機構的Excel檔案copy到總公司累積許多資料 的這個Excel檔的這個工作表中 直接插入 使資料一周次遞增

而不是您目前另外產生一個Excel新檔案
謝謝

geway 提到...

拜讀您以下文章及擴充程式幫助很多
1 如何一次將多個Excel檔案copy到一個檔案內的不同工作表中,並以檔案的檔名作為工作表的名稱
2 如何快速將Excel多個工作表合併成一個

但我的問題差一點就更完美 情況如下:
總公司有一 Excel檔(單一工作表) 每周收集6個分支機構email 寄來相同格式Excel檔 逐周累積資料 以便做樞紐分析 因此我需要將6個分支機構的Excel檔案copy到總公司累積許多資料 的這個Excel檔的這個工作表中 直接插入 使資料一周次遞增

而不是您目前另外產生一個Excel新檔案
謝謝

一整天 提到...

geway您好:
您可以參考
http://272586.blogspot.com/2008/07/excelcopy.html
如何一次將多個Excel檔案copy到一個檔案內
至於是否開新檔.您可以修改程式.讓其在其中的一個工作表中顯示。

橘子愛胖虎 提到...

您好,我用了您寫的EXCEL合併檔案的程式,真的超級好用的,感謝!

03724401 提到...

請問若要匯入時,可否選擇excel儲存格格式為文字格式,可不可以請版主高手教導我一下,該怎麼處理,謝謝。

明和蔡 提到...

03724401您好:
 請問您"excel儲存格格式為文字格式"是指您要匯入的檔案副檔名不是xls嗎?或是您可以寄一個範例給我,我幫您看看.

Phoebe 提到...

彰化大大您好,
您提供的東西讓我受用無窮,非常感謝您提供這麼好的工具^^~我想請問,因為這個程式只會合併各檔案中第一個sheet。我現在因為每個檔案裡都會有兩三個sheet,有沒有辦法把每個檔案中所有的sheet都一起照順序合併呢?在此先謝謝您唷^^~

Unknown 提到...

謝謝你~受益良多,我可以在我自己的blog上引用你的文章嗎?我會放上連結註明原文出處來自這裡~

Unknown 提到...

很好用謝謝~有一個問題就是合併後我原本設定大小沒有滿版(a4)
但合併後都自動變a4大小...照片會拉寬...
怎麼辦呢?

明和蔡 提到...

Unknown您好:
歡迎您引用,只要註明原文出處就可以.

明和蔡 提到...

unknown您好:
可以寄範例檔案給我嗎?我這邊幫您試看看.

sherilyn 提到...

一整天您好:看完了你的範例之後,覺得很棒,且很好用,但要套入我自己的資料,卻不知該怎麼下手,尤其看不懂該怎麼修改VBA的部分。請您有空可以協助我完成資料問題嗎?謝謝!!!

一整天 提到...

sherilyn您好:
您將您的資料eamil寄給我(imingho@gmail.com).我看是否可以幫上您.

william 提到...
作者已經移除這則留言。
william 提到...

您好
看到您的文章受益良多
但有問題想請教
多個Excel檔案copy到一個檔案內的不同工作表
如果Excel檔裡有多個工作表
如果一併複製過去呢?(您這篇文章是針對單一檔案單一工作表)
例如 A有3個工作表 B有2個工作表
合併後可變成5個工作表於同一檔案
再麻煩給予指導
謝謝

一整天 提到...

william:
合併工作表,您可以參考
http://272586.blogspot.tw/2008/10/excel.html
如何快速將Excel多個工作表合併成一個

jill 提到...

版主:
非常感謝您的熱血,寫出這麼實用的巨集,
依照您的指示我有把3個檔合併一個檔,
但現我有262個檔,請問我該如此快速擷取這262個檔名呢?

一整天 提到...

jill您好,

您可以參考:

如何利用Excel讀取單一目錄下的所有檔案名稱

http://blog.bestdaylong.com/2010/01/excel_26.html

jill 提到...

版主,感謝您,我已知道如何處理了,
再請教一下,一個檔案有超過一個工作表以上,有其他的VBA可參考嗎,因為這個VBA只能合併第一個工作表,再次感謝

jill 提到...

版主,再次補充,我現在的情形是要把多個檔,其中檔案大部份只有一張工作表,另外小數有多張工作表,我須把這些檔案,整合成一張工作表做樞紐分析,
你有計算一個檔案有幾張工作表的VBA嗎?
除了檔名,可以再增列工作表名稱嗎?
謝謝你!

一整天 提到...

jill您好,

您可以參考:

http://blog.bestdaylong.com/2008/10/excel.html

如何快速將Excel多個工作表合併成一個

jill 提到...

版主,謝謝你,你提的這個我知道,但我要每個檔案一個一個開啟,我才會知道.
我結合你提供的多檔合一檔,多表併一表,我還是要對工作表名稱及檔名在那邊剪剪貼貼,還是很費時,
謝謝你的不吝賜教!

Patrick Chan 提到...
作者已經移除這則留言。
Patrick Chan 提到...

板大,
可否不要指定檔案名稱
只要檔案抬頭為Sheet都copy到同一個工作表中,sheet% 試過不行用

一整天 提到...

Patrick Chan 您好,
這支程式沒有這種用法,您可以到底下下載的可以用選取檔案的方式選檔案
如何一次將多個Excel檔案copy到一個檔案內的不同工作表中
http://discuz.bestdaylong.com/thread-30584-1-1.html

或是利用底下這支程式讀取目錄檔案


讀取單一目錄下的所有檔案名稱含目錄
http://discuz.bestdaylong.com/forum.php?mod=viewthread&tid=31104&fromuid=2


輸入 sheet*.xls

michelle 提到...

版主, 為何我試把 mergertosheet_v4
做檔案合併, 新工作簿出來結果只開
工作頁, 但全部沒有單元格內容的,
請問怎樣可以複製包含內容呢?
謝謝.

michelle

一整天 提到...

michelle您好,
您是否可以將檔案寄到我的信箱,我幫您看那裡設定有問題.

Mi Michelle 提到...

版主:
我想將合併結果儲存在巳有檔名去 (不另存新檔), 請問程式該如何修改?

一整天 提到...

Mi Michelle您好,
您要將底下這一行,改成用開啟檔案的寫法.就可以.
Excel.Workbooks.Add '開新的workbook

Unknown 提到...

請問可以多檔案多工作表一起合併嗎?(如檔案A有3個sheet不同格式,檔案B也有3個sheet不同格式,可以將檔案A和檔案B合併成一個檔案AB,然後也是3個sheet)

蔡明和 提到...

您好,
目前該檔案沒有您想要的功能,您只能個別工作表合併後,再各別複製到您要的excel檔案.

Unknown 提到...

哈囉想請教一下:
假如說我把所有資料放在a工作表(這個工作表每天都會新增)
然後要每天從a工作表中抓出特定文件的資料
例如工作表a有a1.a2.a3.b1.b2.b3這六個名字的產品
我要抓a1這個產品相對應欄的資料某幾欄資料
使用巨集設計出一個按鈕只要一按就會把a工作表a1產品資料匯出到b工作表而且不覆蓋前一天已經有的資料呢?也就是說今天9/30按出來的資料會新增到昨天9/29已經輸出的b工作表下方,但不會覆蓋掉之前的
請問要怎麼做呢?

莊堯茗 提到...

因為我一直嘗試想把EXCEL的工作表匯入我要的B工作表,但一直找不到哪裡可以這樣做

蔡明和 提到...

您好,
可以麻煩您將問題,到我的論壇http://discuz.bestdaylong.com註冊發問,在論壇可以上傳檔案及圖片方便說明及回答問題.謝謝..

莊堯茗 提到...

好像無法註冊 他寫我的IP要一小時後才可以

https://drive.google.com/file/d/0B_NAYy-1Vgo9QUp0R2I2WnV2TEU/view?usp=sharing

這是檔案
謝謝您~~

蔡明和 提到...

麻煩您跟我講您註冊的帳號,我幫您查看原因.或是將您的畫面截取寄到我的信箱.

走自己的路 提到...

HI 版主您好,這個程式相當好用,但是如果想以合併檔案中的分頁名稱(EX:20160130)作為合併後分頁名稱,該怎麼修改?

蔡明和 提到...

您好,
在程式碼中找到
ActiveSheet.Name = strFileName '將活頁名稱改成檔案名稱
將它改成
ActiveSheet.Name = sheetname '將活頁名稱改成匯入工作表

Sherry Huang 提到...

您好:謝謝您分享這麼實用的程式,真的提高我們的工作效能。如果想以合併檔案中某個儲存格的數值(例如:員工編號)作為合併後分頁名稱,該如何修改?感謝您。

蔡明和 提到...

Sherry Huang您好,
在程式碼中找到
ActiveSheet.Name = strFileName '將活頁名稱改成檔案名稱
將它改成
ActiveSheet.Name = objsheet.range("a1").value '將活頁名稱改成匯入檔案儲存格A1的內容

或是可以合併後,再利用底下這支程式來解決.

在Excel如何整批修改Excel中的活頁簿名稱
http://blog.bestdaylong.com/2009/10/excelexcel.html

Sherry Huang 提到...

謝謝您的熱忱解答,非常感謝。
另由於合併的檔案存在許多公式與函數,因此程式在執行時會不斷地跳出警示訊息[.....您要重新開啟檔案嗎?按下是;接著又跳出檔案已經被修改,您是否要儲存檔案?]有點小困擾,能否透過與法修正避免出現警示訊息呢?謝謝您。
若合併的檔案沒有函數或公式,就不會跳出警示訊息。

蔡明和 提到...

Sherry Huang您好,
可以寄有問題的檔案給我嗎?我這邊找看看是什麼問題.謝謝..

邱馨儀 提到...

您好,拜讀了您的文章覺得您提供的程式都非常好用,也非常佩服。
但我想請問您有沒有方法能將100個txt檔合併成一個excel檔案,不需分成多個分頁。而且如何讓每列資料的欄a是原本txt檔名?並照檔名排列呢?

蔡明和 提到...

邱馨儀您好,
您可以參考
如何一次將多個Excel檔案copy到一個檔案內
http://download.bestdaylong.com/f2.htm

邱馨儀 提到...

好的,謝謝您!!

Charlene Wu 提到...

你好~
謝謝你設計的工具幫忙很多,另外請問我合併的多個檔案中有一個是二個工作表,但合併時只選了第一個,想請問要怎麼做才能二個工作表都能合併到新檔案裏?非常感謝你的幫忙

蔡明和 提到...

Charlene Wu您好,
您可以試看看底下這支程式.
http://download.bestdaylong.com/f445.htm
合併目錄下所有的xls檔案的全部工作表不含標題列

Unknown 提到...

版主您好:
請問如果有100個EXCEL檔,每個檔案格式都相同皆有A/B/C三個不同的sheet,
我能指定這100個EXCEL檔只匯入C sheet表嗎?
我嘗試在EXCEL檔名擷取路徑上,再加入工作表路徑
->但會出現"不存在,請查看是否有拼錯字"
經檢核程式碼: 應該是因為語法採"ActiveSheet.Range("a" & i)"
->所以每個EXCEL表只會抓取存檔後游標所在的那個工作表
->請問這個有辦法修改嗎? (現在功力還不夠...><....)

蔡明和 提到...

您好,
您可以下載新的版本
http://download.bestdaylong.com/f14.htm
裡面有一個【預設工作表】您可以設定C,就是您要的功能。

贊助

彰化一整天粉絲團

About Me

我的相片

很多人問我為什麼叫"一整天",其實這是有原故的.有一天看一本書.就是說如何介紹自己.裡面說到如何讓別人對你的名字有印象.就要讓別人跟你的名字或是身體的一些特徵有關.比如很胖.就會被別人取小胖或是胖子.或是跟名字有關的諧音,那天看布袋戲中有一個叫”一頁書”的角色.所以我就想我的名字.可以想出什麼字出來.結果我把我的名字"明和",拆成日月和,日月和在一起,就代表白天晚上都在一起.就取名為"一整天",而這個"一整天"跟我的名字有關也方便別人記憶,後來發現"一整天"這個名詞太常被用,所以就在"一整天"前面加上"彰化"變成"彰化一整天",為什麼要加彰化.因為我老家在彰化,所以以後別人要找我的部落格的話.我都會跟他們說,請到yahoo或是google打"彰化一整天"第一筆出現的就是我的Blog. PS:現在只要輸入"一整天"可以找到.

Google+ Followers