ワークブック間でも転記（コピペや保存）をすることができる
ワークブック間の転記に関して助けてほしいというメールをいただきました。
「ワークブックAの文字列をコピーしてワークブックBに値として貼り付けたい」という内容です。
多くの方が課題にしている「転記」関連の話です。もう少し詳しく教えていただくとポイントは３点でした。
- 同じフォルダの中にある別のワークブックとの間でコピー&ペーストを行う
- 1つのワークブックから複数のワークブックにコピー＆ペーストを行う
- コピー&ペーストは1回の作業で1ヶ所ではなく複数存在する
簡単に別ブックへのコピー＆ペーストだけ・・・というわけではない様です。
結論から申し上げますとどんな環境でも転記は可能です。
ただしファイル間の作業は少し難しいです。
かんたんに理解できる様にする為に事例を用意しました。事例に対応したコードも用意しています。
最後にコードの中でポイントとなる要素を含め解説を用意しました。
関連記事
今回は1つのフォルダの中でデータのやり取りを行います。
別の記事ではワークブックとフォルダ間のデータのやり取りに関する記事も用意しています。
関連記事VBAで画像を取り込む｜高速で画像自体を取得エクセルに貼り付ける
VBAを使えば1つのフォルダの中にある複数の.xlsxを開く事も可能です。
Dir関数について解説VBA｜Dir関数を使ってフォルダ内の複数ファイルを順番に処理する
本シリーズはこちらの記事とも連携しています。マクロを理解するための道すじを用意しました。
マクロ勉強の道筋マクロは何から勉強するのか｜学習をサポートするためのロードマップを作成
この本で勉強しました
「やりたい事は明確なんだけどコードに置き換えられない」と思った事はありませんか？
ワークブック間の処理は特に多くの方から「コードが思いつかない」という問い合せをいただきます。
初心者の頃私はこの本で勉強しました。コードが思いつかない時は事例を見て参考にすると良いです。
いつでも見れる環境に1冊用意しておくと非常に便利な書籍ですよ。
こちらの書籍は沢山事例が詰まっているのですが少し重たいです。そこで電子書籍をおすすめします。
自分のPCの中に大量の事例集を持つことが出来ます。見るだけでアイデアをもらえるので重宝しています。
転記とは
「転記」に対する認識を統一させるために定義を確認しておきましょう。
weblio辞書：転記 より抜粋
Excelの作業としてはコピー＆ペースト（コピペ）が転記に近い作業になります。
以降事例を使ってExcelにおけるデータの転記を実践していきます。
事例
ここからは事例を使ってワークブック間の転記について説明していきます。
環境
以下のような環境を用意しました。
- 1つのフォルダを用意
- コピー元データ(.xlsm)とコピー先データ(.xlsx)を用意
- 各ワークブックはワークシートを1つずつ持っている
フォルダ内のデータを画像にしてみました。
各ブックに配置されたシートの状態です。
ワークシート
calc.xlsmのsheets(“calc”)です。配置として2つのポイントがあります。
コードを使用する際はシートを再現してください。
実行時はオートシェイプなどの図形にマクロを登録していただく事で簡単に実行できる様になります。
転送先の指示
セルC2で転送先のワークブックを指定します。ここではA～Cを指定する事になります。
今回はワークブック間の作業がメインですのでここはスッキリした仕様にしています。
セルにプルダウンを埋め込んだりインプットボックスで入力させても良いです。
各自用途に応じてアレンジしてください。
貼り付け先のシートには特に規制を設けていません。
単純にシートのセルE4を起点に表を貼り付けるだけの仕様になっています。
表
E4から始まる表を1つ用意しました。
この表をコピー先のブックにある指定のシートに貼り付けるというコードを用意しています。
コード
calc.xlsmの標準モジュールに貼り付けて使うコードです。
Sub calc.xlsmからコピー先ブック.xlsxに貼付け()
'プロシージャ名から.（ドット）を外して使ってください
'*******************************************************************
'1、変数の定義
Dim r As Long 'row 行の取得
Dim c As Long 'column 列の取得
Dim wb_calc As Workbook 'workbook_calc コピー元のワークブック
Dim wb_abc As Workbook 'workbook_abc コピー先のワークブック
Dim ws_calc As Worksheet 'worksheet_calc コピー元のワークシート
Dim ws_abc As Worksheet 'worksheet_abc コピー先のワークシート
Dim ws As String 'ws worksheet コピー先のワークシート名
Dim asr As Long 'answer コピー可否の回答
Application.ScreenUpdating = False
'*******************************************************************
'2、コピー元のワークブックへの処理
'ブックcalcの各種情報を変数にセット
Set wb_calc = ThisWorkbook
Set ws_calc = Sheets("calc")
ws = Range("C2").Value
'ブックAのシートAからコピー用のデータを取得
With ws_calc
r = .Cells(Rows.Count, 5).End(xlUp).Row
c = .Cells(4, Columns.Count).End(xlToLeft).Column
.Range(.Cells(4, 5), .Cells(r, c)).Copy
End With
'*******************************************************************
'3、貼り付け先ワークブックへの処理
'データ保存先のブックを開きつつ各種情報を変数にセット
Set wb_abc = Workbooks.Open(ThisWorkbook.Path & "\" & ws & ".xlsx")
'各ワークブック名と各ブックに配置されたワークシート名は同じです
Set ws_abc = Sheets(ws)
'ブックBのセルE4に貼付け
ws_abc.Range("E4").PasteSpecial xlPasteAll
'コメントになっているコードは値貼付けのコード
'ws_abc.Range("E4").PasteSpecial Paste:=xlPasteValues
'*******************************************************************
'4、保存時の条件分岐
asr = MsgBox("指定のブックの指定のシートに値を貼り付けました。" & _
vbCrLf & "このまま保存しても良いですか？", , vbExclamation + vbOKCancel, "確認")
Application.DisplayAlerts = False
If asr = 1 Then
wb_abc.Save
'データ保存後に保存先の状態を確認するにはこの後の wb_abc.Close をコメントアウトする
wb_abc.Close
Else
MsgBox "指定のブックを保存せずに閉じます。", vbInformation, "報告"
wb_abc.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub
End If
'*******************************************************************
'5、後片付け
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "作業完了", vbInformation, "報告"
'*******************************************************************
End Subコードの解説
コードとして押さえておくべきポイントは2つです。
- オブジェクト変数
- WorkbooksクラスのOpenメソッド
理解出来るとワークブック間の作業やワークシート間の作業に関するコードが書ける様になります。
オブジェクト変数
オブジェクト変数の説明用に画像を2枚用意しました。最初の画像は変数についての考え方です。
変数がどうやってメモリに格納されているのか分かる様になります。
変数は用意した情報をそのまま格納する
文字列型の hello という変数に こんにちは という文字列を格納してみます。
Excelは文字列型の変数が格納できる分のメモリ（ここでは格納先番号1001)を用意します。
用意された場所に変数の中身である こんにちは を格納します。
変数といえば箱のイメージですが実際は箱だけではないです。
変数を定義するという事は画像の様にExcelのメモリ内の一定の番地に変数に代入した値を記憶しています。
コード内で変数を書く時はExcelは格納した値を取出してコードとして使うという建付けです。
オブジェクト変数はオブジェクトの格納先を示す
続いてオブジェクト変数です。
オブジェクト変数とは対象となるオブジェクトの格納先が示された変数です。
ワークブックやワークシートはExcel起動時に既にオブジェクトとして定義されています。
ThisWorkbook・・・とコードを書くと作業中のワークブックが認識されるのはこのためです。
既に定義されているワークブックをもう1回変数として定義すると2重定義になってしまいます。
そこでSet・・・というコードを書く事でオブジェクト変数には中継点になってもらいます。
（画像内の格納先の番号は説明用に適当に用意した数値です。本来の数値とは異なります。）
オブジェクト変数自体は1111番地に居ますがあくまで本体の格納先が明記されているだけです。
最終的には1536番地にあるワークブックを見に行く事になります。
これでオブジェクト（ここではワークブック）の定義を1本化しています。
ワークブックやワークシートをオブジェクト変数として定義する理由を説明させていただきました。
Openメソッド
Microsoftさんのリファレンスです。
Openメソッドには沢山の引数を指定する事ができます。各自で調べてみて下さい。
expression.Open (FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
https://learn.microsoft.com/ja-jp/office/vba/api/excel.workbooks.open
ここでは必要最低限の使い方を説明していきます。
Workbooksクラスに続けて使うケースを想定してコードを示しています。
VBEに書く時は最後の As Workbook は不要です。ご注意ください。
（As ・・・はワークブックを返すという説明です。オブジェクトブラウザーもこの表記です。）
複数のワークブック（コレクション）の中から引数で指定したワークブックを返してというコードです。
引数として指示されているFilenameはワークブック名で良いな・・・と思いがちですがパスも必要です。
例
デスクトップにあるA.xlsxを指定する場合のFilenameは以下の様になります。
.xlsxが配置された場所と.xlsxのブック名が必要です。
C:\・・・（ここは省略します）・・・\Desktop\A.xlsx
パスの書き方には絶対パスと相対パスがあります。先程紹介したのは絶対パスです。
絶対パスと相対パスについてはこちらの記事で詳しく紹介しています。
間違えやすいポイント
パスとワークブック名を￥（もしくはバックスラッシュ）で繋ぐ事を忘れない様にしましょう。
紹介したコードの36行目をご覧ください。。
“\”という記号でパスとワークブック名を繋いでいます。
紹介したコードではバックスラッシュになっています。ご注意ください。
この1文字が無い為にエラーになっている事が非常に多いです。
加えて ＆（アンパサンド） の使い方も悩まれる方が多いです。
参考になる記事を用意しておきます。
コード全般の解説
大きく分けるとやっている事は5つです。
- 1変数の定義
- 2コピー元のワークブックへの処理
- 3貼り付け先ワークブックへの処理
- 4保存時の条件分岐
- 5後片付け
1、変数の定義
各種変数を定義します。
ワークブックやワークシートを格納するオブジェクト変数を定義する事をわすれない様にしましょう。
2、コピー元のワークブックへの処理
ブックとシートをセットします。
表の最終行、最終列を取得しコピー範囲を確定させます。最後に範囲を指定してコピーをとります。
3、貼り付け先ワークブックへの処理
貼り付け先のブックとシートをセットします。
指定したブックを開く かつ オブジェクト変数にセットするというコードを書いています。
ブックA～Cへの貼り付けに対応するため変数wsを使ってブックとシートをセットしています。
今回はコピー元の.xlsmとコピー先の.xlsxが同じフォルダ内に配置されています。
よって4つのデータのファイルパスは全て同じです。ワークブック名だけ異なります。
36行目のコードは全て.xlsmと同じパスになりますので ThisWorkbook.Path を使っています。
最後にペースト（貼り付け）です。貼り付けは通常の貼り付けを使っています。（文字列で貼り付け）
値貼付けのコードを45行目に書いておいたので必要に応じて書き換えて下さい。
値貼り付けについてはこちらの記事でさらに詳しく解説しています。
PasteSpacialメソッドについて解説VBA×コピペ｜値のみ貼り付け＆簡単高速にコピーできる方法を公開
4、保存時の条件分岐
間違えて想定外のシートを指定してマクロを実行した時の救済のために条件分岐を用意しています。
まずメッセージボックスで保存先のワークシートにコピーしても良いかの確認をします。
メッセージボックスのOKボタンが押下されたらブックの保存に入ります。
保存後はブックを閉じるというコードにしていますが確認が必要な場合もあります。
その際はブックを閉じないようにしましょう。57行目のコードをコメントアウトしてください。
メッセージボックスでCancelボタンが押下されたら保存されないままブックを閉じる事になります。
お知らせメッセージボックスでファイルの保存をコントロールするには以下記事が参考になります。
参考VBA｜MsgBoxの「保存」ボタンを使わずにデータをSaveする
5、後片付け
DisplayAlertsとScreenUpdating共にTrueに戻します。
最後にメッセージボックスで作業完了のお知らせをします。
まとめ
ファイル間の転記（コピペなど）作業について解説しました。
オブジェクト変数の考え方が難しいですね。
何回かコードを書いていただければ分かる様になりますので根気強くコードを書いてください。
今回は「コピー&ペーストは1回の作業で1ヶ所ではなく複数存在する」を再現出来ていません。
紹介したコードの様に範囲指定してコピー＆ペーストすることで1回の作業にまとめる事が出来ます。
どうしてもコピー＆ペーストが複数回必要であれば条件分岐と繰り返し処理を使ってアレンジしてください。
関連記事を用意しておきます。
マクロを勉強できる【エラーにならない】 VBA条件分岐完全ガイドコードのテンプレを紹介
