takuocean’s diary

備忘録がメインなので、説明が足りないことも多い。もう少し詳しくという人はコメントをお願いします。

複数Excelファイルの一括置換ツール作成

以下のサイトを参考にVBAを使って一括置換ツールを作成します。

http://t-wata.com/?p=284

  • やりたいこと
    - 置換リストの作成
    - 複数ファイルの一括置換

* 置換リストの作成 以下の画像のようにリストを作成

f:id:takuocean:20170130231342p:plain

構成は
- 1列目:置換したいファイル名
- 3列目:置換したい文字
- 5列目:置換後の文字

* 複数ファイルの一括置換 作成したVBAのツールは以下のとおり。

Sub replaceStrFromFiles()

    Dim book As Workbook
    Dim sheet As worksheet
    
    Dim filename_fullpath As String     ' ファイル名(フルパス)
    Dim filename As String ' ファイル名
    Dim tmp As Variant
    
    Dim row_list As Long
    Dim row As Long
    
    Dim find_str As String
    Dim replace_str As String

    find_str = Cells(1, 3)
    replace_str = Cells(1, 5)
    
    ' リストの先頭から最後まで繰り返す
    For row_list = 2 To Cells(Rows.Count, 1).End(xlUp).row
        ' ファイル名を取得
        filename_fullpath = Cells(row_list, 1).Value
        
        ' フルパスからファイル名を抽出
        tmp = Split(filename_fullpath, "\")
        filename = tmp(UBound(tmp))
        
        ' ファイルをオープンする
        Workbooks.Open (filename_fullpath)
        
        ' オープンしたブックをアクティブにする
        Set book = Workbooks(filename)
        
        ' 保存時の警告を非表示にする
        book.CheckCompatibility = False
        
        'すべてのシートに対して操作を行う。
        For Each sheet In book.Worksheets
            For row = 1 To Cells(Rows.Count, 1).End(xlUp).row
                Cells(row, 1) = Replace(Cells(row, 1), find_str, replace_str)
            Next
        Next
        
        '保存して閉じる
        Workbooks(filename).Close SaveChanges:=True
        
    Next
End Sub

モジュールを作成したら、先ほど作ったリストのボタンに登録する。
sample1.xlsx が以下のようなとき

f:id:takuocean:20170130232313p:plain

置換ツールを実行すると

f:id:takuocean:20170130232315p:plain

となります。

これを応用すれば列だけでなく行の変換も可能