EXCEL VBA

Excel-VBA 実例 テキスト文書の文字列を一括置換

EXCEL VBA

こんにちは! 健史(たけふみ)です。

この記事は、Excel-VBAを起動して貼り付ければ動作することを目指しています。

最初は、真似ることです。

少しずつ何回も Excel-VBAの起動、実行に慣れて、プログラムにも目を通して理解を深め、自然に手が動くようになるまで頭の回路に焼き付けて頂ければと思います。

そして、活用できるレベルにいって頂ければと嬉しく思います。

Excel-VBAの構造、起動方法、実行方法などを理解されていない方は、以下の記事を参照しながら一度やってみてから対応されることをお勧めします。

Excel-VBA 実例 この通りやれば動く、とにかく触ってみよう
こんにちは! 健史(たけふみ)です。 パソコンをお使いの方の多くは、表計算ソフトExcelを利用されていると思います。 この記事は、Exceは知っているけれど ・Excel-VBAって何? ・Excel-VBAは取っ付きにく...

実行中に発生したエラーの対処法は、上記記事の2.エラーが発生したときの対処法に記載しています。(この段落のリンクから直接遷移します)

スポンサーリンク

プログラムの作成と実行

今回のサンプルプログラムは、

フォルダ内にある全テキストファイルの指定文字列を一括置換する

です。

単に文字列を置き換えるだけであれば、フリーソフト”サクラエディタ”の「Grep置換」でできますし、ニーズとしては多くないでしょう。

ですが、Excel-VBAでも「こんなこともできるんだぁ!」ということを知って頂ければと思います。


プログラムの概要は以下の通りです。

・所定のファルダ(IN)にある拡張子が".txt"となっているファイルを順次読み込み、"2020/05/01"~"2020/05/30"となっている文字列を"2020/05/31"に置き換える

・置き換えた後に別の所定フォルダ(out)に書き込む


イメージは以下の通りです。

実行前後

         ↓

対象ファイルを上書きせずに別フォルダに書き込むのは、万が一間違えて置き換えてしまった場合、元に戻すのがたいへんだからです。

手で対応する場合には、修正前にバックアップしておいて上書きするとして
①.エクスプローラでフォルダを開き、対象ファイルを開く(ダブルクリック、もしくは、[Enter]で)
②.編集(E)-置換(R)で置換画面を開く
③.検索する文字列(N)に"2020/05/01"、置換後の文字列(P)に"2020/05/31"を入力
④.すべて置換(A)をクリック
⑤.「③に戻り、検索する文字列(N)を"2020/05/02"へ変更、④(すべて置換(A)をクリック}」を"2020/05/30"までを繰り返す
⑥.ファイル(F)-上書き保存(S)

そして、①から⑥を全てのファイルに行うことでしょう。

そんな時に対応するプログラムです。

実行までの手順

1.データ準備
1).[01.IN]フォルダと[02.OUT]フォルダを作成
この例ではGドライブに作成しています。

2).[01.IN]フォルダ内に以下の3ファイルを作成

2.Excelを起動し、[01.IN]フォルダと[02.OUT]フォルダ設定
1).シート1に以下のデータを貼り付ける
ドラッグ&コピー後にExcelに貼り付けるとき、[形式を選択して貼り付ける]-[テキスト]で貼り付けます。(セル[A1]で[右クリック]、[形式を選択して貼り付ける]-[テキスト])

	参照フォルダ
ドキュメント IN	G:\01.IN
	
ドキュメント OUT	G:\02.OUT

プログラムを実行する場合のフォルダ名は、実際の環境に合わせて修正します。

3.Excel-VBAを起動
Altを押した状態でF8キーを押します。

4.[マクロ名(M):]に'test'と入力(''は不要、''内のtestを入力)

5.[作成(C)]をクリック
私はマクロ名を入力したら、そのままEnterを押します。

6.以下のプログラムをドラッグ&コピー

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub test()
'入力フォルダ内を読み込むときの対象を定義、拡張子が[txt]飲みを対象にする
    Const cnsDIR = "\*.txt"
'フォルダやファイルの場所用の変数を定義
    Dim strPath1 As String
    Dim strPath2 As String
'ファイル名を格納するテーブルを定義
    Dim tblFilename1(500) As String
    Dim i, last_i As Long
'入力フォルダから取得するファイル名用の変数を定義
    Dim strFilename As String
'出力フォルダに出力するファイル名用の変数を定義
    Dim savFilename As String
'その他
    Dim temp As String
    Dim day1 As Long
'フォルダやファイルの場所をワークに取り込む
    strPath1 = Worksheets(1).Cells(2, 2)
    strPath2 = Worksheets(1).Cells(4, 2)
'フォルダの存在確認 --- 必要な場合のみ記述 ---
    If Dir(strPath1, vbDirectory) = "" Then
        MsgBox "アンケートフォルダがない", vbExclamation
        Exit Sub
    End If
    If Dir(strPath2, vbDirectory) = "" Then
        MsgBox "返信フォルダがない", vbExclamation
        Exit Sub
    End If
'入力フォルダ内の最初のファイルを取得
    strFilename = Dir(strPath1 & cnsDIR, vbNormal)
    i = 0
'入力フォルダ内のファイルをテーブルにプール
    Do While strFilename <> ""
        ' 行を加算
        i = i + 1
        tblFilename1(i) = strFilename
'次のファイル名を取得
         strFilename = Dir()
    Loop
'入力ファイルの件数を退避
    last_i = i
'実行中、Escキーを押しても停止しない設定、
    Application.EnableCancelKey = xlDisabled
'入力ファイルを読み込み、出力ファイルを編集して出力
    For i = 1 To last_i
'入力ファイルのリンクを生成
        savFilename = strPath1 & "\" & tblFilename1(i)
'メモ帳を起動
        Shell "notepad.exe", 1
        Sleep 2000
'入力ファイルを開く
        SendKeys "%FO", True
        SendKeys savFilename, True
        Sleep 1000
        SendKeys "%O", True
        Sleep 1000
'文字列を置換
        For day1 = 1 To 30
            SendKeys "%er", True
            If day1 < 10 Then
                temp = "2019/10/0" & day1
            Else
                temp = "2019/10/" & day1
            End If
            SendKeys temp, True
            Sleep 100
            SendKeys "{Tab}", True
            Sleep 100
            SendKeys "2019/10/31", True
            Sleep 100
            SendKeys "%A", True
            Sleep 100
            SendKeys "{Esc}"
        Next
'出力ファイルのリンクを生成
        savFilename = strPath2 & "\" & tblFilename1(i)
'存在しない場合のみ書く
        If Dir(savFilename, vbDirectory) = "" Then
            SendKeys "%FA", True
            SendKeys savFilename, True
            Sleep 1000
            SendKeys "%S", True
            Sleep 2000
            SendKeys "%{F4}", True
        Else
            SendKeys "%{F4}", True
            Sleep 1000
            SendKeys "%N", True
        End If
     Next
'起動しているExcelを終了する
     Application.Quit
End Sub

6.以下に貼り付け(ペースト)
以下の[Sub test()~End Sub]を全て消してから貼り付ける。

Sub test()
End Sub

7.F5キーで実行
プログラムが起動し、ファイルを一つひとつ読み込み、文字列を置き換え、出力フォルダに書き込む動作が自動で実行されます。

8.実行結果の確認
出力フォルダには、入力フォルダ内のファイル名と同じ名前のファイルが生成されています。

ファイルの内容を確認すると、すべて日付が"2020/05/31"に置き換わっています。

プログラムの詳細説明

最初に、入力フォルダ内の拡張子が"txt"となっている全テキストファイル名をプログラム内のテーブル[tblFilename1]にプールします。

そして、メモ帳を起動し、tblFilename1を開くファイルに指定して開きます。

入力フォルダ内のテキストファイル名を取得する処理は、以下の記事を参照下さい。

Excel-VBA 実例 重複チェックして重複データを抽出する
こんにちは! 健史(たけふみ)です。 この記事は、Excel-VBAを起動して貼り付ければ動作することを目指しています。 最初は、真似ることです。 少しずつ何回も Excel-VBAの起動、実行に慣れて、プログラムにも目を...

For文については、以下の記事を参照下さい。

Excel-VBA 実例 この通りやれば動く、とにかく触ってみよう
こんにちは! 健史(たけふみ)です。 パソコンをお使いの方の多くは、表計算ソフトExcelを利用されていると思います。 この記事は、Exceは知っているけれど ・Excel-VBAって何? ・Excel-VBAは取っ付きにく...


上記記事にない部分を追記します。

・Sleep命令を有効にするための定義
今回のプログラムでメモ帳を起動後に、例えば「ファイルを開く」のは[ファイル(F)]-[開く(O)]ですが、その動作をAltを押した状態でF Oを手入力するショートカットを使って実現します。

パソコンの性能が低かったりバックでウィルススキャンなどが動作していると、ショートカットコマンド間やショートカットとデータ入力の間などでコンピュータが処理するのに時間が掛かる場合があり、その時間待つ必要があり、一定時間待つ命令が「Sleep」です。

Sleep命令を使うには最初に宣言しておく必要があり、その宣言が最初に記述してある
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
です。

Sleepの後の変数は、1ミリ秒(1000分の1秒)単位で設定し、1秒待つ場合には、"Sleep 1000"と記述します。

・実行中、Escを押しても停止しない設定
Application.EnableCancelKey = xlDisabled

作成したプログラムの実行中に誤って処理を中断するEscを押しても効かない設定です。

プログラムの中でEscを使いますが、誤ってEscを押してしまったことで処理が誤動作する可能性があるかもしれませんので、記述しておくことがよいと思います。

・値の入力やファンクションキーを押す操作
SendKeys "%FO", True

SendKeys命令を使います。

文字を直接入力したい場合には、""で囲みます。

もしくは[SendKeys sndkeyFileopen, True]のように変数名を指定します。

Dim sndkeyFileopen As String ;

sndkeyFileopen = "%FO" ;

SendKeys sndkeyFileopen, True

"%"はAltです。
ファンクションキーなどは"{}"で囲みます。

ネットにて"Sendkeys"で検索頂ければ出てきます。

注意点

今回の例では、"2020/05/01"や"2020/05/15"といった「月/日」が「mm/dd」の文字列の場合に対応したプログラムでした。

"2020/05/01"が"2020/05/1"となっている場合には対応できません。

別の処理を記述して試行したのですが、意図した通りに動作しませんでした。

最後に

「テキストファイルの文字列を一括で置換するプログラム」でした。

Wordファイルの文字列を一括置換するプログラムは以下です。

Excel-VBA 実例 Word文書の文字列を一括置換
こんにちは! 健史(たけふみ)です。 この記事は、Excel-VBAを起動して貼り付ければ動作することを目指しています。 最初は、真似ることです。 少しずつ何回も Excel-VBAの起動、実行に慣れて、プログラムにも目を...

Excelファイルの文字列を一括置換するプログラムは以下です。

Excel-VBA 実例 Excelの文字列を一括検索・置換
こんにちは! 健史(たけふみ)です。 この記事は、Excel-VBAを起動して貼り付ければ動作することを目指しています。 最初は、真似ることです。 少しずつ何回も Excel-VBAの起動、実行に慣れて、プログラムにも目を...
EXCEL VBA
スポンサーリンク
- 面白かったらシェアお願いします! -
健史をフォローする
自分で改善

コメント