どうもnekoshanです。
前回の記事でvba・マクロ(以下、マクロ)が開いて書ける準備までは出来たと思います。まだ見てない方はこちらからどうそ。
ところで、シートを一つのブックにまとめる際に移動する側のExcelブックとまとめる側のExcelブックを一つ一つ開いて、シートをコピーしていたりしませんか？
その作業の時間、無駄にしてますよ。
今回はタイトルの通りですが、別のフォルダにある複数のExcelブックを一つにExcelブックにまとめる方法をマクロを紹介しようと思います。
説明しながらお伝えしていきます。
今回紹介するコードのテンプレートファイルを下記からダウンロードできるようにしていますので、パソコンの任意の場所に保存してご利用ください。
まず、このプログラムが機能する条件がありますので、それをお伝えしておきます。
プログラムを書く上での現段階の絶対条件
以下の3つが絶対条件です。
- Excelブックが別のフォルダなどに移動せず決まって同じフォルダに存在すること
- Excelブックのファイル名とシート名が変更されないこと
- Excelブックの拡張子も変更されないこと
もし、あなたのVBAスキルが上達してくると上記で記した条件は絶対ではなくなっていきます。コードの作り方によって対応可能となるためです。
あくまでも、「今は」ですよ。
まずは完成形を見せます
下記コードが今回記述していただくコードです。やりたいこともシンプルなので結構コードは短くて済みます。
下記コード内の Workbooks.open(“ “) のダブルクォーテーション内(黄色枠)はご自身のパソコンに保存した場所までのパスを記入しなければなりません。
””内コード部分は私のパソコンでのパスを示してますので、他の方がそのまま使ってもマクロは動きません。エラーになります。
Option Explicit
Private Sub CommandButton1_Click()
Dim W As Workbook
Set W = Workbooks.Open("D:\ブログ用\Excel・VBA\vba\▼▼教室の出席簿.xlsx")
Sheets("▼▼教室").Copy After:=Workbooks("〇〇教室の出席簿.xlsm").Sheets(1)
W.Close SaveChanges:=False
Set W = Workbooks.Open("D:\ブログ用\Excel・VBA\vba\××教室の出席簿.xlsx")
Sheets("××教室").Copy After:=Workbooks("〇〇教室の出席簿.xlsm").Sheets(2)
W.Close SaveChanges:=False
MsgBox "コピぺ完了"
End Sub各コードの説明をコードの上に加えると何をしているかより分かりやすくなります。理解もしやすいと思います。
Option Explicit
Private Sub CommandButton1_Click()
'------------------------------------------------------------------------------------
'変数を宣言する
'------------------------------------------------------------------------------------
Dim W As Workbook
'------------------------------------------------------------------------------------
'コピーしたい▼▼教室の出席簿ブックを開く
'------------------------------------------------------------------------------------
Set W = Workbooks.Open("D:\ブログ用\Excel・VBA\vba\▼▼教室の出席簿.xlsx")
'------------------------------------------------------------------------------------
'コピーするシートを指定して貼り付け先のExcelブックの新シートにコピーする
'------------------------------------------------------------------------------------
Sheets("▼▼教室").Copy After:=Workbooks("〇〇教室の出席簿.xlsm").Sheets(1)
'------------------------------------------------------------------------------------
'コピーするシートは保存の必要はないので、保存をしないで閉じる
'------------------------------------------------------------------------------------
W.Close SaveChanges:=False
'------------------------------------------------------------------------------------
'コピーしたい××教室の出席簿ブックを開く
'------------------------------------------------------------------------------------
Set W = Workbooks.Open("D:\ブログ用\Excel・VBA\vba\××教室の出席簿.xlsx")
'------------------------------------------------------------------------------------
'コピーするシートを指定して貼り付け先のExcelブックの新シートにコピーする
'------------------------------------------------------------------------------------
Sheets("××教室").Copy After:=Workbooks("〇〇教室の出席簿.xlsm").Sheets(2)
'------------------------------------------------------------------------------------
'コピーするシートは保存の必要はないので、保存をしないで閉じる
'------------------------------------------------------------------------------------
W.Close SaveChanges:=False
'------------------------------------------------------------------------------------
'最後に転記が終了したことのメッセージをポップアップで出させる
'------------------------------------------------------------------------------------
MsgBox "コピペ完了"
End Sub背景設定
今回は教室が複数あり、その教室ごとの出席簿を一つのExcelにまとめるマクロを組みます。
出席簿のExcelに氏名と出欠が入力されています。誰が出席して誰が欠席したかを見るということですね。
予め以下の3つのExcelブックをダウンロードしていただいたと思いますが、そのファイルをどこでも良いので自分のわかる場所に保存しておいてください。
- 〇〇教室の出席簿 ←このファイルに他の出席簿をまとめたい
- XX教室の出席簿
- ▼▼教室の出席簿
この３つのファイルの各シートは同じ形式となっています。
マクロを書いていく
「〇〇教室の出席簿」ブック内のシートの空いてる所にボタンを設置し、そのボタンを押すと他の出席簿ファイル内の目的のシートがこのExcelブックにまとまる、というような作り方をしていきます。
では、テンプレートファイルの 「〇〇教室の出席簿.xlsm」を開いてください。
データをまとめて表示したいExcelブックをマクロ有効ブックに変更する方法を知らない方はこちらの記事を参照して下さい。
〇〇教室の出席簿ブックを開いて、「開発」タブ→「挿入」タブ→アクティブXコントロール内の「コマンドボタン」をクリックします。
そうするとカーソルが＋マークになり、コマンドボタン作成用に変化するので、ドラックアンドドロップで好きな大きさで作成します（ただし、小さ過ぎないように）。
大きさは後で変えられるので、今は気にしなくて大丈夫です。
作成したボタンの上で右クリックをして、「コードの表示」をクリックします。
そうすると何やら英語が書いてあると思います。
Private Sub CommandButton1_Click()
End Sub上記のように書いてありますが、この英語は「ボタンが押された時」という意味で、
ボタンが押された時()
終わりと言った具合に解釈していただければ良いです。
赤枠で囲った中にボタンを押した後の処理を書き込んでいきます。
今回記述するコードのプロセスは、
- 「XX教室の出席簿」ブックを開く
- 「XX教室」シートを選択してコピーする
- 「〇〇教室の出席簿」ブックの新しいシートに「XX教室」シートをペーストする
- マクロが動くように微調整する
- 「XX教室の出席簿」ブックを保存しないで閉じる
- コピペ完了のメッセージを画面に出させる
主に4ステップでとても簡単なコードです。5, 6ステップは余談です。
ブック名やシート名をご自身の環境に合わせた名前で試してみると良いです。
「▼▼教室の出席簿」ブックを開く
まずは下記のコードを書いてみて下さい。
Workbooks.Open("D:\ブログ用\Excel・VBA\vba\▼▼教室の出席簿.xlsx")このコードはExcelブックを開くコードです。一般的にエクセルブックをWorkbook（ワークブック）と呼ばれ使われています。
“の中はExcelブックが格納してあるフォルダまでのパスとフォルダ(拡張子含む)を入れます。
記述しても良いのですが、コピペしたほうが間違いがないため、PC本体でエクスプローラーを開いてもらい、任意のブック（この記事では「▼▼教室の出席簿」ブック）が格納してあるフォルダまで進んでください。
フォルダまで来たら「Alt」＋「D」キー押して格納フォルダまでのパスをコピーしましょう。
コピーしたパスを２つのダブルクォーテーションの間にペーストします。
企業などで共有フォルダからExcelファイルを引っ張りたい場合はコードが異なりますので、別記事でお伝えします。
パスを貼り付けたら、ファイル名を拡張子含めて入れ、カーソルをカッコの最後まで移動させ、エンターキーで改行をしてください。
「▼▼教室」シートを選択してコピーする
改行したその行に下記コードを記述してください。
このコードは「カッコ内のシート名をコピーする」というコードです。
Sheets("▼▼教室").Copy入力後半角スペースを入れ、そこに下記コードを書き込みましょう。
After:=Workbooks("〇〇教室の出席簿.xlsm").Sheets(1)上記コードは「〇〇教室のシート1に貼り付ける」というコードです。
Excelでは最初に新しいシートを作成するとSheets(1)というシート名になります。そこに▼▼教室のシートを上書きして貼り付けてしまおうということです。
ここまで入れると、下記のようになると思います。
Sheets("▼▼教室").Copy After:=Workbooks("〇〇教室の出席簿.xlsm").Sheets(1)ブックを開く動作を変数として格納する
次の操作自体は▼▼教室のブックを閉じる動作なのですが、ここまで書いた状態のコードでは閉じることができません。
なぜかというと、今プログラム上でアクティブな状態になっているExcelブックが〇〇教室ブックだからです。
付け加えるならアクティブなExcelブックの指定したシート上ですけど。
ここ、結構マクロ初心者がつまずく部分なのですが、マクロのコードというのは、基本的には動作がアクティブになっているExcelブック上で動くプログラムだからです。(あくまでも今回の記事では)
ここまでコードを書いてきて、今アクティブになっているExcelブックはなんでしょう。
答えは、「〇〇教室の出席簿.xlsm」 です。
ですから、このまま次に▼▼教室の出席簿を閉じるコードを書いても上手く機能しません。
つまり、もう一度▼▼教室のExcelブックをアクティブにしてあげればよいのです。
そこで▼▼教室を開く動作を最初に書いてますのでそれをもう一度呼び出します。
そのために必要なのが、小タイトルにある「変数に格納する」というテクニックです。
変数は文字通り変わる数と書き、定まっていませんから、プログラムとしては厄介です。
そこでこのプログラムではこう動きをさせますと決めてしまえばよいのです。
下記のコードをPrivate Sub・・・()と Workbooks.open・・・の間で改行して行に書き込んでください。
Dim W As Workbook
Dim とは Dimension の略語で、英語としては寸法という意味です。箱のようなイメージだと思ってください。
W は 何でも良かったのですが、よくWorkbookの頭文字を取ったほうが分かりやすいので、Wを使います。Wという文字列を Dim の入れ物に入れるようなイメージです。ちなみに大文字小文字の区別はあります。
As Workbook は意味通りワークブックとしてです。Wと書かれたものはワークブックの意味を持つようになります。WorkBook以外の意味を持たせることもできます。
つなげると、ワークブックの意味を持ったWの文字列をDim の箱に入れて、ワークブックとして動きを決めてあげているコードになります。
これを「変数の宣言」と言いまして、これもマクロ初心者がつまずく２つ目の部分なんです。
理解できましたでしょうかね？説明が上手くできずに申し訳ないです。
「変数の宣言」のコードを書いたら、先ほど記入していた下記のコード
Workbooks.Open("D:\ブログ用\Excel・VBA\vba\▼▼教室の出席簿.xlsx")の先端に
Set W =のコードを追加してください。そうすると、
Set W = Workbooks.Open("D:\ブログ用\Excel・VBA\vba\▼▼教室の出席簿.xlsx")となると思います。これで、▼▼教室のExcelブックを箱に格納したので、いつでも持ち出しどこでも開いて使うことができます。
▼▼教室のブックを保存せず閉じる
▼▼教室のブックを閉じるにはもう一度、▼▼教室をアクティブにする必要があります。
下記コードをSheets(“▼▼教室”).Copy After:=Workbooks(“〇〇教室の出席簿.xlsm”).Sheets(1)の行に記入してください。
W.Close SaveChanges:=FalseW を使って▼▼教室のワークブックが開き(既に開いているのでアクティブになるだけ)、ワークブックが閉じられる。その際に名前を付けて保存しないを選択しています。
それが、SaveChanges:=False というコードです。
False は偽という意味ですが、否と思ったほうが分かりやすいでしょう。逆に真の場合は True です。True の場合は名前を付けて保存することになります。
××教室の出席簿をコピーして〇〇教室の出席簿に貼り付ける
前述した「「▼▼教室の出席簿」ブックを開く」小項目から同様に××教室の出席簿を開いてコピーしてペーストして、保存せず閉じるコードを書きましょう。
下記をコピペしてもらっても構いませんがファイルの格納してあるパスはご自身が保存したところまでのパスに変えて下さいね。
Set W = Workbooks.Open("D:\ブログ用\Excel・VBA\vba\××教室の出席簿.xlsx")
Sheets("××教室").Copy After:=Workbooks("〇〇教室の出席簿.xlsm").Sheets(2)
W.Close SaveChanges:=False▼▼教室と異なるコードの部分があります。
- ▼▼が××に変更になった
- Sheets(1)からSheets(2)になった
Sheets(2)なのは、既に新しいシートを作った時にSheet1は出ましたので、Excelの機能で次はSheet2が適応されることを考慮しています。
記入が終わったら改行をしておきましょう。
コピペが終わったことを表示させる
最後にコピペが終わったことをメッセージといて画面上に表示させましょう。
改行した行に、下記のコードを記入しましょう。””内は自由に文字を書き換えて構いません。
MsgBox "コピぺ完了"これでコードの記述は全て終了です。
マクロを動かしてみる
右上画面の閉じるボタンをクリックしてVBEを閉じます
次にデザインモードを終了しましょう。「デザインモード」の背景がグレーであれば、モードが有効になっていますので、一回押して背景を白っぽい色に戻しましょう。
一度全てのExcelファイルを閉じて、再度「〇〇教室の出席簿」を開いて、ボタンをクリックして見ましょう。
エラーが起きなければ、▼▼教室と××教室のシートがこのシートにまとまっているはずです。
エラーが起きた場合は、当ブログまでお問い合わせください。
まとめ
複数のクラス、複数の教科で出席率を出す場合に利用ができます(そんな状況があるのか)。
私が思っていないところでも需要はあると思います。慣れてきたら様々に応用してみて下さい。
