【Excel VBA】コピペ自動化マクロの紹介

本投稿では、ニーズプラットフォームに寄せられたニーズの解決事例を紹介します。

ニーズの整理

内容

「マスタExcelファイルの更新にともなう関連Excelファイルのアップデート作業」を自動化したい！

業務手順

フォルダAに格納されている全てのExcelファイルについて、マスタExcelファイルが更新されるたびに下記の作業を手動で実施している。

1. フォルダAに格納されているExcelファイルAのシート1 (Table.2) について、マスタExcelファイルによるアップデート箇所のみ削除する (枠線等あるためフォーマットは残したまま)
2. マスタExcelファイルのシート1 (Table.1) にあるデータをコピーし、フォルダAに格納されているExcelファイルAのシート1に貼り付ける
3. 手順1～2をフォルダAに格納されている残りのExcelファイルB, C, Dに対して繰り返し実施する

ニーズ・ミライ研究所

Table.1のA, B列の値をTable.2のC, D列に貼り付けます。

Table.1 マスタExcelファイル

Table.2 コピー先のExcelファイル (実行前)

到達点

Excel VBAを用いて上記のコピペ作業を自動化する。

プログラム概略

動作内容

1. フォルダAに格納されているいずれかのExcelファイルのシート1を開き、マスタExcelファイルのアップデートによる影響が及ぶ範囲の値を全削除する。
2. マスタExcelファイルのシート1をコピーする。(ctrl+cと同じ)
3. 手順1で消した部分にペーストする。(ctrl+vと同じ)
4. 手順1～3をフォルダAの全てのExcelファイルに対して繰り返す。

その他の仕様

• 業務で使用するExcelファイルとは別にマクロ用のExcelファイルを作成する。
• 作業者がソースコードにアクセスせずに一連の動作を行えるよう、マクロを実行するためのボタンをシート上に設置する。
• 更新される可能性がある情報はシートに記載し、ソースコードから呼び出す。
• マクロによるデータの上書きは不可逆的なものになるため、実施時に警告が出るようにする。

プログラム紹介

UIとソースコード

Option Explicit

Sub copy_data()

  'wb1:本Excelファイル,wb2:マスタExcelファイル,wb3:コピー先Excelファイル
  Dim wb1, wb2, wb3 As Workbook
  'ws1:本Excelファイルのシート1,ws2:マスタExcelファイルのシート1,ws3:コピー先Excelファイルのシート1
  Dim ws1, ws2, ws3 As Worksheet
  
  'wb1:本Excelファイル, ws1:本Excelファイルのシート1
  Set wb1 = ThisWorkbook
  Set ws1 = wb1.Worksheets("Sheet1")
  
  'wb2:マスタExcelファイル,ws2:マスタExcelファイルのシート1
  'ポイント(1) パスは本Excelファイルのシート1のA5セルに記載
  Set wb2 = Workbooks.Open(ws1.Range("A5").Value)
  Set ws2 = wb2.Worksheets("Sheet1")
  
  'ポイント(2) FileSystemObjectオブジェクトを作成
  'Excelマクロでフォルダやファイルにアクセスするために必要
  Dim fs As FileSystemObject
  Set fs = New Scripting.FileSystemObject

  'ポイント(1) 本Excelファイルのシート1のA2セルに記載されているパスのFolderオブジェクトを取得
  Dim basefolder As Scripting.Folder
  Set basefolder = fs.GetFolder(ws1.Range("A2").Value)

  '変数myfilesにフォルダA内の全てのファイルをFileオブジェクトとして格納
  Dim myfiles As Scripting.Files
  Set myfiles = basefolder.Files
  
  '変数myfileをFileオブジェクトとして定義する
  Dim myfile As Scripting.File
  
  'ポイント(3) ファイルを書き換える前に警告を出す
  MsgBox basefolder.Name & "内の全てのファイルのデータを上書きします。" & vbCrLf & "本当によろしいですか？", vbOKCancel + vbExclamation
  
  'フォルダに含まれるすべてのファイルに対して、下記のプログラムを実施する
  For Each myfile In myfiles
    
    'wb3:コピー先Excelファイル, ws3:コピー先Excelファイルのシート１1
    Set wb3 = Workbooks.Open(myfile.Path)
    Set ws3 = wb3.Worksheets("Sheet1")

    'コピー先ExcelファイルのD,E列を値のみ削除する
    ws3.Columns("D:E").ClearContents
    'マスタExcelファイルのA,B列をコピーする
    ws2.Columns("A:B").Copy
    '貼り付けたいエリアの一番左上(D1セル)に値のみ貼り付ける
    ws3.Range("D1").PasteSpecial Paste:=xlPasteValues
    
    'コピー先Excelファイルを上書き保存する
    wb3.Save
    '次のファイルに進むためにコピー先Excelファイルを閉じる
    wb3.Close

    '次のFileオブジェクトを格納するために変数ws3, wb3を解放する
    Set ws3 = Nothing
    Set wb3 = Nothing

  Next

'マスタExcelファイルを閉じる
wb2.Close

End Sub

ポイント(1) 今後変更される可能性が高い情報はセルに記載

フォルダやファイルはパスを指定してアクセスするため、これらのフォルダやファイルの保存先が変わると、指定されたパスが存在しないためエラーが発生します。

したがって、フォルダやファイルのパスが変わった場合、下記のような問題点が発生します。

• フォルダ整理のたびにプログラムを修正する手間がかかる
• 不慣れな担当者がプログラムを触ることによるヒューマンエラーが生じる可能性がある

セキュリティの関係でセルへの記載ができないという制約がなければ、フォルダやファイルのパスはセルに記載しておき、パスが変わった場合はセルだけ変える仕組みにしておくと便利です。

ポイント(2) Excel VBAからファイルやフォルダにアクセスする方法

Excel VBAからファイルやフォルダにアクセスするためには、FileSystemObjectオブジェクトという外部ライブラリを使用する必要があります。

FileSystemObjectオブジェクトは外部ライブラリのため、VBEのツール → 参照設定からMicrosoft Scripting Runtimeをオンにしておいてください。

こちらの使い方については下記の記事を引用させていただきました。FileSystemObjectオブジェクトを用いてフォルダ内の全てのファイルにアクセスする方法について、詳しく説明されています。

VBAでフォルダ内のエクセルファイルを全てに処理するマクロを紹介

フォルダ内のすべてエクセルファイルに処理するVBAプログラムを紹介します。 このページでは、以下のフォルダ内のエクセルファイルに同じ処理を実行します。 フォルダ内の「Book1～Book5」の各エクセルのセルA1～セルA5に数値を入れます。 さて、このような作業は手作業でも可能...

fastclassinfo.com

ポイント(3) 実行前のアラートの表示

フォルダAに格納すべきExcelファイルを周知徹底したとしても、ヒューマンエラーにより全く関係ない重要なExcelファイルが誤って格納される可能性があります。

本Excelマクロは各Excelファイルの保存まで完結してしまうため、目的が異なる重要なExcelファイルが格納されていると取り返しのつかないことになるかもしれません。

そこで、マクロを実行する前の注意喚起としてアラートを表示させます。もちろんアラートを無視する方もいると思いますが、多くの方がファイル名を確認したりバックアップを取ったりするのではないでしょうか？

実行結果

Table.3 コピー先のExcelファイル (実行後)

ニーズ・ミライ研究所

コピー先のExcelファイルのC, D列の数字がアルファベットの小文字に置き換わりました！

おわりに

今回Excel VBAによる業務効率化のニーズをいただいたため、記事にしました。

Excel VBAやGASを用いた業務効率化は私たちの得意の1つと考えていますので、簡単なものから少し難しいものまでニーズの投稿をお待ちしています！