DirとWorkbooks.OpenTextを併用し、大量のファイルの開閉をしてます。
ファイル数2千。ファイルの行数は5千行程度。CSVファイル。
出だしは快調で10ファイル/秒程度の速さですが、だんだん遅くなり、1ファイル/5秒程度になり、ついに固まります。最初は問題ありませんでしたが、毎日処理を続け1か月目あたりから発生してます。固まるまでの処理数は、1000~500ファイル程度。
単にファイルの開閉だけなのに止まります。なぜでしょう?
Sub Macro()
TwbPath = ThisWorkbook.Path
Folder = Cells(1, "A").Value
buf = Dir(TwbPath & "\" & Folder & "\*.dat")
Do While buf <> ""
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=TwbPath & "\" & Folder & "\" & buf, Comma:=True
Application.DisplayAlerts = False
Workbooks(buf).Close savechanges:=False
Application.DisplayAlerts = True
buf = Dir()
Application.ScreenUpdating = True
Loop
End Sub
No.2ベストアンサー
- 回答日時:
こんにちわ
原因は、エクセルでのオーブン・クローズの繰り返しにあると思います。
バイナリで読み込んだらどうなるか
下のコードで試してください。
Sub MacroReadFile_InputB()
Dim THisBookPath As String, fName As String
Dim intFF As Long, xFolder As String
Dim FullFilePass As String
Dim ByteSjis, strUnicode, valArray
Dim iRow As Long
Application.ScreenUpdating = False
THisBookPath = ThisWorkbook.Path
xFolder = Cells(1, "A").Value
fName = Dir(THisBookPath & "\" & xFolder & "\*.csv")
Do While fName <> ""
intFF = FreeFile
FullFilePass = THisBookPath & "\" & xFolder & "\" & fName
Open FullFilePass For Input As #intFF
ByteSjis = InputB(FileLen(FullFilePass), #intFF) 'SJISのテキスト読み込み
Close #intFF
strUnicode = StrConv(ByteSjis, vbUnicode) 'SJISからUNICODEへ
valArray = Split(strUnicode, vbCrLf) '改行コードごとに区切って配列化 '
iRow = UBound(valArray) '行数取得
fName = Dir()
Loop
Debug.Print strUnicode
Application.ScreenUpdating = True
End Sub
ありがとうございます。
動きました。この方法で何故うまく動いたのでしょうか?
心配事がひとつあり、質問文のマクロも以前は問題なく動いてました。1ヶ月位経って突然「だんだん遅くなる」症状が出て来たのです。もう少し様子を見ます。
No.4
- 回答日時:
すいません。
回答ではないのですが、2点、確認させてください。まず、「出だしは快調で…だんだん遅くなり…」とのことですが、この時間の測定は、どのように行っているのでしょうか?
次に、「固まる」とは、何をもって固まったと判断されていますか?
何が気になっているのかと云うと、Excelって、”(応答なし)”と表示していても裏で一生懸命処理していて、いつの間にか正常終了していたことがあったので…。
だんだん遅くなるということが正確に測定されているのであれば、疑う余地はないのですが…。
ありがとうございます。
体感です。Application.ScreenUpdating=Falseは使ってますが、画面下部の幅1センチ位の部分はチラチラ動くので「画面下部が動く=ファイル開閉がある」と判定してます。
No.3
- 回答日時:
#1の回答者です。
私が、Dataファイルの処理のVBAを覚えたのは古いことですが、その件については、Datファイルをバイナリ処理で位置決めなどした覚えがありますが、#2様も多少触れていますので、その件は、私のほうからは後回しにさせていただくことにします。
Datという拡張子が付く理由なのですが、結局、相手のアプリ側の出力の問題だったりすることが多いわけで、Text なら.Txt もしくは、拡張子なしで出力させていたります。
マクロに掛ける前に、私などは、バイナリエディタやNotePad++ で内部を調べていることが多いです。ファイル構造のサンプルでもあれば、こちらでも、厳密に調べたいと思います。
(1) DoEvents
「https://oshiete.goo.ne.jp/qa/8750372.html」をご覧になったかもしれませんね。DoEvents は、ループの中に入れて、VBAの命令が受け付けない状態から復帰させるコードですね。
それと、
Application.DisplayAlerts = False
Application.DisplayAlerts = True
これは何か問題が出ていましたか?
一度外して、Doeventsを代わりにいれてみたらどうでしょうか。(以下 Macro2を参照)
(2) 問題点か?
私が自分で書き直しみて、気がついた点がいつくかあります。
Sub Macro2()
Dim Bk As Workbook
Dim FName As String
Dim buf As String
Dim TwbPath As String
Folder = ActiveSheet.Cells(1, "A").Value
TwbPath = ThisWorkbook.Path &"\" & Folder
buf = Dir(TwbPath & "*.csv")
Do While buf <> ""
Application.ScreenUpdating = False
FName = TwbPath & "\" & buf
Workbooks.OpenText Filename:=FName, Comma:=True '①ここでオブジェクトが取れない。理由は分析しているからだそうですが。
'' --略--
DoEvents
WorkBooks(buf).Close SaveChanges:=False '② ここで手間がかかっている
buf = Dir()
Application.ScreenUpdating = True
Loop
End Sub
'//
(2) -1
ここから考えられることは、「Workbooks.OpenText」を他のものに変えてみる。
データ(タブ)--外部データの取り込み-テキストファイル
||
VBA では、その等価のQueryTable
を使います。ただし、出来上がったQueryTable 面倒ですが、
ActiveSheet.QueryTables(1).Delete とか、最後に置かなくてはなりません。
(2)-2
QueryTable でも同じことですが、
新規のワークブックひとつで、書いたり消したりする。
単に計算するための場所として、以下を繰り返す
FNo = FreeFile()
i = 1
Open Fname For Input As #FNo
Do While Not EOF(FNo)
Line Input #FNo, TextLine
If TextLine <> "" Then
arBuf = Split(TextLine, ",")
Cells(i, 1).Resize(, UBound(arBuf) + 1).Value = arBuf
DoEvents
arBuf = ""
i = i + 1
End If
Loop
Close #FNo
こうしたImport メソッドを利用してみる。
集計が終わったら、ActiveSheet.UsedRange.Clear や ActiveSheet.Cells.Clear
としてしまいます。(究極的には、シートに出さなくても、コードでも可能なはずです)
文中の手間がかかるには、2つの意味があります。
・オブジェクトの生成と削除の処理に、オーバーヘッドが掛かる。
・VBAコードの実行で、物理的処理が伴うために、リターンコードの発行に時間が掛かる。
3.
>WorkBooks(buf).Close SaveChanges:=False
この後、次のステップにかかる前に、Sleepを入れてみる。
モジュール一番上に
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
とおくものですが、ご存知だと思います。時間は適当ですが、500 ms ぐらいか?
4.
メモリリークの検査は専門的ですから、これも後に回したほうがよいかもしれません。
//
#2様のUnicodeの話は、時間の許す限りは検証したいと思います。前年にUnicode(UTF-16 B付き,Bなし、Little Endian, Big Endianなど)散々ここらのあたりは、様々なコードで試したものの、なかなか難しくて、途中で保留にしてしまいました。
ありがとうございます。
前後を挟むのはやってないです。落ち着いたら色々試してみます。
質問文の方法は
https://oshiete.goo.ne.jp/qa/9175852.html
ここでうまくいったため、今回、同じ方法を踏襲しました。ところが同じ現象が再発し、Lineを使う方法に戻したら上手く行った、使い分けの判断がまったく分からない状態です。
No.1
- 回答日時:
こんにちは。
私には経験はありませんが、その状況は、メモリー・リークを起こしている状態に似ています。今、私のところでは解決の目処があるわけではありませんが、
*.dat
とは、内容にもよりますが、Excelで扱う.datファイルは、基本的には、Textファイルに非常に似ていますが、バイナリを含んでいたりしますので、OpenTextで開いても、終了時には、Excelのメモリに一部を残してしまっているものだと思います。
そのマクロが始まった時から、止まるまでの時、タスクマネージャのメモリ状態の変化などを最初に調べてみるところから始まると思います。
Microsoft には、様々ツールがありますし、もし、現行のタスクマネージャで足りない場合は、Microsoft Process Explorer もあります。
メモリリークを調べるRAMMap というのもあるそうです。
しかし、結局のところ、datファイルを開くためのマッチングを図らないといけないでしょう。
".dat" の拡張子は、汎用性だとは言われますが、Excelで扱うときには決まったものが存在します。ご質問のマクロは、ファイルの開け閉めで、最終的には何をしているのでしょうか?Viewer(ヴューワー) のようでもあるし、そうでないのかもしれません。何かを検索しているようなプログラムにも思えます。
その目的によっては、まったく別な方法を考えてもよろしいのかもしれませんね。
なお、現状のままで処理する場合というか、私の場合は、Dir 関数で予めファイル名を配列変数に入れて、その取得したリストでもって、ファイルをIn/Out することが多いです。Dir 関数は、次のループのDir 関数に別な仕事をすると、何かおかしな動きをすることがあります。今回は、まったく関係がないのかもしれません。暗中模索の状態です。
いつもありがとうございます。
.datの中身は文字のテキストです。処理内容は(質問文では省いてますが)特定文字列を列ごとにカウントし集計します。
Dirに関しては、御推察通りこの処理の後でもう一回ループで回してます。ただ、ここまでたどり着かないので質問文では割愛しました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
エクセルVBAが途中で止まります
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ファイル名の先頭にアンダース...
-
csvファイルを開かずに文字を検...
-
VBSでファイルを開く
-
ファイルへの書き込み
-
C++書式について
-
Cコンパイル時のマルチデファイ...
-
COM相互運用機能のON,OFFによる...
-
マウスポインタの変更
-
ファイルを開かずにカスタムド...
-
ライトモードとリードモードの...
-
C言語初心者の質問失礼します。
-
SGファイルって何ですか?
-
[VBScript]ファイルの入出力を...
-
OpenDialog1->FileNameについて
-
フリーソフトウェアでflaファイ...
-
【C#】リソースファイルの埋め...
-
プログラムファイルの分割で…
-
アプリケーション終了時例外エ...
-
バッチで118項目のCSVを処理し...
-
VB.NET ファイルをひとつにまと...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
C言語初心者の質問失礼します。
-
csvファイルを開かずに文字を検...
-
ファイル名の先頭にアンダース...
-
テキストファイルの最終行を削...
-
CSVファイルへの保存の際、デー...
-
VBに、Cのincludeのようなもの...
-
分割コンパイルの#defineについて
-
SGファイルって何ですか?
-
グローバル変数のよくない使い...
-
Excelマクロでの再読込み方法
-
iniファイルに追記がしたいです。
-
ASP .NETでファイル選択ダイア...
-
ハッシュの計算時間について
-
バッチで118項目のCSVを処理し...
-
FileOpen 関数で既にファイル...
-
Javascript で INI の読み書き
-
fopenできる上限の変更
-
VBAにてEXCEL以外のファイル(テ...
-
マウスポインタの変更
-
C言語のfopenについて教えてく...
おすすめ情報
詰まってる行はworkbooks.opentextです。ESCキーで中断するといつもここで止まってます。「VBA だんだん遅くなる」で検索すると同様な症状に思えるので、10ファイルおきにDoEventsを入れましたが変わらず。
Windows7, Excel2010です。