VBA 大きなｔｘｔテキストファイルを開く方法

質問

VBA excelで大きなtxtファイルを開きたいのです！65500行(excelの行のMax）以上の場合sheetを分けてtxtのデータを全部開く場合にどうしたら良いのかどたなかアドバイスをお願いします。txtファイルを開くプログラムは作成した事があるのですが今回必要としている作業はほぼ65500行以上あるデータです。txtファイル名はその都度違います。データ量も一定ではありません。（行数は多いのですが、列は５列ほどのデータです）現在はマニュアルでエクセルにコピペしている状態です。ヒントを頂ければ幸いです。よろしくお願いします。

回答No.5

fumufumu_2006 · ベストアンサー

ANo.4です。

VBAの画面で、[ツール]->[参照設定]で、[Microsoft Scripting Runtime]にチェックを入れてください。

それと、

ws.Cells(row, col).Resize(1, UBound(c) + 1) = c '1列全データ

を、以下に変更してください。（改行のみの行の場合エラーになるので）

If UBound(c) > -1 Then
ws.Cells(row, col).Resize(1, UBound(c) + 1) = c '1列全データ
End If

お礼

fumufumu_2006さん
どうもありがとうございました！！全て読み込む事が出来ました！コードの内容は初心者の私には解読出来ない部分が多々あるのですが、少しずつでも理解出来るよう勉強したいと思います。
お陰様でかなりの作業の簡略化が出来ます。何度も回答を頂き本当にありがとうございました！

回答No.4

fumufumu_2006

1シートに読み込みます。
コマンドボタンを2つ用意してください
CommandButton1は読み込み開始。
CommandButton2は読み込み中止。
読み込み経過をステータスバーに表示します。

Option Explicit
Const fileName = "c:\ken_all.csv" 'ファイル名
Const dataSheetName = "Sheet2" 'データシート名
Const rowsNumber = 65536 '最後の行(最大65536だが、区切りのいい50000とかにもできる)
Const colOfs = 5 '2列目になった場合の列オフセット
'Const fieldDelimiter = "," 'カンマ区切りの場合
Const fieldDelimiter = vbTab 'TAB区切りの場合
Private abortFlag As Boolean '中止フラグ

Private Sub CommandButton1_Click()
Dim d() As String
'読み込み
Dim fso As New FileSystemObject
d() = Split(fso.OpenTextFile(fileName).ReadAll, vbCrLf)
Set fso = Nothing
MsgBox "読み込み完了"
'転送開始
Dim ws As Worksheet
Dim c() As String
Dim row As Long
Dim col As Long
Dim i As Long
Set ws = Sheets(dataSheetName) '転送シート
ws.Cells.Clear
row = 1
col = 1
abortFlag = False
'Application.ScreenUpdating = False '転送中表示しない（若干速くなるが、中止ボタンも押せない）
For i = 0 To UBound(d)
'ステータス表示と中止チェック
If (i Mod 100) = 0 Then
DoEvents
Application.StatusBar = i & "/" & UBound(d)
If abortFlag = True Then
MsgBox "中止しました"
Exit For
End If
End If
c = Split(d(i), fieldDelimiter)
'ws.Cells(row, col).Resize(1, 4) = c '1行4列に固定する場合
ws.Cells(row, col).Resize(1, UBound(c) + 1) = c '1列全データ
row = row + 1
If row > rowsNumber Then
row = 1
col = col + colOfs
End If
Next
Application.StatusBar = i & "/" & UBound(d)
Application.ScreenUpdating = True
Application.StatusBar = False
MsgBox "終了"
End Sub

'中止ボタン
Private Sub CommandButton2_Click()
abortFlag = True
End Sub

お礼

こんにちは。
fumufumu_2006さんの教えてくださったコードを打ってやってみたのですが、Dim fso As New FileSystemObject　が反転して　”コンパイルエラー：ユーザー定義型は定義されていません”とのエラーが出てしまいました。これはどう回避したら良いのでしょうか？初歩的な質問でしたら大変申し訳ないのですがご教示いただけると助かります。どうか宜しくお願いいたします。

回答No.3

imogasi

こんな質問なぜ出るの？
TEXファイルの、１レコードをLine　Inputなどで読む都度、カウンター変数を１アップして、６万を超える行になったら、別シートや、同一シートの別列に移す先を買えればよいのでは。
なお１レコード読む都度、スペース（やCSVならカンマなど）等で文字列をスプリットし、項目数だけ、「列に項目分離後のデータ」を代入すればよい。
ーー
＞txtファイル名はその都度違います
毎回名が変わるというのか
複数のtxtファイルを集合させるのか。
前者なら操作者に実行後最初に入れさせる。
後者ならプログラムにでも名前を持つか、同一フォルダ内とかで選別するか、サブ番号で増えていくとか手を打てばよい。
対象の名前や所属にルールがあるかどうかをいつも菅変えること。
質問者は、処理進行・流れに関するロジックの訓練が不足しているようだ。レコードという考えも非常に大切。

お礼

回答をいただきどうもありがとうございました。ＶＢＡを勉強し始めてまだ浅いので質問が稚拙ですみません。

＞TEXファイルの、１レコードをLine　Inputなどで読む都度、カウンター変数を１アップして、６万を超える行になったら、別シートや、同一シートの別列に移す先を買えればよいのでは。
なお１レコード読む都度、スペース（やCSVならカンマなど）等で文字列をスプリットし、項目数だけ、「列に項目分離後のデータ」を代入すればよい。

↑自分のやりたい事のイメージはimogasiさんが書いて下さっている感じなのですが、具体的にどういう記述をしていったら良いのかが分からず困っています。　TAB区切りで読み込んで4列のテキストデータが大体10万行～40万行ほどになります。
自分では、startrow:=65500、131000、・・・として個々に開いたら良いのか？と思ってやってみたのですがこれでは開く事が出来ませんでした。　別列にペーストが出来て全部のデータを一つのシート上に開く事が出来るとその後の処理に便利なのですが。
具体的にどう記述していけば良いのかご教示いただけると非常に助かります。どうかよろしくお願いいたします。

回答No.2

fumufumu_2006

３つ方法があるのではないかと思います。

1.は配列に取り込んで、必要な部分をCELLに転写する。（文字列配列の添え字の制限はもっと大きいので）
2.テキストファイルのデータベースとして扱い、必要な部分をCELLに転写する。（ほとんど制限なくなります）
3.Excel2007を使う。（何も考えずにできます）

ちなみに、一番手軽な１の方法で、全国郵便番号データ（約１２万件）を読んでみました。（下記の全国一括）
http://www.post.japanpost.jp/zipcode/dl/kogaki.html

Sub test()
Dim d() As String
Dim fso As New FileSystemObject
d() = Split(fso.OpenTextFile("c:\ken_all.csv").ReadAll, vbCrLf)
Set fso = Nothing
MsgBox UBound(d) & " 行"
End Sub

お礼

回答どうもありがとうございました。今、回答を拝見して自宅のPCで（excel2007）開いてみたのですが、この大量な郵便番号データが本当にすんなり開けました、、、。知りませんでした。

来週会社のPC(XP)で教えて下さったコードでトライしてみます！ぶ厚い参考書を読んだり随分調べてみたのですがなかなか良い方法が見つからなかったので非常に助かります。（VBA初心者でまだ良く理解出来ていないので近い答えに辿りつかないのだと思いますが）

来週改めてお礼のメッセージ入れさせて頂きます。どうもありがとうございました。

補足

こんにちは。
fumufumu_2006さんの教えてくださったコードを打ってやってみたのですが、Dim fso As New FileSystemObject　が反転して　”コンパイルエラー：ユーザー定義型は定義されていません”とのエラーが出てしまいました。これはどう回避したら良いのでしょうか？初歩的な質問でしたら大変申し訳ないのですがご教示いただけると助かります。どうか宜しくお願いいたします。

回答No.1

marbin

>txtファイルを開くプログラムは作成した事があるのですが

これができているのなら、
転記する行番号が65537になったらシートを一つ増やして、
行番号を65536で割った余りの行に転記する、とすればいい
のではないでしょうか？

Dim i As Long
i = 65537
MsgBox "Sheet:" & i \ 65536 + 1 & vbCrLf & "行:" & i Mod 65536