こんにちは！ 健史です。
手動で行う操作、例えば、
・フォルダに保管されているテキストファイルを一つひとつ開く
・特定の文字列を検索して、あれば別の文字に変更する
・ファイルを上書きする
を自動化したいということありませんか。
プログラムで指定したフォルダ内のテキストファイルの文字列を修正する処理を作成してみました。
プログラムの作成と実行
今回のサンプルプログラムは、
です。
単に文字列を置き換えるだけであれば、フリーソフト”サクラエディタ”の「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]フォルダ内に以下の３ファイルを作成
2.Excelを起動し、[01.IN]フォルダと[02.OUT]フォルダ設定
1).シート１に以下のデータを貼り付ける
ドラッグ＆コピー後にExcelに貼り付けるとき、[形式を選択して貼り付ける]-[テキスト]で貼り付けます。(セル[A1]で[右クリック]、[形式を選択して貼り付ける]-[テキスト])
設定項目 設定内容 ドキュメントIN G:\01.IN ドキュメントOUT G:\02.OUT
プログラムを実行する場合のフォルダ名は、実際の環境に合わせて修正します。
3.Excel-VBAを起動
Altを押した状態でF8キーを押します。
4.[マクロ名(M):]に'test'などと入力(''は不要、''内のtestを入力)
しかし、今回のプログラムを手順に従い登録すると、マクロ名はプログラム内に記述されているマクロ名[MAIN00]になります。
5.[作成(C)]をクリック
私はマクロ名を入力したら、そのままEnterを押します。
6.以下のプログラムをドラッグ＆コピー
#If VBA7 And Win64 Then
'64Bit版用
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
'32Bit版用
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
Sub MAIN00()
'入力フォルダ内を読み込むときの対象を定義、拡張子が[txt]飲みを対象にする
Const cnsDIR = "\*.txt"
'フォルダやファイルの場所用の変数を定義
Dim strPath1 As String
Dim strPath2 As String
'ファイル名を格納するテーブルを定義
Dim ix1, ix1_max As Long
'入力フォルダから取得するファイル名用の変数を定義
Dim strFilename As String
'出力フォルダに出力するファイル名用の変数を定義
Dim savFilename As String
'その他
Dim temp As String
Dim day1 As Long
'フォルダやファイルの場所をワークに取り込む
strPath1 = Sheets(1).Cells(2, 2)
strPath2 = Sheets(1).Cells(3, 2)
'フォルダの存在確認 --- 必要な場合のみ記述 ---
If Dir(strPath1, vbDirectory) = "" Then
MsgBox "ドキュメントINフォルダがない", vbExclamation
Exit Sub
End If
If Dir(strPath2, vbDirectory) = "" Then
MsgBox "ドキュメントOUTフォルダがない", vbExclamation
Exit Sub
End If
'
Select Case Sheets.Count 'シート２，シート３を追加し忘れた時の対応
Case 1
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "ワーク"
End Select
Sheets(2).Cells.Clear
'入力フォルダ内の最初のファイルを取得
strFilename = Dir(strPath1 & cnsDIR, vbNormal)
ix1 = 0
Do While strFilename <> ""
' 行を加算
ix1 = ix1 + 1
Sheets(2).Cells(ix1, 1) = strPath1 & "\" & strFilename
Sheets(2).Cells(ix1, 2) = strPath1
Sheets(2).Cells(ix1, 3) = strFilename
strFilename = Dir()
Loop
ix1_max = ix1
'実行中、Escキーを押しても停止しない設定、
Application.EnableCancelKey = xlDisabled
'入力ファイルを読み込み、出力ファイルを編集して出力
For ix1 = 1 To ix1_max
'入力ファイルのリンクを生成
savFilename = Sheets(2).Cells(ix1, 1)
'メモ帳を起動
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 = "2020/05/0" & day1
Else
temp = "2020/05/" & day1
End If
SendKeys temp, True
Sleep 100
SendKeys "{Tab}", True
Sleep 100
SendKeys "2020/05/31", True
Sleep 100
SendKeys "%A", True
Sleep 100
SendKeys "{Esc}"
Next
'出力ファイルのリンクを生成
savFilename = strPath2 & "\" & Sheets(2).Cells(ix1, 3)
'存在しない場合のみ書く
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を終了する
ThisWorkbook.Save
MsgBox "処理終了！"
Application.Quit
End Sub
6.以下に貼り付け(ペースト)
以下の[Sub test()～End Sub]を全て消してから貼り付ける。
Sub test() End Sub
7.F5キーで実行
プログラムが起動し、ファイルを一つひとつ読み込み、文字列を置き換え、出力フォルダに書き込む動作が自動で実行されます。
8.実行結果の確認
出力フォルダには、入力フォルダ内のファイル名と同じ名前のファイルが生成されています。
ファイルの内容を確認すると、すべて日付が"2020/05/31"に置き換わっています。
プログラムの詳細説明
・Sleep命令を有効にするための定義
今回のプログラムでメモ帳を起動後に、例えば「ファイルを開く」のは[ファイル(F)]-[開く(O)]ですが、その動作をAltを押した状態でF Oを手入力するショートカットを使って実現します。
パソコンの性能が低かったりバックでウィルススキャンなどが動作していると、ショートカットコマンド間やショートカットとデータ入力の間などでコンピュータが処理するのに時間が掛かる場合があり、その時間待つ必要があり、一定時間待つ命令が「Sleep」です。
Sleep命令を使うには最初に宣言しておく必要があり、その宣言が最初に記述してある
#If VBA7 And Win64 Then '64Bit版用 Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) #Else '32Bit版用 Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) #End If
です。
VBAのバージョンによって宣言する内容が異なりますので、上記内容に変更しました。(2021/03/21)
Sleepの後の変数は、1ミリ秒(1000分の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"となっている場合には対応できません。
別の処理を記述して試行したのですが、意図した通りに動作しませんでした。
最後に
「テキストファイルの文字列を一括で置換するプログラム」でした。
Ｗｏｒｄファイルの文字列を一括置換するプログラムは以下です。
Ｅｘｃｅｌファイルの文字列を一括置換するプログラムは以下です。
尚この記事は、Excel-VBAを起動して貼り付ければ動作することを目指しています。
Excel-VBAの構造、起動方法、実行方法などを理解されていない方は、以下の記事を参照しながら一度やってみてから対応されることをお勧めします。
実行中に発生したエラーの対処法は、上記記事の2.エラーが発生したときの対処法に記載しています。(この段落のリンクから直接遷移します)
コメント