ブックの全シートをそれぞれ別の新規ブックに分ける
１つのブックの中にたくさんのシートがある場合がありますが、あまりに多すぎて探すのが大変な場合があります。
そういう場合はいっそのこと各シートを別のブックを分けてしまった方が都合がよくなることが多々あります。
以下では指定したブックの全シートをそれぞれ新規ブックに保存するマクロを紹介します。
ブックの全シートを新規ブックで作成するマクロ
引数にブックのフルパスを指定すると、そのブックと同じフォルダに全シートをひとつずつ新しいブックで保存します。
引数のブックはそのまま残ります。
作成するブックの名前は３桁の連番とシート名になります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
Sub SheetToBook(a_BookName As String) Dim ex As New Excel.Application '// Excelオブジェクト（別プロセス用） Dim wbBase As Workbook '// Workbookオブジェクト（別プロセス用） Dim sht As Object '// 引数ブックのシートオブジェクト Dim i '// ループインデックス Dim ext As String '// 拡張子 '// 警告ダイアログを非表示 ex.Application.DisplayAlerts = False '// 別プロセスのExcelで引数ブックを開く Set wbBase = ex.Workbooks.Open(Filename:=a_BookName, UpdateLinks:=0, ReadOnly:=True, IgnoreReadOnlyRecommended:=True) '// 拡張子を設定 ext = ".xlsx" i = 1 '// 引数ブックの全シートをループ For Each sht In wbBase.Sheets '// シートの表示状態を非表示でなくす sht.Visible = xlSheetVisible '// シートをコピー wbBase.Sheets(sht.Name).Copy '// ブック保存 Call ex.ActiveWorkbook.SaveAs(Filename:=wbBase.Path & "\" & Format(i, "000_") & sht.Name & ext) '// ブックを閉じる Call ex.ActiveWorkbook.Close i = i + 1 Next '// 引数ブックを閉じる Call wbBase.Close '// 警告ダイアログを表示 ex.Application.DisplayAlerts = True Call ex.Application.Quit Call MsgBox("完了", vbOKOnly) End Sub |
コード説明
引数のブックを別プロセスのExcelで開いています。内部的にExcelオブジェクトを利用しているため引数のブックは非表示で起動された状態になります。
警告ダイアログを非表示にしていますが、これはあまり使うことは無いと思いますがExcel 4.0 マクロシートを使っている場合の対応です。
拡張子を固定でxlsxにしていることが警告ダイアログに引っかかります。それを防ぐための警告ダイアログを非表示にしています。
また、ループ内でシートの表示状態を標準状態にしています。これは非表示シートを新規ブックに保存するためです。非表示のままではエラーになります。
その他はコメントの通りです。
使い方
上の関数の利用サンプルです。シートを分解したいブックのフルパスを関数に渡します。
|
1 2 3 4 5 6 7 |
Sub SheetToBookTest() Dim s As String s = "C:\web\test\\Book2.xlsm" Call SheetToBook(s) End Sub |
実行結果
Excelで作成できるシートには「ワークシート」「グラフ」「Excel 4.0 マクロ」「MS Excel 5.0 ダイアログ」の４種類があります。
サンプルのBook2.xlsmにはそれらの４種類のシートと非表示にしているワークシートのがあります。
1. ブックを用意します。ここではBook2.xlsmです。
2. 上で紹介しているSheetToBook関数を実行します。
3. Book2.xlsmと同じフォルダに各シートが新しいブックで保存されます。
注意点
このマクロには注意点があります。それは途中で止まったように見えることがある点です。
新規ブックの保存が連続して行われるとVBAの処理が止まったようになることがあります。時間にして１分程度です。
実際には止まってないのでしばらく待ってみてください。一応完了ダイアログを出すようにはしているのでそれを待ってみてください。