エクセルVBA テキスト読み込み(区切り文字で読み込み) サンプルプログラムの改良方法
- エクセルVBAを使用してテキストファイルを区切り文字で読み込む方法について、サンプルプログラムを改良することで複数の区切り文字を使用できるようにします。
- 通常、エクセルでテキストファイルを読み込む際にはウイザードを使用しますが、マクロを使用することで手間を省くことができます。
- 上記のサンプルプログラムを改変し、スペースとタブの両方を区切り文字として指定できるようにする方法を探しています。
- ベストアンサー
エクセルVBA テキスト読み込み(区切り文字で読み込み) サンプルプログラムの改良をしてください。
通常、エクセルでテキストファイルを読み込もうとすると、ウイザードが立ち上がりそれを終えると読み込めます。 私はこのウイザードで、「カンマやタブなどの区切り文字で…」を選択し、次へ。次の画面で「タブ」と「スペース」にチェックを入れます。なお、スペースにチェックを入れると、自動的に「連続した区切り文字は1文字として扱う」にチェックが入ります。 そして次へ→、完了としてテキストを読み込んでいます。 このたび、この操作が面倒なので、マクロで実行しようといろいろ調べていたら、自分のやりたいことと一番近いサンプルマクロを見つけました。以下です。 http://www.bekkoame.ne.jp/~poetlabo/COMP/Excel/VBASAMP/IMP_TXT.TXT このマクロをそのまま書いて実行すると、区切り文字を指定でき、読み込めます。 ただし、このままだと区切り文字を1つしか指定できません。 先ほど書いたように、今回は「スペース」「タブ」で区切って開きたいのですが、上記のサンプルプログラムをどう改変すればよいのでしょうか?
- dansin_Goo
- お礼率44% (193/432)
- オフィス系ソフト
- 回答数8
- ありがとう数11
- みんなの回答 (8)
- 専門家の回答
質問者が選んだベストアンサー
こんにちは。 別に元のコード自体を評価するというような気持ちではありませんが、このコードは、シーケンシャルファイル用(拡張子は、.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
その他の回答 (7)
- Wendy02
- ベストアンサー率57% (3570/6232)
こんにちは。 #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
- ベストアンサー率27% (4737/17068)
掲出のプログラムの改良ではないが、下記の方が判りやすい・簡単と思う。 デリミタは普通カンマ、スペース、タブなどが多いようですが (1)大げさですが、対話式に利用者にデリミタを選択させるのか (2)プログラムの中にどれか決めてしまって、1つをセットするのか (3)指定は何もしないようにしたいのか どれですか。質問では明確ではない。 ーー それと区切り文字はファイル1つを作られるときは、普通は1種と思いますが、 >「スペース」「タブ」で区切って開きたいのですが、 とはスペース出現でも区切り、タブ出現でも区切るようにしたいということですか。 (A)1つのファイルの場合か (B)色々な別ファイルの場合にデリミタがバラバラのこと なのか。 下記VBAは(A)でやっているが、(B)でも1つのプログラムで対応すると思う。 ーー (3)とすると エクセルVBAの課題らしいので、エクセルVBAらしく、エクセルにあるデーター区切り位置の機能を使いました。 ・レコードを読んでA列にセット ・そのA列1行分データを、データー区切り位置の機能で各列に分離 の作業を(自動で)を行うと Sub test01() i = 1 Open "C:\Documents and Settings\xxx\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,以下セミコロン、カンマ、スペースが指定されていますが、これらは 1つの文字列の中において、OR条件的に働いて、分離してくれることが確認できました。 もっと種類を増やしたければ、区切り位置の操作で「その他」に文字を指定してマクロの記録をとってどうなるか、調べてコードに追加してください。 そうすれば実行時にはデリミタの種類をユーザーが指定する必要はありません。 ファイル名は都度指定できるよう改良のこと。 ーーーー 途中の’--から’--まではマクロの記録をとって一部手直しをしたものを挿入した。 ーーーー テスト 例データ メモ帳で aa,bbb cd(T) df xxx yyyy,zz vv ww(T) ff(T) rr,tt (T) mm hh,dd (T)はTABコードが入っている例を作りました。 レコード(およびファイル)内でタブ、カンマ、スペースの3種混合です。 ----- 上記プログラム実行結果 A列 B列 C列 D列 aa bbb cd df xxx yyyy zz vv ww ff rr tt mm hh dd とうまく行くようです。
お礼
>(1)大げさですが、対話式に利用者にデリミタを選択させるのか >(2)プログラムの中にどれか決めてしまって、1つをセットするのか >(3)指定は何もしないようにしたいのか 今回、読み込ませるテキストファイル内の記述形式は決まっているので、対話式に選択させる必要もなく、決まりきった区切り文字で区切ってくれればいいです。今回はスペースとタブ(ただし、連続した区切り文字は1文字として扱う)ということです。 しかし、今後のプログラムで必要となってくると思うので、対話式で選択もぜひマスターしたい事項でもあります。 >とはスペース出現でも区切り、タブ出現でも区切るようにしたいということですか。 よくわかりませんが、通常のテキストファイルの開き方で開いたときと同じ結果になってくれればよかったのです。今回、ウイザードで「タブ」と「スペース」(それから自動でチェックが入る「連続した区切り文字は1文字…」)にチェックを入れて開いたらうまく開いてくれていたので、単純に「タブとスペース」と質問文には書いていたまでです。 プログラム作成のヒントありがとうございました。いろいろ試してみたいと思います。
- mitarashi
- ベストアンサー率59% (574/965)
こんなのではダメでしょうか?外していたらすみません。 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
- ベストアンサー率31% (68/216)
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
- ベストアンサー率31% (68/216)
タブは、「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変数の中身に置き換えるという意味でよろしいですか? うーむ、まだまだぜんぜん理解が足りないです…。
- n-jun
- ベストアンサー率33% (959/2873)
いつもやっている作業をマクロの自動記録した事ありませんか? そこから改良してみても良いのかと思いますけど。
お礼
もちろんそれはいの一番に試しました。 が、どうも思うのと違うため、今のところ保留にしています。 「読み込み」と「開く」は違うみたいで、手動でやっているのと同じ結果にはなるのですが、新しいファイルが開いて、そこにテキストファイルが開かれたり…。手動のときはしょうがないので、それをコピーして、目的のエクセルのファイルに貼り付けてました。 今回発見したプログラムは実行したエクセルファイルにちゃんと読み込んでくれますし、非常に使い勝手がよさそうです。あとは区切り文字にスペースを追加できればいいだけなのですが…。
- fujillin
- ベストアンサー率61% (1594/2576)
ちゃんとマクロを見ていなくて申し訳ないですが・・・ 下の方の SetDelimiterで入力している変数「DeLimiter」がセパレータなので、これを複数扱えるように変更すればよいのでは? あとは 「Delimiter」を検索して、そこで処理している内容を2つのセパレータで行えば良いと思われます。 セパレータがスペースとタブに決め打ちなら、Constなんかで最初に定義しておいて、入力ルーチンを飛ばしてしまってもいいかも。
お礼
回答ありがとうございます。 エクセルVBA初心者で、webのサンプルプログラムをあさりながら勉強している最中なのですが、今回のようなサンプルプログラムは自分にとってはまだ難易度がかなり高いです。 そもそも、「Tab」をどのように設定し、どこで指定しているのかわかりません。初心者的にはどこかに" "(←Tabで入力される空白と考えてください。)というような場所が存在していると考えてしまいますが、このプログラムにはどこにも存在していません。DeLimiterに「Tab」として処理される何かを入れているのかとは思いますが、具体的にどのようにしているかまったくわかりません。よろしければもう少し詳しく説明していただけますでしょうか。 自分でもいろいろ調べてみますが…。
関連するQ&A
- スペース区切りのテキストファイルをExcelで開く
テキストファイルをExcelファイルとして読み込む必要が多いです 毎回Excelの 開く を使ってテキストファイルを開くのは面倒なので、右クリックの 送る にExcelの実行ファイルをコピーしてそこに送っています ただ、この方式だとスペース区切りで改列出来ず、タブと改行(エンターキー)のみ反映したファイルが開きます 最初からスペース区切りも列区切りにした形でテキストファイルを開く方法(Excelの 開く を使わずに)はないでしょうか
- ベストアンサー
- オフィス系ソフト
- vbaでテキストファイル(*.txt)を取込みたい
vba初心者です。 Application.Dialogs(xlDialogOpen).Showで選択したテキストファイル(***.txt)をテキストファイルウィザードで、タブ、カンマ、スペース区切りで、エクセルファイルに取り込むマクロを組みたいのですが? 何度やってもうまくいきません。 ご教示願えないでしょうか! excel2002を使用してます。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- ExcelでtxtファイルをVBAで読み込む方法
スペース区切りのテキストファイル(拡張子はtxt)が有ります。 データは、数字ですが上桁には0が入っています。 00001 0003 010222 302033 00025 等のデータです。 このファイルを、ウイザードを使わずにVBAにて直接文字列(テキスト)として読み込むコマンドを失念してしまいました。 教えて下さい。宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- Excel でテキストファイルを開くときに「テキストファイルウィザード」を起動させたい
Excel(Excel 2003)を「ファイル名を指定して実行」にて起動し(excel "C:\xxxx\xxx\xxx.txt")、Textファイル(.txt)を起動すると、Excel の機能の「テキストファイルウィザード」が起動されずにテキストファイルの内容がExcelのシートに展開されてしまいます。「テキストファイルウィザード」が起動されない為、区切り文字の指定ができず、特定の区切り文字を指定してファイルを開くことができません。 「ファイル名を指定して実行」にて、excel "C:\xxxx\xxx\xxx.txt" のように指定して実行した時に、「テキストファイルウィザード」が起動するように設定することはできませんでしょうか?
- 締切済み
- オフィス系ソフト
- VBAでタブ区切りテキストの保存に関して
VBAでタブ区切りテキストの保存のプログラムを書くためには Application.DisplayAlerts = False Sheets("sheet01").Copy ActiveWorkbook.SaveAs Filename:="D:\test.txt", FileFormat:=xlText ActiveWindow.Close Application.DisplayAlerts = True という記述が一般的に使われます。 いま、あるプログラムでexcelシート上に title subtitle 001 002 003 333 444 555 666 777 888 ような内容が書き込まれたものをタブ区切りで出力したいのですが、 上記の方法で出力すると、 titleやsubtitleと書かれた行の右側にも空白のタブ区切りが一緒に保存されてしまいます。 あるプログラムで読み込ませるためには、 この二つの行のタブ区切りをいちいち消す必要があり、毎回手作業でするのは面倒です。 VBAでこのようなタブ区切りの有無を行ごとに指定して保存するようなことは可能でしょうか?
- ベストアンサー
- Visual Basic
- エクセルのVBAでタブ区切りのテキスを出力したい。
エクセルのVBAでテキスト出力をしたいのですが、 ・タブ区切りで保存 ・A列の最終行×1行目の最終列の範囲指定が対象 ・範囲内の空欄もタブ区切りにする(最終列には必ず数値あり) ・出力テキストはシートと同じフォルダ内へ格納 ・テキストファイル名はシート名 という感じにしたいのです。 いろいろ検索はしているのですが、これといった回答やサンプルは 見つかっておりません(><) ちなみに、私は、自動記録をしたものを改良して少し使えるレベルです。 よろしくお願いします。
- ベストアンサー
- その他(プログラミング・開発)
- 任意の区切り文字でsplitする方法
VBAで任意の区切り文字でsplitする方法を教えてください。 例えば 123 456 789 333 3444 66 65 22 といようなスペースあるいはタブで区切られたテキストデータがあるとします。 これを配列に格納したいのですが、 この区切りがタブ一つとか、スペース一つとか決まっていれば split関数で簡単に配列に格納することができますが、 タブかも知れないし、スペースがかも知れないし その数も未定の場合にはどうしたら良いですか? replace関数を使って、まず全てのタブをスペースに変換し その後に、複数のスペースを一つのスペースに変換し 最後にsplit関数を使うという方法しかないでしょうか? もっとスマートな方法があれば教えてください。
- 締切済み
- Visual Basic
- テキストファイルのデータ区切り文字(制御文字)について
テキストファイルにデータを保存しているのですが、区切り文字タブ\tはフィールドの区切りに使っています。 その状態で、仮に1つのフィールドにデータを詰め込む場合、,(カンマ)で区切った場合にデータ自体に金額の,があった場合、誤動作します。フィールドの区切りはタブ\tは普通ですが、1つのフィールドに複数データを持たせる場合の区切り文字は\0など使っても問題ないのでしょうか? 環境はWindows, Linux両方で使います。\0を使ってそれが文字列の最後とまた誤動作しても嫌です。 皆さんは\t以外にテキストデータの区切りにどのような制御文字を使っていますか?ちなみに改行は1レコードの区切り文字です。
- ベストアンサー
- Perl
- excelからtab区切りテキストへの変換で…。
過去の質問を検索しましたが、無い様ですので質問させてください。 excelでためたデータベースをtab区切りテキストに変換したのですが、 できあがったtab区切りテキストをテキストエディタで開きますと、 (" ")が勝手にデータの両端についてしまうのです。 例えば(18,000)という数字データがあったとします、 それをタブ区切りテキストに書き出すと、("18,000")という風になってしまいます。 そこで、とりあえずすべてのデータの書式を文字列にしてやりますと、(18000)となってしまいますから、 文字列として(,)を追加したのですが、 やっぱりtab区切りテキストに変換すると(" ")がついてきます。 ただし、普通の文字のフィールドは大丈夫です。 (" ")を消す方法はないでしょうか? 少々わかりにくい説明ですが、何卒よろしくお願いします。 あと、追加で質問なのですが、桁数の多い数字が勝手に16進数?に変わってしまいます。 こちらの方も原因が分かりませんので教えていただければ幸いです。 ちなみに検証してみたexcelのバージョンは Win版 Excel2000 Mac版 Excel98 です。 設定は特にいじっていませんので、初期設定だと思います。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- EXCELの区切り位置のコマンドについて
HPから取ってきたデータをEXCELにテキストで貼り付けて、 スペースで区切る処理をするために、データメニューの区切り位置のコマンドで処理しようとしていますが、この区切り位置のコマンドは、1行ごとにしなければならないようですが、100行もあるデータも一回一回、データメニューの区切り位置のコマンドを実行しなければなりませんか? 記録マクロで処理しようと思いましたが、セルを固定してしまうマクロになるのでできませんでした。 よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
お礼
回答ありがとうございます。 とりあえず教えてくださったサンプルプログラムをそのまま実行してみました。うまく読み込めましたが、やはり区切り方が不十分だったので、以下のことを試してみました。 まず、 .TextFileTabDelimiter = True 'タブデリミタあり のあとに、 .TextFileSpaceDelimiter = Trueを加えて実行。 なぜか加えた一文に反応してくれてないようなので、この一文の位置を変更してみました。(「.AdjustColumnWidth = False '列幅の自動調整 」の文の次など)そしたら反応するようになってくれましたが、今度は区切り文字が多すぎて失敗。 次に「マクロの記録」を参考にして、 .TextFileConsecutiveDelimiter = True を加えて実行。 これもなぜか反応してくれてないようなので、位置を変えて再び実行したところ、見事希望通りの結果になってくれました。 なぜ書いた記述に反応してくれないかを確かめるため、もう一度まっさらな状態から教えていただいたプログラムを書き、 .TextFileTabDelimiter = True 'タブデリミタありの次に .TextFileSpaceDelimiter = True .TextFileConsecutiveDelimiter = True の2文を加えて実行してみました。が、やはり反応してくれません。 次に、同様に、この2文の記述位置を変えて実行→見事反応して、期待通りの結果になりました。 位置をずらさないと反応してくれない理由はわかりませんが、ひとまず期待通りの結果を出せることができました。実行速度も格段に速くなって(一瞬の速さ)満足です。 回答ありがとうございました。