xlsApp.Quit してもQuitされない事があって、困っています。
解決VB6を久しぶりに1年前から趣味で使っています（Windows98）。
MSDNのサンプルを参考にしてVB6プログラム（以下EXE）を作成しました。EXEは、ある計算
をして、予め作成しておいたExcel Bookに書き込みます。書き込むシート数が5、データ数が
各シートに100行程度の場合には正常に書き込みでき、正常終了します。しかし、EXEの実行中
にxlsApp.QuitしてもCtl+Alt+DelするとExcelは実行中と表示され、EXE終了と同時にExcelも
終了される（メモリから消える）のが気になります。
ところで私の質問は、書き込むシートのうち、1シートだけ7000×20セルのデータを約4分間
かけて書き込んだ（この間CPU使用率ほぼ100%）後、Book.Save して、xlsApp.Quit して、
宣言した全てのオブジェクト変数=Nothingして、EXEを終了すると、Ctl+Alt+Delしても
Excelは起動状態で、EXE終了と同時にCPU使用率がほぼ100%となります（正常終了でき
ません）。Excelへの書き込みは完了しています。
質問１ xlsApp.QuitしてもメモリからExcelが消えず、EXE終了と同時に消えるのは正常ですか？
質問２ 多くのデータを書き込むと、EXE終了と同時にExcelを終了できない問題の解決方法を
教えて下さい。
以上、よろしくお願いします。
質問者から問題の状況を追加報告しておきます。
１ xlsApp.Quitの前に当然ですが Book.Closeしています。
２ Excel Bookは予め印刷し易いレイアウトを作成し保存し、当然EXE起動前に閉じています。
３ 書き込むデータ数が少ない条件において、EXEの起動、終了を何度も繰り返して、Freeze
しない事を確認済みです。
４ 最初の質問における「メモリから消えない」とは「タスク一覧に残っている」を意味します。
以上、よろしくお願いします。
質問者から問題の状況を追加報告しておきます。
１ 確認のため xlsApp.Quit の前に On Error Resume Next 行を設けましたところ、
Excel Book に大量のデータを書き込み Book.Save、Book.Close すると、
xlsApp.Quit 後において Err.Number=0（エラー無し）でした。という事は、xlsApp.Quit
は成功していると思われますが、Ctl+Alt+Del するとタスク一覧にExcel が残っており、CPU
使用率もほぼ100%が続きます。現状ではEXE終了後、Ctl+Alt+Del でExcelを閉じています。
[VB6.0]の場合は、解放処理もあまり考えなくて
いいですし、おそらく、エクセルに負荷を掛け過ぎ
ているんでしょうね。エクセルはメモリ管理が下手
ですし、データが変更されるとそれに伴って色々
な処理が実行され、加速度的に遅くなる現象が
起こります。ですから、最小限のアクセスにとどめる
様な処理が無いか？工夫する必要があります。
セルに１つ１つデータを書き込んでいませんか？
配列を利用して、複数のデータを一度に書き込む
ような処理に変えてみてください。
http://madia.world.coocan.jp/cgi-bin/VBBBS2/wwwlng.cgi?print+200403/04030021.txt
一度に書き込む量は、マシンスペックも気になる
ところですが、とりあえず1行が20セルなら、それを
7000回繰り返すようにしてみてください。
その際、配列は Redim で初期化せず、データの
無い部分を "" で埋めるなどしてみてください。
いい結果が出なければ、配列の大きさを変更
してみてください。
また、シートの再表示もしないようにしてください。
# CPU 750MHz、メモリ200MBくらいのマシン
# ならテストした事がありますが、10秒以内で
# 書き込み処理は終了できます。
はい。「VBレスキュー」の中にご指摘の方法をみつけ、やってみました。
書き込み時間は約1/10になりました。EXCEL終了時のメモリの解放がうまく
いかない症状は、出たり出なかったり、という程までに改善しました。
解決までにもう少しみたいです。また迷ったら相談します。
「特攻隊長まるるう」さん、早いご回答を頂き感謝します。
>EXCEL終了時のメモリの解放がうまくいかない症状は、
>出たり出なかったり、という程までに改善しました。
根本的な原因は xlsApp.Quit でも Book.Save、Book.Close
でもないと想像しています。セルへの書き込み処理でエクセル
内部の処理に遅延が発生し、それが積み重なって処理でき
なくなっていると予想しています。
一般的に言われている『エクセルのプロセスが残る』問題とは、
少し内容と原因が違います。↑の意味でメモリの解放ができて
いない場合は、CPU使用率は上がりません。
あくまで Excel 内部の処理が完了できなくなっている状態だと
思われます。
つまり、Book を閉じる前に改善できていなければ、解決
することは無いと考えてください。
ここからは Win2000、Excel2000 で検証した情報です。
PCのスペックは前述の通りです。
以前、10列 * 50万行程度の書き込みを行った事があります。
>最小限のアクセスにとどめる様な処理が無いか？
>工夫する必要があります。
こう書きました通り、65,000行(１シート分)を一度の処理で
書き込むのが一番高速であると考え、実行しました。
１シート目は20秒程度で終了したのですが、２シート目
では、同じ処理なのに30秒、以降40秒、50秒。。。と
どんどん遅くなる現象が発生しました。
色々と試した結果、この環境では１行ずつ処理することで
落ち着きました。最終的には50万行で４分くらい、10万行
単位で、50秒くらいのタイムをコンスタントに出せるようになり
ました。(Sleep 等でCPU使用率は80％程度に抑えました。)
実行環境のスペックによって最適な処理は変わってくると思います。
>１ 確認のため xlsApp.Quit の前に On Error Resume Next 行を設けましたところ、
関係ないと思われます。VB側の処理の問題ではなく、Excel
内部の処理が間に合っていないのです。
エクセルの場合、ブックを開くとロックがかかることはご存知
ですよね？エクセル内部の演算処理が終了していない場合、
その処理が終わるまで、ファイルのロック解除の処理などが
実行できません。つまり終了できません。
保存処理も実行できない(プログラムから命令はできても
実際にファイルに書き込まれていない)と思いますが、
>Excelは起動状態で、EXE終了と同時にCPU使用率がほぼ100%となります（正常終了でき
>ません）。Excelへの書き込みは完了しています。
変更点は保存できていましたか？
以上のような経験から、ファイルを閉じる前にCPU使用率が
落ち着いていないと、この質問は解決しないと思っています。
場合によっては、Sleep や OnTime で待ち時間を作ってやる
必要があるかもしれません。API に関しては積極的に使用
しないほうがいいのでは？という意見もありますので、ご自分で
調べて判断してください。
http://madia.world.coocan.jp/cgi-bin/VBBBS/wwwlng.cgi?print+200606/06060002.txt
追加の情報として、エクセル側でセルのコピーなどを実行すると
エクセルの負担が増えますので、セルに書き込むデータの演算等は、
できる限りVB側で行いました。
VBレスキュー（花ちゃん）
http://support.microsoft.com/default.aspx?scid=kb;ja;JP414107
に詳しく書いています。（[XL2000] オートメーションでセルの値の取得やコピーを繰り返すと
応答しない）
「特攻隊長まるるう」さんへ、ありがとうございました。
VBレスキュー（花ちゃん）のVB6 & Excel コーナーで紹介されている
http://support.microsoft.com/default.aspx?scid=kb;ja;JP414107
に詳しく書いています。（[XL2000] オートメーションでセルの値の取得やコピーを繰り返すと
応答しない）
が、まさに私のプログラミング環境を説明しています。
それから、従来はxlsheet.cells(2,5).value=AAAA のように１セルずつ書き込んでいたのを
ご助言のとおり、１行ずつ文字列をつなげておいて書き込むようにしました。
どうもありがとうございました。今後も何かありましたら相談させて下さい。
こちら質問者「解決できず」です。完全ではありませんがほぼ問題解決
しましたので報告します。
問題１ EXE内でxlsApp.Quitをしても、その時点でタスクにExcelが残り
（CPU使用率は0%）、EXEを終了した時点でタスクからExcelが消える。
問題２ EXE内で大量にBook.Sheetに書き込みをした場合、EXEを終了して
もタスクにExcelが残り、CPU使用率がほぼ100%となる。
「特攻隊長まるるう」様の助言を頂いた後の結果は、
問題１ 解決せず。でも問題１はEXE実行において重症ではありませんので
ゆっくり時間をかけて、xlsApp.やxlsBook.等の記載漏れを探してみます。
問題２ 解決しました。何と、具体的には、次の従来のコード
With xlsSheet
For i=0 to 5,000
For j=0 to 20
.cells(i, j).value = AAAA
Next j
Next i
End With を
With xlsSheet.cells
For i=0 to 5,000
For j=0 to 20
.item(i, j).value = AAAA
Next j
Next i
End With
にしただけで、書き込み時間は改善しませんが、cell書き込みも成功し、
EXE終了と同時にタスクからExcelも消えました。もう、いちいちCtl+Alt
+CelでExcelを終了する必要が無くなりました。
「特攻隊長まるるう」さん、感謝します。目からウロコです。
「特攻隊長まるるう」様が教えて下さったサイトを経由して、
http://support.microsoft.com/default.aspx?scid=kb;ja;JP414107
[XL2000] オートメーションでセルの値の取得やコピーを繰り返すと応答しない）
にたどりつき、そこを参考にして「私が最適と考える」次のサブルーチンを作成しました
結果、うまくいきましたので報告します。要点は、
１ Variant配列にBook.Sheetの中の書き込みたい位置(Range)の書式等を(空白でいいから)
記憶させる。MyArray = Book.Sheet.Range( )
２ MyArray に所望の値(私の場合は計算結果)を記憶させる。
３ 前記１の位置(Range)に再び書き戻す。Book.Sheet.Range( ) = MyArray です。
Sub Test()
Dim i as integer, j as integer, Row1 as Integer, wb As String
Dim MyArray As Variant
Dim MyObject As Object
Set MyObject = CreateObject("excel.application")
MyObject.Application.ScreenUpdating = False
MyObject.Application.DisplayAlerts = False
MyObject.Visible = True
'1頁分(A1:T60 に適当な書式を指定したブック Book1.xls を作成しておきます。
wb = MyObject.Workbooks.Open("C:\Book1.xls").Name
'1ずつ(番号0だけ)大きい配列とします。
ReDim MyArray(0 to 60, 0 to 20) '1頁の書き込む領域分
'MyArray にワークシートの書き込む領域(Range)を書式を含めて保持します。
'Range("A1", "it99")は空白セルの集まりでも構いません
MyArray = MyObject.Workbooks(wb).Worksheets(1).Range("A1", "it99")
'例えばひねくれて ReDim MyArray(3 to 102, 3 to 257)としても、前記行を
'実行した途端、MyArray(0 to 99, 0 to 254)に変更されてしまいます。
Row1 = 1
For i = 0 to 印刷頁数 - 1
'1頁分をコピーします
xlsSheetDst.Rows(CStr(Row1) & ":" & CStr(Row1 + 59)).Select
xlsApp.Selection.Copy Destination:=Rows(CStr(Row1 + 60) & ":" & CStr(Row1 + 60))
For j = 1 to 60
For k = 1 to 20
'ここで MyArray に所望の値をセットします。
MyArray(j, k) = XXXXXXXX
Next k
Next j
'Book.Sheet にMyArray を書き戻します
MyObject.Workbooks(wb).Worksheets(2).Range("A1", "IT99") = MyArray
Next i
ReDim MyArray(0, 0)
MyObject.Workbooks(wb).Save
MyObject.Quit
Set MyObject = Nothing
End Sub
さらなる修正案を載せておきます。
--------------------------------------
(1) Application の操作
> MyObject.Application.ScreenUpdating = False
> MyObject.Application.DisplayAlerts = False
上記の記述は冗長といえます。この場合は、
MyObject.ScreenUpdating = False
MyObject.DisplayAlerts = False
のように記述する事をおすすめします。
Application プロパティは、Application オブジェクトへの参照を返しますので、
MyObject.ScreenUpdating = False
MyObject.Application.ScreenUpdating = False
MyObject.Application.Application.ScreenUpdating = False
MyObject.Application.Application.Application.ScreenUpdating = False
は、いずれも全く同じ結果を示すからです。
--------------------------------------
(2) Workbook の操作
> wb = MyObject.Workbooks.Open("C:\Book1.xls").Name
：
> MyObject.Workbooks(wb).Worksheets(2).Range("A1", "IT99") = MyArray
：
> MyObject.Workbooks(wb).Save
ワークブック名を String 型で保持しておくのではなく、
Dim wb As Object 'As Excel.Workbook
Set wb = MyObject.Workbooks.Open("C:\Book1.xls")
のように、Workbook オブジェクトそのものを変数に保持しておきましょう。
そうすれば、
> MyObject.Workbooks(wb).Save
というコードも、
wb.Save
のように短く記述できますし、また、途中でファイル名を変更するような場合にも便利です。
--------------------------------------
(3) 範囲配列の操作
> Dim MyArray As Variant
> ReDim MyArray(0 to 60, 0 to 20) '1頁の書き込む領域分
> MyArray = MyObject.Workbooks(wb).Worksheets(1).Range("A1", "it99")
ここで ReDim を行うことは、実は意味がありません。この場合には、
Dim MyArray As Variant
MyArray = MyObject.Workbooks(wb).Worksheets(1).Range("A1", "it99").Value
のように、ReDim 無しで代入しても大丈夫です。
Range オブジェクト(の既定のプロパティ)は、新たな配列を作成して返すので、事前に
ReDim 等で確保しておいたとしても、その次の代入式で上書きされてしまいます。
> '実行した途端、MyArray(0 to 99, 0 to 254)に変更されてしまいます。
これはおそらく、MyArray(1 To 99, 0 To 254) ではないでしょうか?
Excel の Range から返される配列は、0ベースではなく、1ベースの配列だと思います。
MyArray への代入後、LBound(MyArray, 1) および LBound(MyArray, 2) が、
どのような値を返してくるのかを確認してみてください。
ちなみに、.Range("A1", "it99") というコードは、.Range("A1:IT99") とも書けます。
--------------------------------------
(4) オブジェクトへの暗黙の参照を利用しない。
> xlsSheetDst.Rows(CStr(Row1) & ":" & CStr(Row1 + 59)).Select
> xlsApp.Selection.Copy Destination:=Rows(CStr(Row1 + 60) & ":" & CStr(Row1 + 60))
変数 xlsSheetDst はどこで宣言されているのでしょうか?
また、xlsApp というのは何者でしょう。MyObject とは別の Excel ですか?
それはともかくとして、上記のような操作を行っているという事は、おそらくは
Excel Library を参照設定しているのかと思います。
その場合、グローバルオブジェクトを省略しないようにしてください。たとえば上記では、
〜.Copy Destination:=Rows(〜)
のように書かれていますが、これは正しくは、
〜.Copy Destination:=MySheet.Rows(〜)
のように、上位オブジェクトまで明示した記述が必要とされます。
Excel VBA ならば、自身を操作していることが明確なので、あまり問題にはなりませんが、
VB からの制御時には、上位オブジェクトを省略すると、Excel が解放されずに残ってしまったり、
二回目の操作がエラーになるなどといった、思わぬ誤動作を引き起こします。
これは Excel を外部から操作するに当たって、もっとも重要な点の一つなので、特に気をつけてください。
なお、『参照設定』を行っていない場合には、上位オブジェクトを省略することができなくなるため、
このようなオブジェクト指定のミスを防ぐことができます。
--------------------------------------
(5) Select / Selection 系の操作回数を減らす。
> xlsSheetDst.Rows(CStr(Row1) & ":" & CStr(Row1 + 59)).Select
> xlsApp.Selection.Copy Destination:=Rows(CStr(Row1 + 60) & ":" & CStr(Row1 + 60))
Select してから、それを Selection で再操作するといった行為は、操作としてはやや冗長です。
すべての Selection 操作が不要というわけではありませんが、たとえば上記の場合には、
〜.Rows(…).Select
〜.Selection.Copy Destination:=〜
のように記述する代わりに、
〜.Rows(…).Copy Destination:=〜
などと記述する事ができないかを検討した方が良いでしょう。
Select や Active 系の操作は、フォーカスの移動を伴いますので、その分、処理時間もかかります。
また、思わぬユーザー操作によって、選択範囲が変更されてしまった場合などにも対応しにくく
なってしまいますから、Selection 等の使用は最低限度に留めておいた方が安全です。
--------------------------------------
(6) 使用した変数の後処理
> ReDim MyArray(0, 0)
> MyObject.Workbooks(wb).Save
> MyObject.Quit
> Set MyObject = Nothing
配列のリセットを望むのであれば、Erase ステートメントを使った方が良いと思います。
ただし MyArray も MyObject も、この Sub Test() 内のプロシージャレベル変数なので、
Erase や Nothing 代入を行わずとも、End Sub が処理された時点で、自動的かつ確実に、
変数の解放処理が行われます。そのため、早期に解放処理を行わせたい場合を除いては、
Nothing 代入などは行わなくても問題無かったりします。
提案者の「旧 解決できず」です。
う〜ん。なるほど。今から実行してみます。有難うございます。
| ツイート |  |