エクセルＶＢＡでＣＳＶファイルをシートに転写させたところ途中で止まってしまうのは何故でしょう？

VBA初心者です。よろしくご教授のほどお願いします。
エクセルのシートに読み込んだＣＳＶファイルのデータを転写する下記のＶＢＡを作ったところ
テストではうまくいくのですが、本来の転写先である４０Ｍぐらいあるワークブックで試す
と、途中で読み込みをストップしてしまいます。
どのように対処したら良いのでしょうか。


（ＣＳＶデータは13フィールド×5000行ぐらいです。）

Open "C:\data\a.csv" For Input As #1
Do Until EOF(1)
Line Input #1, buf
tmp = Split(buf, ",")

For i = 0 To 12
Worksheets("deta-a").Cells(n, i + 1).Value = tmp(i)

Next i
n = n + 1
Loop
Close #1

No.3 回答者： WindFaller 回答日時： 2015/02/23 11:58

こんにちは。

>取り込み先のエクセルブックが４０Ｍあるのです。
そういうことでしたか。想像つきませんでしたね。
>新規のエクセルブックにコードをコピペして実行すると２秒ぐらいで完了します。
それに、Excel のバージョンをお聞きしていなかったのです。
40Mというのは巨大なサイズなのです。
OS とExcelのVersionの関係では、もう無理かもしれません。例えば、Excel 2000とか2002など。

私が、最初に使ったCSVファイルを、Excelファイル Xlsxに保存しなおしても、11M程度です。
私は、Exce 2010なのですが、そのファイルサイズで、さらに上乗せはせず、データファイルとしての利用以外にはしません。

それに、再計算や他のマクロが連動して動いているのではないでしょうか。
Application.EnableEvents =False

これだけでは足りない場合があります。

実際には、最低限、この程度は加えていらっしゃいますか？
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Application.EnableEvents =False '場合による

'マクロコード

'Application.EnableEvents = True
Application..Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

他にも
Application.Interactive = False　'マクロが終わるまで一切を受け付けない設定（特殊）
Application.DisplayAlerts = False

があります。

しかし、「取り込み先」ファイル自体に、すでに問題が発生しているような気がします。あえてそのブック・ファイルを使うにしても、単なるデータファイルなら、完全にマクロも、場合によっては書式もなくして保管したほうがよいでしょうね。マクロも外部のブックやアドインから行うようにします。

No.2 回答者： WindFaller 回答日時： 2015/02/20 19:17

こんばんは。

#1の回答者です。

私が最初に考えた回答は、まったく違うものでしたが、いきなり書くよりも、コードをみて、コード自体を指摘したほうがよいと思ったからです。今は、やっぱりそうだったかな、というところです。

>sleep 1でＣＰＵを少し休ませることも実験したのですが、結果は同じでした。
Sleep 1 では、1/1000 秒ですから、ほとんど意味はありませんね。
Sleep 500 ～ 1000ぐらいなら、効き目があるかもしれませんが、DoEvents も入れたほうがよいかもしれません。

最初の案は、直接、エラーを回避する方法ではありません。

第1案
お話の様子で、最初、

On Error Goto ErrHandler はご存知だと思いますが、エラートラップを仕掛けして、このエラーが発生した時に、MsgBox などで、i の値（エラーを出した行数）を調べればよいと考えました。
たぶん、Next まで通ってなければ、そのものズバリの行数ですから、テキスト・エディタで、その行の所を調べるわけです。その時に、もしかしたら、テキストでは読めない部分が存在していないか、ということです。

それにしても、一体、コードのどこでエラーが発生しているかも、特定できたほうがよいでしょうね。

もし、iの値が分かったなら、そこから、また読み込みしていけば、と考えました。
……別案、第4案に続く

第2案
あまりお勧めではないのですが、WorksheetFunctio のClean 関数を用いることで、バイナリーコードを抜くということです。
　　 Line Input #FNo, buf 'の次の行
　　 buf = WorksheetFunction.Clean(buf)　'←
バイナリーとは言っても、せいぜい、エスケープコードレベルでも、気休めにしかはなりませんが、最も期待度が低い一番簡単な方法です。

第3案
鈍足ですが、TextStreem のFileSystemObject で、OpenTextFile(ファイル名）のオブジェクトから、
ReadLineで読み込んでみる方法があります。

第4案
回答#1で使った、ZIP_ALL.CSV というのは、全国の郵便番号とその住所のファイルで、11 M程度ですが、なかなか大きなファイルです。それでも、40Mはこの4倍近いわけで、巨大なファイルには違いありません。それが原因かとも考えました。

そこで、Text分割という方法も考えてみたら、と思いました。Window7辺りでも、ファイル分割ツールは備えてはいますが、ダウンロードサイトのVector辺りには、Divという名前のアプリも存在しているようです。

連続して、分割で読み込んだらできるかもしれません。

この回答へのお礼

WindFallerさん　こんにちは

ｃｓｖデータそのものは、１３×５０００程度なので５００ｋもありません。取り込み先のエクセルブックが４０Ｍあるのです。いつまで経っても終わらないので、ブレークしてみるとその度ごとにブレークポイントが違います。新規のエクセルブックにコードをコピペして実行すると２秒ぐらいで完了します。ちなみにPCはノートでメモリは４Ｇです。

お礼日時：2015/02/23 09:50

No.1 回答者： WindFaller 回答日時： 2015/02/18 19:31

こんばんは。

>　　 Worksheets("deta-a").Cells(n, i + 1).Value = tmp(i)
全文がないので、n の初期値は気になるけれども、同じようには書かないまでも、他は、そんなに間違っているとは思えません。

ただ、ここの部分はちょっと気になります。

>For i = 0 To 12
この部分は、12とハードコーディング（キメウチ）していますが、大丈夫ですか？
区切り数が12よりも超えていればよいのですが、12より以下ですと、ハングするはずです。

CSVファイルで、40Mというのも、ちょっと気になります。

これは、私が試してみたコードです。以下の※の部分をごらんください。

'//
Sub TestMacro2()
　Dim myPath As String
　Dim fn As String
　Dim i As Long
　Dim n As Long
　Dim tmp As Variant
　Dim FNo As Integer
　myPath = "C:\temp\"
　fn = "ZIP_ALL.csv"
　n = 1
　FNo = FreeFile()
　Application.ScreenUpdating = False
　Open myPath & fn For Input As #FNo
　Do Until EOF(FNo)
　　Line Input #FNo, buf
　　tmp = Split(buf, ",")
　　For i = 0 To UBound(tmp) 　　'←※こんな風にします。
　　　Cells(n, i + 1).Value = tmp(i)
　　Next i
　　n = n + 1
　Loop
　Close #FNo
　Application.ScreenUpdating = True
End Sub
'//

この回答へのお礼

WindFallerさん　こんにちは！
早々にご教授いただきありがとうございました。

教えて頂いたコードでやってみたのですが、やはり４０Mのエクセルファイルでは途中でハングしてしまいます。まっさらのエクセルファイルだと３秒ぐらいで書き込みが終わるのですが。。。。
sleep 1でＣＰＵを少し休ませることも実験したのですが、結果は同じでした。

お礼日時：2015/02/20 16:22