HOME > 即効テクニック > Excel VBA > ファイル操作関連のテクニック > 異なる区切り文字の混在したテキストファイルを読み込む(OpenTextメソッド)

異なる区切り文字の混在したテキストファイルを読み込む(OpenTextメソッド)|Excel VBA

ファイル操作関連のテクニック

異なる区切り文字の混在したテキストファイルを読み込む(OpenTextメソッド)

(Excel 2000/2002/2003/2007/2010)

データの区切り文字が混在しているテキストファイルをワークシートに読み込むとき、Openメソッドで開くと思い通りに区切られず、一つのセルに1行分すべてが入ったり、複数の項目が入ってしまったりします。
OpenTextメソッドを使うと、区切り文字を指定することができるので、複数の区切り文字が混在したテキストファイルをワークシートに正しく読み込むことができます。
OpenTextメソッドの構文と主な設定項目は次のとおりです。

構文  Object.OpenText(Filename, Origin, StartRow, DataType, TextQualifier, 
                       ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, 
                       OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, 
                       ThousandsSeparator, TrailingMinusNumbers, Local)
設定項目内容
ObjectWorkbooksコレクションオブジェクトを指定[省略不可]
FileName読み込むテキストファイルの名前を指定[省略不可]
DataTypeファイルに含まれるデータの形式を指定。
区切り記号でフィールドが区切られた形式(xlDelimited:既定値)、固定長文字列の形式(xlFixedWidth) [省略可能]
Tab区切り文字にタブ を使う(True)/使わない(False) [省略可能]
Semicolon区切り文字に「;(セミコロン)」 を使う(True)/使わない(False)[省略可能]
Comma区切り文字に「,(カンマ)」を使う(True)/使わない(False)[省略可能]
Space区切り文字にスペースを使う(True)/使わない(False)[省略可能]
Other区切り文字に引数OtherCharで指定した文字を使う(True)/使わない(False)[省略可能]
OtherChar引数OtherがTrueのときは、必ずこの引数に区切り文字を指定[省略可能]

サンプル1では、"Staff.txt"というテキストファイルを読み込みます。

●Staff.txt●

社員番号,氏名,部署名,保有資格(複数可)		
M2079  東京太郎   営業一部         IC3/MOS/VBAエキスパート
W2870  丸の内 花子  経理・管理部     MOS/簿記2級
W3510  真黒 小百合  経営企画室       VBAエキスパート/英検1級
M3025  毛具一郎   システム開発部   Oracle Master/VBAエキスパート

このテキストファイルは、1行目がカンマ区切り、2行目以降がタブ区切りになっています。
4つ目の項目には「/(スラッシュ)」で区切られた複数の資格名が入力されています。
これを「/」で区切り、それぞれセルに読み込みます。

(1)または(2)を1つずつ有効にすると、OpenメソッドとOpenTextメソッドの動作の違いを確認できます。

●サンプル1●

Sub Sample()
    Dim TextPath As String

    'テキストファイルのパスを取得
    TextPath = ActiveWorkbook.Path & "\Staff.txt"
    
    'Openメソッドでテキストファイルを開く(1つのセルに1行分が入る)
    'Workbooks.Open TextPath    '---(1)

    'OpenTextメソッドでテキストファイルを開く(区切り文字でセルに分かれる)
    Workbooks.OpenText Filename:=TextPath, _
                       DataType:=xlDelimited, _
                       Tab:=True, _
                       Semicolon:=False, _
                       Comma:=True, _
                       Space:=False, _
                       Other:=True, _
                       Otherchar:="/"    '---(2)

    '列幅を調整する
    ActiveSheet.UsedRange.EntireColumn.AutoFit
    Range("A1").Select
End Sub

実行すると次の図のように、カンマ、タブ、スラッシュ区切りでワークシートにデータが読み込まれます。

■区切り文字の指定を省略すると

OpenTextメソッドのヘルプで引数Spaceの説明を見てみると、次のように書かれています。

「区切り文字にスペースを使うときは True を指定します。既定値は False です。」

つまり、引数Spaceを指定しないとFalseを指定したことになる、というわけです。
この点について実験してみます。
「Staff.txt」の「氏名」と「保有資格」には半角スペースを含むデータが混ざっています。
サンプル1の(2)のコードを変更しながら、マクロを実行してみましょう。

【(A)引数Spaceの指定を省略してマクロを実行】
(2)のコードから「Space:=False, 」の部分を削除して実行します。

Workbooks.OpenText Filename:=TextPath, DataType:=xlDelimited, Tab:=True, _
                   Semicolon:=False, Comma:=True, _
                   Other:=True, Otherchar:="/"

サンプル1と同じ結果が得られました。引数SpaceにFalseを指定したときと同じです。
問題なさそうですね。

【(B)引数SpaceにTrueを指定してマクロを実行】
「Space:=True,」のコードを追加して実行してみます。

Workbooks.OpenText Filename:=TextPath, DataType:=xlDelimited, Tab:=True, _
                   Semicolon:=False, Comma:=True, Space:=True, _
                   Other:=True, Otherchar:="/"

Spaceを区切り文字として指定したため、氏名や資格名にスペースが含まれていたデータは2つのセルに分かれます。

【(C)引数Spaceの指定を省略してマクロを実行】
では、再び「Space:=True, 」の部分を削除して実行します。

Workbooks.OpenText Filename:=TextPath, DataType:=xlDelimited, Tab:=True, _
                   Semicolon:=False, Comma:=True, _
                   Other:=True, Otherchar:="/"

引数Spaceの指定を省略したにもかかわらず、Trueを指定した(B)と同じ結果となりました。
(C)は(A)のコードと同じなのに実行結果が違うのは、どういうことなのでしょうか。

さらに続けてみます。
(C)を実行後、Falseを指定して実行 → 指定を省略して実行 を行ってみます。
すると、再び(A)と同じ結果となります。

このことから、ヘルプにある「既定値はFalseです」の記述は誤りで、OpenTextメソッドの区切り文字の指定を省略すると、前回実行したときの条件が引き継がれていることがわかります。

引数を省略したときに前回の設定が引き継がれるものには、RangeオブジェクトのFindメソッドなどもあります。「検索に失敗するとき(Findメソッドの引数省略時に起きる失敗)」もチェックしてみてください。
ちなみに、Findメソッドとは違い、OpenTextメソッドでは手作業で実行したテキストファイルウィザードの設定条件は引き継がれません。

●ポイント●

住所や氏名など、手入力したデータを取り込むときは、セルがずれるなどの不具合が起こりがちです。データの中にこれらの文字が含まれる可能性がある場合は、区切り文字の引数は省略せず、True/Falseを指定するようにしましょう。