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

質問

通常、エクセルでテキストファイルを読み込もうとすると、ウイザードが立ち上がりそれを終えると読み込めます。
私はこのウイザードで、「カンマやタブなどの区切り文字で…」を選択し、次へ。次の画面で「タブ」と「スペース」にチェックを入れます。なお、スペースにチェックを入れると、自動的に「連続した区切り文字は1文字として扱う」にチェックが入ります。
そして次へ→、完了としてテキストを読み込んでいます。

このたび、この操作が面倒なので、マクロで実行しようといろいろ調べていたら、自分のやりたいことと一番近いサンプルマクロを見つけました。以下です。
http://www.bekkoame.ne.jp/~poetlabo/COMP/Excel/VBASAMP/IMP_TXT.TXT

このマクロをそのまま書いて実行すると、区切り文字を指定でき、読み込めます。
ただし、このままだと区切り文字を1つしか指定できません。
先ほど書いたように、今回は「スペース」「タブ」で区切って開きたいのですが、上記のサンプルプログラムをどう改変すればよいのでしょうか？

回答No.7

Wendy02 · ベストアンサー

こんにちは。

別に元のコード自体を評価するというような気持ちではありませんが、このコードは、シーケンシャルファイル用（拡張子は、.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

お礼

回答ありがとうございます。
とりあえず教えてくださったサンプルプログラムをそのまま実行してみました。うまく読み込めましたが、やはり区切り方が不十分だったので、以下のことを試してみました。
まず、
.TextFileTabDelimiter = True 'タブデリミタあり
のあとに、
.TextFileSpaceDelimiter = Trueを加えて実行。
なぜか加えた一文に反応してくれてないようなので、この一文の位置を変更してみました。(「.AdjustColumnWidth = False '列幅の自動調整
」の文の次など)そしたら反応するようになってくれましたが、今度は区切り文字が多すぎて失敗。

次に「マクロの記録」を参考にして、
.TextFileConsecutiveDelimiter = True を加えて実行。
これもなぜか反応してくれてないようなので、位置を変えて再び実行したところ、見事希望通りの結果になってくれました。

なぜ書いた記述に反応してくれないかを確かめるため、もう一度まっさらな状態から教えていただいたプログラムを書き、
.TextFileTabDelimiter = True 'タブデリミタありの次に
.TextFileSpaceDelimiter = True
.TextFileConsecutiveDelimiter = True
の2文を加えて実行してみました。が、やはり反応してくれません。
次に、同様に、この2文の記述位置を変えて実行→見事反応して、期待通りの結果になりました。

位置をずらさないと反応してくれない理由はわかりませんが、ひとまず期待通りの結果を出せることができました。実行速度も格段に速くなって(一瞬の速さ)満足です。

回答ありがとうございました。

回答No.8

Wendy02

こんにちは。

#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

回答No.6

imogasi

掲出のプログラムの改良ではないが、下記の方が判りやすい・簡単と思う。
デリミタは普通カンマ、スペース、タブなどが多いようですが
（１）大げさですが、対話式に利用者にデリミタを選択させるのか
（２）プログラムの中にどれか決めてしまって、１つをセットするのか
（３）指定は何もしないようにしたいのか
どれですか。質問では明確ではない。
ーー
それと区切り文字はファイル１つを作られるときは、普通は１種と思いますが、
＞「スペース」「タブ」で区切って開きたいのですが、
とはスペース出現でも区切り、タブ出現でも区切るようにしたいということですか。
（Ａ）１つのファイルの場合か
（Ｂ）色々な別ファイルの場合にデリミタがバラバラのこと
なのか。
下記ＶＢＡは（Ａ）でやっているが、（Ｂ）でも１つのプログラムで対応すると思う。
ーー
（３）とすると
エクセルＶＢＡの課題らしいので、エクセルＶＢＡらしく、エクセルにあるデーター区切り位置の機能を使いました。
・レコードを読んでＡ列にセット
・そのＡ列１行分データを、データー区切り位置の機能で各列に分離
の作業を（自動で）を行うと
Sub test01()
i = 1
Open "C:\Documents and Settings\ｘｘｘ\My Documents\text14.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)はＴＡＢコードが入っている例を作りました。
レコード（およびファイル）内でタブ、カンマ、スペースの３種混合です。
-----
上記プログラム実行結果
Ａ列　　Ｂ列　　Ｃ列　　Ｄ列
aa bbb cd df
xxx yyyy zz vv
ww ff rr tt
mm hh dd
とうまく行くようです。

お礼

>（１）大げさですが、対話式に利用者にデリミタを選択させるのか
>（２）プログラムの中にどれか決めてしまって、１つをセットするのか
>（３）指定は何もしないようにしたいのか
今回、読み込ませるテキストファイル内の記述形式は決まっているので、対話式に選択させる必要もなく、決まりきった区切り文字で区切ってくれればいいです。今回はスペースとタブ(ただし、連続した区切り文字は1文字として扱う)ということです。
しかし、今後のプログラムで必要となってくると思うので、対話式で選択もぜひマスターしたい事項でもあります。

＞とはスペース出現でも区切り、タブ出現でも区切るようにしたいということですか。
よくわかりませんが、通常のテキストファイルの開き方で開いたときと同じ結果になってくれればよかったのです。今回、ウイザードで「タブ」と「スペース」(それから自動でチェックが入る「連続した区切り文字は1文字…」)にチェックを入れて開いたらうまく開いてくれていたので、単純に「タブとスペース」と質問文には書いていたまでです。

プログラム作成のヒントありがとうございました。いろいろ試してみたいと思います。

回答No.5

mitarashi

こんなのではダメでしょうか？外していたらすみません。
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

お礼

まだ試していませんが、いろいろヒントになる部分があり、勉強になります。
回答ありがとうございました。

回答No.4

Tiffa9900

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に代入
と言う事になります。

お礼

回答ありがとうございます。
一文追加してみましたが、まだ思うようになりません。
しかし、こういう方法もあると大変参考になりました。
いろいろ試してみたいと思います。

回答No.3

Tiffa9900

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

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

お礼

回答ありがとうございます。
おっしゃっていることを確認させてください。
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
サブルーチン「ReadLine」とは上記の部分から始まるところでいいですか?
「ReadLine」の中とはReadLineサブルーチンのどの変なのでしょうか。

＞Dim で定義した直後に
何を定義するのでしょう。新しい変数??

CurTxt = Replace(CurTxt, " ", DeLimiter)
この意味はCurTxtの中の""(つまりスペースのことですよね)をDeLimiter変数の中身に置き換えるという意味でよろしいですか?

うーむ、まだまだぜんぜん理解が足りないです…。

回答No.2

n-jun

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

お礼

もちろんそれはいの一番に試しました。
が、どうも思うのと違うため、今のところ保留にしています。
「読み込み」と「開く」は違うみたいで、手動でやっているのと同じ結果にはなるのですが、新しいファイルが開いて、そこにテキストファイルが開かれたり…。手動のときはしょうがないので、それをコピーして、目的のエクセルのファイルに貼り付けてました。

今回発見したプログラムは実行したエクセルファイルにちゃんと読み込んでくれますし、非常に使い勝手がよさそうです。あとは区切り文字にスペースを追加できればいいだけなのですが…。

回答No.1

fujillin

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

下の方の　SetDelimiterで入力している変数「DeLimiter」がセパレータなので、これを複数扱えるように変更すればよいのでは？
あとは　「Delimiter」を検索して、そこで処理している内容を２つのセパレータで行えば良いと思われます。

セパレータがスペースとタブに決め打ちなら、Constなんかで最初に定義しておいて、入力ルーチンを飛ばしてしまってもいいかも。

お礼

回答ありがとうございます。
エクセルVBA初心者で、webのサンプルプログラムをあさりながら勉強している最中なのですが、今回のようなサンプルプログラムは自分にとってはまだ難易度がかなり高いです。

そもそも、「Tab」をどのように設定し、どこで指定しているのかわかりません。初心者的にはどこかに" "（←Tabで入力される空白と考えてください。)というような場所が存在していると考えてしまいますが、このプログラムにはどこにも存在していません。DeLimiterに「Tab」として処理される何かを入れているのかとは思いますが、具体的にどのようにしているかまったくわかりません。よろしければもう少し詳しく説明していただけますでしょうか。
自分でもいろいろ調べてみますが…。