大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAでテキストファイル内のデータを任意のデータへ置換する方法をご紹介します。サブフォルダ内のすべてのテキストデータも一括で置換することができます。

はじめに

テキストデータをExcel VBAを使って任意のデータに置換する方法をご紹介します。

サブフォルダ内のテキストデータも一括で変換できるので便利です。

私はブログのhtmlのアーカイブなどを一括で更新する際に使用しています。アイディア次第でいろいろな用途に応用が利くコードかと思います。

やりたい効率化の内容

  • テキストファイルのデータを任意のデータへ置換できます
  • 本ファイルが保存されているフォルダ内のテキストファイルを置換します
  • サブフォルダを含むすべてのテキストファイルを置換できます
  • UTR-8形式で保存されたテキストデータを置換できます
  • ちなみに自作ブログだと・・・htmlで作ったブログ内のページにあるアーカイブを一括で置換することができます

本記事の内容を動画でまとめています

この記事で紹介するExcel VBAのコードを実行するとこんなことができます。Excel VBAの実行結果をパッと見たい方はどうぞご覧ください。

目次から見たい項目へ移動すると便利ですよ。

目次

フォルダ構成

次のようなフォルダ構成でフォルダとサブフォルダ、テキストデータを保存しています。

置換するフォルダ構成

Excel VBAコードが記載されたマクロファイルと同フォルダに複数のフォルダとサブフォルダ、.html形式のファイルが保存されています。

フォルダ構成

今回は.html形式のファイルを置換していますが、.txt形式のファイルでもExcel VBAコードの『.html』の部分を変換したい拡張子名に変えれば同じように置換ができます。

置換後のテキストデータ(エクセル側)

置換後のテキストデータをエクセル側へあらかじめ保存しておきます。

置換後のテキストデータとターゲットデータ

置換後のテキストデータとターゲットデータ

今回は<!--置換始め-->と<!--置換終了-->をターゲットにしてその間のテキストを置換します。もちろん他のテキストでも大丈夫です。

置き換えるデータは『この値に置換します』の部分です。

置換したいテキストデータ(テキストファイル側)

テキストファイルには置換したいテキストを<!--置換始め-->と<!--置換終了-->で挟むように記述しておきます。

置換したいテキストデータとターゲット

置換したいテキストデータとターゲット

<!--置換始め-->と<!--置換終了-->の間が置換されます。

置換した結果

置換した結果です。エクセルデータに記載したデータがテキストファイルに入力されていることがわかります。

テキストファイルのデータが任意のデータへ置換されています

置換した結果

他のサブフォルダ内のテキストファイルのデータも置換されています。

サブフォルダ内のテキストデータも置換できています

サブフォルダ内のテキストデータも置換できています

サブフォルダ内のテキストデータも置換できています

Excel VBAのコード

Excel VBAのコードです。3つのコードがあります。

『置換実行するコード』と『サブフォルダも含めたフォルダ内のhtmlファイルを探索するコード』、『テキストデータを任意のデータに置換するコード』です。

すべてExcel VBAの標準モジュールへ記載します。

VBAコード:置換実行するコード

Sub ReplaceText()
    
    fc = MsgBox("ファイルのデータを置換しますか?", vbQuestion + vbYesNo, "確認")
    If fc = vbNo Then Exit Sub
    
    '書換あり
    Call SearchSubFolder(ThisWorkbook.Path)
    
    MsgBox ("置換しました")
    
End Sub

置換するフォルダをこのマクロファイルがあるフォルダとするため『ThisWorkbook.Path』と指定しています。

VBAコード:サブフォルダも含めたフォルダ内のhtmlファイルを探索するコード

'サブフォルダも含めたフォルダ内のhtmlファイルを探索します。
'入力
'Path・・・探索するフォルダパス
Sub SearchSubFolder(ByVal Path As Variant)
    
    '参照設定します
    Dim FSO As Object, Folder As Variant, File As Variant
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'サブフォルダ内も探索します
    For Each Folder In FSO.GetFolder(Path).SubFolders
        Call SearchSubFolder(Folder.Path)
    Next Folder
    
    'フォルダ内の.htmlファイルを探索します
    Dim FilePath As Variant
    ReDim FilePath(1 To 100, 1 To 1) As Variant
    i = 0
    For Each File In FSO.GetFolder(Path).Files
        If InStr(File.Name, ".html") > 0 Then
            i = i + 1
            FilePath(i, 1) = File.Path
        End If
    Next
        
    'フォルダ内のファイルを任意のデータに置換します
    Call Func_Replace(FilePath)
    
End Sub

フォルダ一つ一つを探索してフォルダ内にある.htmlファイルのフルパスをすべて取得します。

取得した.htmlファイルのフルパスのすべてを次の『Func_Replace』のマクロへ渡します。

VBAコード:テキストデータを任意のデータに置換するコード

'全ファイル内のテキストを任意のデータに置換します
'入力
'FilePath・・・置換するファイルのフルパス(配列)
Sub Func_Replace(ByVal FilePath As Variant)
    
    '置換の始めと終わりのターゲットを格納
    Dim RepalceStart, ReplaceLast
    With Worksheets("置換")
        ReplaceStart = .Cells(1, "B")
        ReplaceLast = .Cells(2, "B")
    End With
    
    '置換するデータを格納
    Dim ReplaceData, LastRow
    With Worksheets("置換")
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        ReplaceData = .Range(.Cells(6, "A"), .Cells(LastRow, "A"))
    End With
    
    Dim Hozon As Variant
    Dim flag
    
    Dim buf As String, Target As String
    For k = 1 To UBound(FilePath, 1)
        
        If IsEmpty(FilePath(k, 1)) = False Then
            
            'ファイルの全データを取得(UTF-8形式で取得します)
            Target = FilePath(k, 1)
            With CreateObject("ADODB.Stream")
                .Charset = "UTF-8"
                .Open
                .LoadFromFile Target
                buf = .ReadText
                .Close
            End With
                            
            '改行区切りで1行ごとに分けて配列として保存します
            Hozon = Split(buf, vbLf)
                            
            'ここで任意のデータに置換していきます
            buf = ""
            flag = 0
            '1行ずつのデータを一つのデータにまとめます
            For i = 0 To UBound(Hozon, 1)
                
                '最終行より前(改行をつけて保存します)
                If i < UBound(Hozon, 1) Then
                    
                    '置換終了のターゲットの場合
                    If InStr(Hozon(i), ReplaceLast) > 0 Then
                        flag = 0 '置換フラグをオフ(元のデータを保存していきます)
                    End If
                    
                    '置換フラグがオフの場合
                    If flag = 0 Then
                        buf = buf & Hozon(i) & vbLf '元のデータを保存します(改行をつけます)
                    End If
                    
                    '置換開始のターゲットの場合
                    If InStr(Hozon(i), ReplaceStart) > 0 Then
                        flag = 1 '置換フラグをオン(元のデータを保存しないようにします)
                        '置換するデータを挿入
                        For j = 1 To UBound(ReplaceData, 1)
                            buf = buf & ReplaceData(j, 1) & vbLf
                        Next
                    End If
                    
                '最終行(改行しないで保存します)
                ElseIf i = UBound(Hozon, 1) Then
                    buf = buf & Hozon(i) '元データを保存します(最後なので改行はしません)
                End If
            Next
            
            'ファイルにデータを格納(UTF-8形式で入力します)
            With CreateObject("ADODB.Stream")
                .Charset = "UTF-8"
                .Open
                .WriteText buf, 0
                .SaveToFile Target, 2
                .Close
            End With
            
        End If
        
    Next
    
End Sub

.htmlファイルのフルパスから.htmlファイル内のテキストデータを『ADODB.Stream』で取得します。

取得したテキストデータを改行毎に配列『Hozon』へ格納します。

『ここで任意のデータに置換していきます』以下であらかじめエクセルに保存しておいた置換後のデータをテキストデータへ格納していきます。

最終的に1つのデータ『buf』にすべてのテキストデータを保存し.htmlファイルへ戻してやります。

.htmlの部分を.csvや.txtに変えれば同じように置換することができますので応用が利くと思います。

また、『全ファイル内のテキストを任意のデータに置換』コードの『ここで任意のデータに置換していきます』の部分を変更すれば、指定行した行のみを取得したり置換したりができます。

おわりに

テキストデータを任意のデータに置換する方法をご紹介しました。

サブフォルダ内を含むすべてのテキストデータの操作ができるコードになっていますので使い方次第では業務の効率化が図れるかと思います。

私は同じようなコードで大量のテキストデータから任意のデータのみを取り出して一覧表を作成するといった使い方もしたりしています。

人間の手でテキストデータからデータをコピペなんかしてると大変ですし手首が痛くなってきちゃいますからね。Excel VBAで効率化することをおすすめします。

最後までご覧くださいましてありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す