エクセルVBA テキスト読み込み（区切り文字で読み込み)　　サンプルプログラムの改良をしてください。

Question

通常、エクセルでテキストファイルを読み込もうとすると、ウイザードが立ち上がりそれを終えると読み込めます。
私はこのウイザードで、「カンマやタブなどの区切り文字で…」を選択し、次へ。次の画面で「タブ」と「スペース」にチェックを入れます。なお、スペースにチェックを入れると、自動的に「連続した区切り文字は1文字として扱う」にチェックが入ります。
そして次へ→、完了としてテキストを読み込んでいます。

このたび、この操作が面倒なので、マクロで実行しようといろいろ調べていたら、自分のやりたいことと一番近いサンプルマクロを見つけました。以下です。
http://www.bekkoame.ne.jp/~poetlabo/COMP/Excel/VBASAMP/IMP_TXT.TXT

このマクロをそのまま書いて実行すると、区切り文字を指定でき、読み込めます。
ただし、このままだと区切り文字を1つしか指定できません。
先ほど書いたように、今回は「スペース」「タブ」で区切って開きたいのですが、上記のサンプルプログラムをどう改変すればよいのでしょうか？

Wendy02 · Accepted Answer

こんにちは。

別に元のコード自体を評価するというような気持ちではありませんが、このコードは、シーケンシャルファイル用（拡張子は、.dat)にも適用できるマクロです。それで、問題は発生はしないけれども、全体的に、インポートは遅いはずです。

それと、誰もご指摘にならないようですが、元のサンプルのコードは、Excel97 用です。

Excel2000以上なら、テキストラインで、このようなことは必要ないはずです。
Call ReadLine(CurTxt, DeLimiter, rn)

Split 関数で十分なはずです。ただし、デリミタは、一つに限るので、その前に、Replace 関数で、置換しておく必要があります。

今風の書き方ではないし、プロシージャが分散しすぎて読みにくいように思います。新たに、書き直したほうがよい様な気もしてくるのですが。

記録マクロに関して、

>どうも思うのと違うため、今のところ保留にしています。
>「読み込み」と「開く」は違うみたいで、手動でやっているのと同じ結果にはなるのですが、新しいファイルが開いて、そこにテキストファイルが開かれたり…。

それは、インポートの方法が違っているからです。これは、VBAのオブジェクトで言えば、QueryTable といいますが、ワークシートでは、[外部データの取り込み]のことを指します。オプション自体は、複雑なこともありますので、記録マクロを利用するのが一番です。なお、Tab と Comma 共存の場合は、QueryTable が優れています。

VBA入門レベルでも、記録マクロと組み合わせれば可能かと思います。というか、よほど、インポート間で加工しなければ、このまま使えると思います。

例：

Sub TestMacro1()
　　Dim Fname As Variant
　　On Error Resume Next
　　ActiveSheet.QueryTables(1).Delete　'QueryTableがあったら削除
　　On Error GoTo 0
　　Fname = Application.GetOpenFilename( _
　　"テキストファイル (*.txt; *.csv),*.txt;*.csv", 1, "OpenTextFile")
　　If VarType(Fname) = vbBoolean Then Exit Sub
'-------------------細かいオプションは、記録マクロから取り出してください。---
　　With ActiveSheet.QueryTables.Add( _
　　　　Connection:="TEXT;" & Fname, _
　　　　Destination:=Cells(1, 1))
　　　　.AdjustColumnWidth = False '列幅の自動調整
　　　　.TextFileCommaDelimiter = True 'カンマ切り
　　　　.Refresh BackgroundQuery:=False 'バックグラウンドの再入力（一回きり）
　　　　.TextFileTabDelimiter = True 'タブデリミタあり
　　End With
'-------------------
　　ActiveSheet.QueryTables(1).Delete
End Sub

Wendy02 · Answer

こんにちは。

#7です。
ある程度の確信はあったのですが、うまくいって、よかったです。

>やはり区切り方が不十分だったので、以下のことを試してみました。

>これもなぜか反応してくれてないようなので、位置を変えて再び実行したところ、見事希望通りの結果になってくれました。

一応、弁解されてください。実は、こういうところって、あまり考えないで、本当は、記録マクロのその設定部分を、全部貼り付けていただいたほうがよいようです。細かい、オプションまでは、見切れていないのです。まして、私自身、順番とかあるのは知りませんでした。

それと、ちょっと気になったことですが、

以下は、今、試してみましたが、シートの使いまわしするような場合に、既にデータがあったりすると、誤動作があるようです。それに、QueryTable(クエリテーブル）は、インターネットからデータを取るようなこと以外は、通常は無用の長物なので、このように変えたほうがよいようです。

＞On Error Resume Next
＞　　ActiveSheet.QueryTables(1).Delete　'QueryTableがあったら削除
＞On Error GoTo 0

　　　　↓

　Dim qt As Object
　
　　ActiveSheet.UsedRange.ClearContents '一応データだけのクリア(書式も含めるとClear)
　　For Each qt In ActiveSheet.QueryTables
　　　 qt.Delete　'QueryTableがあったら削除
　　Next

imogasi · Answer

掲出のプログラムの改良ではないが、下記の方が判りやすい・簡単と思う。
デリミタは普通カンマ、スペース、タブなどが多いようですが
（１）大げさですが、対話式に利用者にデリミタを選択させるのか
（２）プログラムの中にどれか決めてしまって、１つをセットするのか
（３）指定は何もしないようにしたいのか
どれですか。質問では明確ではない。
ーー
それと区切り文字はファイル１つを作られるときは、普通は１種と思いますが、
＞「スペース」「タブ」で区切って開きたいのですが、
とはスペース出現でも区切り、タブ出現でも区切るようにしたいということですか。
（Ａ）１つのファイルの場合か
（Ｂ）色々な別ファイルの場合にデリミタがバラバラのこと
なのか。
下記ＶＢＡは（Ａ）でやっているが、（Ｂ）でも１つのプログラムで対応すると思う。
ーー
（３）とすると
エクセルＶＢＡの課題らしいので、エクセルＶＢＡらしく、エクセルにあるデーター区切り位置の機能を使いました。
・レコードを読んでＡ列にセット
・そのＡ列１行分データを、データー区切り位置の機能で各列に分離
の作業を（自動で）を行うと
Sub test01()
i = 1
Open "C:\Documents and Settings\ｘｘｘ\My Documents	ext14.txt" For Input As #1
While Not EOF(1)
Line Input #1, a
Cells(i, "A") = a
'---
Cells(i, "A").TextToColumns Destination:=Cells(i, "A"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=True, Other:=False
'---
i = i + 1
Wend
Close #1
End Sub
ーーー
上記はTab:=True,以下セミコロン、カンマ、スペースが指定されていますが、これらは
１つの文字列の中において、ＯＲ条件的に働いて、分離してくれることが確認できました。
もっと種類を増やしたければ、区切り位置の操作で「その他」に文字を指定してマクロの記録をとってどうなるか、調べてコードに追加してください。
そうすれば実行時にはデリミタの種類をユーザーが指定する必要はありません。
ファイル名は都度指定できるよう改良のこと。
ーーーー
途中の’－－から’－－まではマクロの記録をとって一部手直しをしたものを挿入した。
ーーーー
テスト
例データ　
メモ帳で
aa,bbb cd(T)df
xxx yyyy,zz vv
ww(T)ff(T)rr,tt
(T)mm hh,dd
(T)はＴＡＢコードが入っている例を作りました。
レコード（およびファイル）内でタブ、カンマ、スペースの３種混合です。
-----
上記プログラム実行結果
Ａ列　　Ｂ列　　Ｃ列　　Ｄ列
aabbbcddf
xxxyyyyzzvv
wwffrrtt
mmhhdd
とうまく行くようです。

mitarashi · Answer

こんなのではダメでしょうか？外していたらすみません。
Sub test()
   Dim srcFileName As String
   
   srcFileName = "C:\Documents and Settings\?????\My Documents\hoge.txt"
   
   'ここは自動記録したコードを、テキストファイル名だけ変数で与えるように改変
   Workbooks.OpenText Filename:=srcFileName, StartRow:= _
        1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _
        Space:=True, Other:=False
    '開いたテキストファイルのシートを、マクロを記述してあるブックに移動
    ActiveSheet.Move Before:=ThisWorkbook.Sheets(1)
End Sub

Tiffa9900 · Answer

Sub ReadLine(CurTxt As String, DeLimiter As String, rn As Integer)
　　Dim StrStart As Integer, StrEnd As Integer
　　Dim StrArray() As String, cn As Integer
    
　　StrStart = 1
（以下略）

これを

Sub ReadLine(CurTxt As String, DeLimiter As String, rn As Integer)
　　Dim StrStart As Integer, StrEnd As Integer
　　Dim StrArray() As String, cn As Integer

　　CurTxt = Replace(CurTxt, " ", DeLimiter)

　　StrStart = 1
（以下略）

と一文追記しただけですね。


Replace関数は、おっしゃっている通りでOKです。厳密には、
CurTxt変数(読み込んだ1行分の内容)の中の" "(半角スペースです)をDeLimiter変数の中身に置き換えた結果をCurTxtに代入
と言う事になります。

Tiffa9900 · Answer

タブは、「Chr(9)」ですよ。

自分なら、サブルーチン「ReadLine」中で
初めに、スペースを分割文字列に置換しちゃうかな？
Dim で定義した直後に、
　CurTxt = Replace(CurTxt, " ", DeLimiter)
こうかな？
こうすれば、その後の処理でスペースの部分も分割文字列として扱われる気がします。

n-jun · Answer

いつもやっている作業をマクロの自動記録した事ありませんか？
そこから改良してみても良いのかと思いますけど。

fujillin · Answer

ちゃんとマクロを見ていなくて申し訳ないですが・・・

下の方の　SetDelimiterで入力している変数「DeLimiter」がセパレータなので、これを複数扱えるように変更すればよいのでは？
あとは　「Delimiter」を検索して、そこで処理している内容を２つのセパレータで行えば良いと思われます。

セパレータがスペースとタブに決め打ちなら、Constなんかで最初に定義しておいて、入力ルーチンを飛ばしてしまってもいいかも。

エクセルVBA テキスト読み込み（区切り文字で読み込み) サンプルプログラムの改良をしてください。

こんにちは。

こんにちは。

掲出のプログラムの改良ではないが、下記の方が判りやすい・簡単と思う。

こんなのではダメでしょうか？外していたらすみません。

Sub ReadLine(CurTxt As String, DeLimiter As String, rn As Integer)

タブは、「Chr(9)」ですよ。

いつもやっている作業をマクロの自動記録した事ありませんか？

ちゃんとマクロを見ていなくて申し訳ないですが・・・

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

エクセルVBA テキスト読み込み（区切り文字で読み込み)　　サンプルプログラムの改良をしてください。