複数のエクセルブックの任意のシートをひとつのブックにまとめる方法

2014年04月14日(月) 住職(第18世住職)

困ってたらOKWaveに答えがありましたので、自分のためにメモ。

Option Explicit

‘ 以下が必ず設定するところ

‘ 統合したブックの保存名
Dim margedBookPath
margedBookPath = “統合したファイルの作成場所とファイル名.xlsx”

‘ 対象ブック群が保存されているパス
Dim targetPath
targetPath = “ターゲットフォルダのパス”

‘ 対象ブックの拡張子
Dim targetExtension
targetExtension = “xls”

‘ 以上が必ず設定するところ 何枚目か指定する場合は下の方へ設定あり

Dim fso
Set fso = CreateObject(“Scripting.FileSystemObject”)

‘ 対象ブックが保存されているパスを参照
Dim targetFolder
Set targetFolder = fso.GetFolder(targetPath)

‘ Excel 起動
Dim xlApp
Set xlApp = CreateObject(“Excel.Application”)
xlApp.Visible = True

‘ 統合ブックを新規作成
Dim margedBook
Set margedBook = xlApp.Workbooks.Add

‘ 統合ブックの初期シート数を記憶しておく
Dim initialSheetsCount
initialSheetsCount = margedBook.Worksheets.Count

Dim targetBook

‘ 対象ブックが保存されているフォルダー内の全てのファイルを精査
Dim f
For Each f In targetFolder.Files
Dim targetSheet ‘ 対象ブック内のコピー対象シート
Dim copiedSheet ‘ 統合ブック内のコピーされてきたシート

‘ ファイルの拡張子が合致すれば
If fso.GetExtensionName(f.Name) = targetExtension Then
‘ 対象ブックを開き、1枚目のシートを統合ブックにコピーする (右端にコピー)
Set targetBook = xlApp.Workbooks.Open(f.Path, 0, True)

‘ シートの何枚目かを指定
Set targetSheet = targetBook.Worksheets(1)

Call targetSheet.Copy(, margedBook.Worksheets(margedBook.Worksheets.Count))

‘ コピーされたシートの名前を変更
Set copiedSheet = margedBook.Worksheets(margedBook.Worksheets.Count)
copiedSheet.Name = fso.GetBaseName(targetBook.Name)

‘ 対象ブックを閉じる
Set targetSheet = Nothing
call targetBook.Close(False)
Set targetBook = Nothing
End If
Next

xlApp.DisplayAlerts = False

‘ 統合ブックから初期シートを削除する (左端の数枚)
Dim i
For i = 1 To initialSheetsCount
margedBook.Worksheets(1).Delete
Next

‘ 統合ブックを保存して閉じる
Call margedBook.SaveAs(margedBookPath)
Call margedBook.Close(False)

xlApp.DisplayAlerts = True
xlApp.Quit
Set xlApp = Nothing

これを拡張子をvbsで保存し、実行すると統合ファイルが作成されます。上記のものだと1枚目のシートのみが抜き出されすべて統合されますが、targetBook.Worksheets(1)の(1)を他の数字に変えれば任意の枚数目のシートだけを統合できます。