Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


12858 / 13646 ツリー ←次へ | 前へ→

【8210】メッセージボックス出力と同様のイベントとは? 初心者 03/10/3(金) 19:46 質問
【8211】Re:メッセージボックス出力と同様のイベントとは? INA 03/10/3(金) 19:54 回答
【8212】Re:メッセージボックス出力と同様のイベントとは? 初心者 03/10/3(金) 20:10 質問
【8213】Re:メッセージボックス出力と同様のイベントとは? Kein 03/10/3(金) 20:50 回答
【8214】Re:メッセージボックス出力と同様のイベントとは? 初心者 03/10/3(金) 21:26 質問
【8217】Re:メッセージボックス出力と同様のイベントとは? bykin 03/10/3(金) 22:23 回答
【8220】Re:メッセージボックス出力と同様のイベントとは? 初心者 03/10/4(土) 0:55 発言
【8225】Re:メッセージボックス出力と同様のイベントとは? bykin 03/10/4(土) 19:10 回答
【8259】Re:メッセージボックス出力と同様のイベントとは? 初心者 03/10/6(月) 12:06 お礼
【8273】Re:メッセージボックス出力と同様のイベントとは? bykin 03/10/6(月) 18:15 回答
【8275】Re:メッセージボックス出力と同様のイベントとは? 初心者 03/10/6(月) 19:52 発言
【8277】Re:メッセージボックス出力と同様のイベントとは? bykin 03/10/6(月) 21:08 回答
【8287】bykinさんに一票 しのしの 03/10/7(火) 12:29 回答
【8291】Re:bykinさんに一票 初心者 03/10/7(火) 15:05 質問
【8292】FileFormatですか しのしの 03/10/7(火) 16:03 回答
【8293】サンプル変でしたね。すいません。 しのしの 03/10/7(火) 16:07 回答
【8300】Re:サンプル変でしたね。すいません。 初心者 03/10/7(火) 19:09 お礼

【8210】メッセージボックス出力と同様のイベントとは?
質問  初心者  - 03/10/3(金) 19:46 -

引用なし
パスワード
   現在複数のエクセルファイルを扱う処理をしています。
ファイルの処理数が増えると、メモリ不足またはワトソン博士表示にて
エクセルが強制終了されてしまいます。

(処理)
1つのエクセルファイル‘A.xls’を‘B.xls’の名でコピーしてそれを開き、
データ操作後、‘C.xls’の名で別名保存・‘B.xls’を削除する。
(‘B.xls’を上書保存(Save)後、Close(True)では‘B.xls’がCLOSEされず、
 Close(False)ではSaveが無効になってしまいました)
これを複数ファイル分繰り返します。
Openするファイルは、複数シート処理する為、エクセル限定の仕様です。


試してみたのは‘C.xls’の保存直後にDoEventsの記述や10秒ほどWaitさせる処理や
メモリの使用可能量の取得を記述しました。
結果、若干処理数は増えましたが、とても不安定な状態です。
次に、‘C.xls’の保存直後にメモリの使用可能量をMsgbox出力させてみたところ、
100件以上の処理でもメモリ不足は発生しませんでした。
(メッセージボックスの'on'ボタンは表示後1〜3秒ほどでクリックしました)

ファイルの数だけMsgbox出力をさせておくことはできないので、
Msgbox出力に変わる処理を加えたいのですが、どのような処理が
該当するのでしょうか?

ご存知の方、ご教授頂けますようお願い致します。

 

【8211】Re:メッセージボックス出力と同様のイベントとは?
回答  INA  - 03/10/3(金) 19:54 -

引用なし
パスワード
   ユーザーフォームを使えば、よいのではないでしょうか?

【8212】Re:メッセージボックス出力と同様のイベントとは?
質問  初心者  - 03/10/3(金) 20:10 -

引用なし
パスワード
   ▼INA さん:
>ユーザーフォームを使えば、よいのではないでしょうか?

お返事ありがとうございます。
ユーザーフォームということは、各ファイル処理ごとに
何か操作をするということでしょうか?
検討違いな解釈でしたらすみません。

できれば、実行ボタンを押す以外の操作は無い状態で、
裏でMsgbox表示と同じようにメモリ不足にならない
処理を行いたいのですが・・・。

【8213】Re:メッセージボックス出力と同様のイベントとは?
回答  Kein  - 03/10/3(金) 20:50 -

引用なし
パスワード
   >ファイルの数だけMsgbox出力をさせておくことはできないので、
>Msgbox出力に変わる処理

Debug.Print 値

とすれば、イミディエイトウィンドゥに出力できます。どのブックの値なのか
分かるように

Debug.Print ActiveWorkbookName & " = " & 値

などとしておけば良いでしょう。

【8214】Re:メッセージボックス出力と同様のイベントとは?
質問  初心者  - 03/10/3(金) 21:26 -

引用なし
パスワード
   ▼Kein さん:

回答ありがとうございます。
皆様、本当にすみません。
自分の文章力の無さを痛感しています。

メッセージボックスと同様の機能とは・・・。

値の取得ではなく、アプリケーションを落とさない
(メモリ不足による強制終了を回避する)為の
代替策を模索しています。
メッセージボックスは値取得の為でなく、
処理途中でイベントを起す目的で記述しました。

メッセージボックスを出力することで、アプリケーションに
何らかのイベントもしくは内部処理(?)が発生するらしく、
メッセージボックスを出力せずに処理を流した時には表示される
‘ワトソン博士’が、「表示されずに正常終了」することが判りました。
そこで、メッセージボックスのように手を煩わす機能ではなく、
見えない所でアプリケーションに働きかける機能を探しているのです。

メッセージボックスの表示によって、何故メモリ不足にならないのかを
教えて頂くのでも大変助かります。

宜しくお願い致します。

【8217】Re:メッセージボックス出力と同様のイベントとは?
回答  bykin  - 03/10/3(金) 22:23 -

引用なし
パスワード
   こんばんわ。

>1つのエクセルファイル‘A.xls’を‘B.xls’の名でコピーしてそれを開き、
>データ操作後、‘C.xls’の名で別名保存・‘B.xls’を削除する。

ここだけ読んだら、なんで B.xls が必要なんか、さっぱりわからんのやけど(^^;;
最終的に削除してまうんでっしゃろ?
A.xls をそのまま開いてデータ操作したあとで C.xls という名前で保存するだけで
ええと思うねんけどな。

>値の取得ではなく、アプリケーションを落とさない
>(メモリ不足による強制終了を回避する)為の
>代替策を模索しています。

ループで処理してるんかな?
標準モジュールの宣言セクション(先頭部分)に

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

って書いて、MsgBox の代わりに

Sleep 100

とか書いたらちょっとはマシになりまへんか?

はずしてたらかんにんな。
ほな。

【8220】Re:メッセージボックス出力と同様のイベントとは?
発言  初心者  - 03/10/4(土) 0:55 -

引用なし
パスワード
   ▼bykin さん:
回答ありがとうございます。

>ここだけ読んだら、なんで B.xls が必要なんか、さっぱりわからんのやけど(^^;;
>最終的に削除してまうんでっしゃろ?
>A.xls をそのまま開いてデータ操作したあとで C.xls という名前で保存するだけで
>ええと思うねんけどな。

最初の質問に記述した繰り返しになりますが、
A.xlsを別名コピーしてC.xlsを作成し、それをOpen→Save→Closeしたのですが、
Save→Closeが上手く処理できませんでした。
close(true)だとcloseされずにブックが開いたままで、close(false)だと
Saveそのものが無効になってしまい、仕方ないのでB.xlsを登場させました。
「A.xls をそのまま開いて」は、処理中にアプリケーションが落ちてファイルが
壊れることを想定して、元のファイルは綺麗に残したのですが・・・。
不要な心配なのでしょうか?


>ループで処理してるんかな?

はい。1回のループ処理でC.xlsが1つ作成されます。


>標準モジュールの宣言セクション(先頭部分)に
>Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
>って書いて、MsgBox の代わりに
>Sleep 100
>とか書いたらちょっとはマシになりまへんか?

ありがとうございます。早速試してみます。
DoEventsやWaitを使用してみた限りでは、若干の改善しか見られませんでしたので、
何らかの取っ掛かりとなるヒントを頂けると、とても嬉しいです。

【8225】Re:メッセージボックス出力と同様のイベントとは?
回答  bykin  - 03/10/4(土) 19:10 -

引用なし
パスワード
   こんにちわ。

>「A.xls をそのまま開いて」は、処理中にアプリケーションが落ちてファイルが
>壊れることを想定して、元のファイルは綺麗に残したのですが・・・。
>不要な心配なのでしょうか?

A.xls を開いて、すぐに別名を付けて保存する。
その後は必要な処理をして、最後に上書き保存する。
・・・ってのをループにすれば A.xls を壊す心配もほとんど無いんとちゃいまっか?
マクロが書いてある制御用のブックと同じフォルダに入ってる Book1.xls〜Book3.xls
を順番に開いて Book1_new.xls〜Book3_new.xls として保存してから処理をするって
コードを書いてみました。
既に同名のファイルが存在する場合のチェックは省略してます。

Sub test2()
  Dim ArrayBooks As Variant
  Dim i As Integer
  
  Application.ScreenUpdating = False
  ArrayBooks = Array("Book1", "Book2", "Book3")
  For i = LBound(ArrayBooks) To UBound(ArrayBooks)
    With Workbooks.Open(ThisWorkbook.Path & "\" & ArrayBooks(i) & ".xls")
      .SaveAs ThisWorkbook.Path & "\" & ArrayBooks(i) & "_new.xls"
    
      'ここで必要な処理を実行(↓例)
      .Worksheets(1).Range("A1").Value = Now()
    
      .Save
      .Close
    End With
  Next
  Application.ScreenUpdating = True
End Sub

ループ内の処理のスピードアップについては、↓これが参考になるかも・・・
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=8037;id=excel

ま、なんにしても作業の前にはデータのバックアップってのが基本ですわなー
ほな。

【8259】Re:メッセージボックス出力と同様のイベントとは?
お礼  初心者  - 03/10/6(月) 12:06 -

引用なし
パスワード
   ▼bykin さん:
回答ありがとうございました。
お返事遅くなり、すみません。

>A.xls を開いて、すぐに別名を付けて保存する。
>その後は必要な処理をして、最後に上書き保存する。

試してみました。
途中の編集が色々(テキストファイルデータ読込等)あるので、Withでは囲むのではなく、
Workbooks(ファイル名).SaveAs と Workbooks(ファイル名).Save と
Workbooks(ファイル名).Close を使用してみたのですが・・・。
これが原因なのかもしれませんが、やはり上手く保存されませんでした。
最後のWorkbooks(ファイル名).Save「上書保存」後、
 1.Workbooks(ファイル名).Close(True) → Closeされずに開いたまま
 2.Workbooks(ファイル名).Close    → SaveAsの状態(編集前)で保存
となってしまいました。

MsgBoxのような‘メモリに働きかける動き’は見つからなかったのですが、
時間切れになりつつあるので、最終手段として、適当なファイル数で処理にストップをかける動きにしてしまおうかと考えています。
(ワトソン博士表示よりはマシというレベルですが・・・)

色々とありがとうございました。
今後とも、宜しくお願い致します。

【8273】Re:メッセージボックス出力と同様のイベントとは?
回答  bykin  - 03/10/6(月) 18:15 -

引用なし
パスワード
   こんばんわ。

>Workbooks(ファイル名).SaveAs と Workbooks(ファイル名).Save と
>Workbooks(ファイル名).Close を使用してみたのですが・・・。

↑この「ファイル名」はどうなってるんかな?
Workbooks(元ファイル名).SaveAs 新ファイル名
の次は、ちゃんと
Workbooks(新ファイル名).Save
Workbooks(新ファイル名).Close
になってるんでっしゃろか?

全部「元ファイル名」のままで、しかもコードの中に On Error Resume Next なんて
書いてあったりしまへんか?

はずしてたらかんにんな。
ほな。

【8275】Re:メッセージボックス出力と同様のイベントとは?
発言  初心者  - 03/10/6(月) 19:52 -

引用なし
パスワード
   ▼bykin さん:

何度もお手数をお掛けしてすみません。

>↑この「ファイル名」はどうなってるんかな?
>Workbooks(元ファイル名).SaveAs 新ファイル名
>の次は、ちゃんと
>Workbooks(新ファイル名).Save
>Workbooks(新ファイル名).Close
>になってるんでっしゃろか?

ファイル名はSaveもCloseも新ファイル名です。
Close(false)とすると、直前のSaveは無効にされてしまいますが、
ファイルCloseは行われるので、名前は正しいはずなのです。
(最初のSaveAsの状態でCloseされています)
Close(True)とする時のみ、Closeが無視されてしまいます。
この時、更新日付は上書保存の時刻に更新されています。


>全部「元ファイル名」のままで、しかもコードの中に On Error Resume Next なんて
>書いてあったりしまへんか?

エラー処理は「On Error Goto エラー処理」で、メッセージを表示させるように
していますが、何事もなく正常終了してしまいます。
On Errorステートメントをコメントアウトしても同様でした。
後続処理は正しく処理されます。
にも拘らず、ファイルが開いたままなのです。

因みに、A.xlsをOpenし、そのまま編集→上書保存→Closeは
Closeまで正常に処理できました。

【8277】Re:メッセージボックス出力と同様のイベントとは?
回答  bykin  - 03/10/6(月) 21:08 -

引用なし
パスワード
   こんばんわ。

うーん・・・わからんなー
ブックをオブジェクト変数に取り込んでみたらどうなんかな?
前に提示したコードを変更してみると・・・

Sub test3()
  Dim ArrayBooks As Variant
  Dim TargetBook As Workbook
  Dim i As Integer
 
  Application.ScreenUpdating = False
  ArrayBooks = Array("Book1", "Book2", "Book3")
  For i = LBound(ArrayBooks) To UBound(ArrayBooks)
    Set TargetBook = Workbooks.Open(ThisWorkbook.Path & "\" & ArrayBooks(i) & ".xls")
    TargetBook.SaveAs ThisWorkbook.Path & "\" & ArrayBooks(i) & "_new.xls"
  
    'ここで必要な処理を実行(↓例)
    TargetBook.Worksheets(1).Range("A1").Value = Now()
  
    TargetBook.Save
    TargetBook.Close
  Next
  Set TargetBook = Nothing
  Application.ScreenUpdating = True
End Sub

ところで・・・まずはSaveAs,Save,Close自体が怪しいのか
途中の処理が怪しいのかをはっきりさせたほうがええと思います。

途中の処理を全部コメントアウトして、単に「別名保存→保存→閉じる」だけの
コードにして実験する。
それでもうまいこといかんかったらSaveAs・・・が怪しいんやけど、恐らく
途中の処理が原因なんやないかな?

で、コメントアウトしてる処理を少しずつ非コメント化して順番に実行することで
どこに問題があるんかを見つけ出す・・・ってことになるんとちゃいまっか?
(On Error Goto はコメントアウトしておく)

原因がわからんときのデバッグ作業ってのは大変やけど、地道にやることが
結局一番早道やと思います。

一般論ですんまへん。
ほな。

【8287】bykinさんに一票
回答  しのしの  - 03/10/7(火) 12:29 -

引用なし
パスワード
   初心者さん、bykinさんこんにちは。
私も初心者さんのような仕様の場合は、bykinさんのような方法で処理しています。
が、いちどもエラーが発生したことはありませんよ。

Dim bkA As Excel.Workbook  'A.xls
Dim strA  As String    'A.xlsのFullname
Dim strC  As String    'C.xls のFullname

'A.xlsを開く
Set bkA = Workbooks.Open(Filename:=strA, ReadOnly:=True)

'C.xlsの内容に書き換え処理


'作業中のA.xlsをC.xlsとしてに保存
bkA.SaveCopyAs (strC)

'Aを閉じる
bkA.Close savechanges:=False
Set bkA = Nothing


A.xlsを開いて、データ操作中に異常処理しても、
保存さえしなれば、書き換えられることは無いと思います。
(開いているのはオブジェクトのインスタンス、とお考えください。)

あくまで、予想なんですが、
ファイルの処理数が増えるとエクセルが強制終了される
のと
B.xlsあるいは、C.xlsがCloseできない、
というのは、同じ原因の可能性が高いと思います。

たとえば、"データ操作"中に、開いているブックのオブジェクトを取得しているのに、
きちんと、参照解放せぬままCLOSEしようとしていませんか?
#例えば、モジュールの内部変数、クラスの内部変数などに、
#ブックのシートを参照設定しておいて、その解放がきちんとされていない。
#実は.netから操作していて、RCWの参照カウントが残っているなど。

>ところで・・・まずはSaveAs,Save,Close自体が怪しいのか
>途中の処理が怪しいのかをはっきりさせたほうがええと思います。
>
>途中の処理を全部コメントアウトして、単に「別名保存→保存→閉じる」だけの
>コードにして実験する。
>それでもうまいこといかんかったらSaveAs・・・が怪しいんやけど、恐らく
>途中の処理が原因なんやないかな?
>
>で、コメントアウトしてる処理を少しずつ非コメント化して順番に実行することで
>どこに問題があるんかを見つけ出す・・・ってことになるんとちゃいまっか?
>(On Error Goto はコメントアウトしておく)
>
>原因がわからんときのデバッグ作業ってのは大変やけど、地道にやることが
>結局一番早道やと思います。

まったく、その通りだと思います。
このアドバイスを実行してみてください。
#時間もないし、あと一歩で完成!なので、
#イベント探しを探求されるお気持ちもたいへんよく分かります

予想は、あくまで、予想ですので、はずしていたら、すみません。

【8291】Re:bykinさんに一票
質問  初心者  - 03/10/7(火) 15:05 -

引用なし
パスワード
   ▼bykinさん、しのしのさん:

ありがとうございます。
お陰様で違う視点に着目することができました。
ご指摘頂いた点から検証してみました。

結果、思いもしなかった原因が判明し、新たに頭を悩ませる事になりました。
原因は「コピー元のファイル」のバージョン違いでした。
開発は'97で行っているのですが、サンプルで貰った元ファイルが2000で
作成されたものだったようです。
今後、これを事前に判別するのが課題となります。


>たとえば、"データ操作"中に、開いているブックのオブジェクトを取得しているのに、
>きちんと、参照解放せぬままCLOSEしようとしていませんか?

変種処理は処理毎にFunctionを作成しているのですが、
解放ロジックは必要なのでしょうか?


>#ブックのシートを参照設定しておいて、その解放がきちんとされていない。

この参照設定の解放というのは、何をすれば良いのでしょうか。


以下、検証詳細です。

コピー元のファイルにはマクロ(コマンドボタン/処理は軽いもの)や
セル式が埋め込まれたものですが、サイズは240kb程度です。
シンプルにOpen→SaveAs→Closeとした場合にも処理ができなかったので、
コピー元ファイルを適当な新規ブックで実行してみたところ、処理が成功しました。

次に、問題のファイルを使用して、Saveの直前に
Application.DisplayAlerts = True
と記述し、システムのメッセージを表示させたところ、
「このエクセルは最新版のExcelで作成されました」のメッセージが・・・。
このメッセージに対して、手動で「はい」を押下すれば正常に保存され、
「いいえ」を押下するとCloseされずに開いたままです。
処理の最初に
Application.DisplayAlerts = False
を記述したので、「いいえ」を押下したのと同じ状態で処理が中断されたようです。

それから、以前Bykinさんに教えていただいたSleepはWaitよりも効果を得ました。
処理速度が落ちてしまうので最善の策とは言えませんが、各ファイル毎に
Sleep 5000(5秒)
とすれば、100ファイルの処理も可能となりました。
(3000以下では強制終了となりました)

ありがとうございました。

【8292】FileFormatですか
回答  しのしの  - 03/10/7(火) 16:03 -

引用なし
パスワード
   >結果、思いもしなかった原因が判明し、新たに頭を悩ませる事になりました。
>原因は「コピー元のファイル」のバージョン違いでした。
>開発は'97で行っているのですが、サンプルで貰った元ファイルが2000で
>作成されたものだったようです。
>今後、これを事前に判別するのが課題となります。

dim bkA as Excel.Workbook
dim strC as string

If bkA.FileFormat = xlExcel9795 Then
  bkA.SaveAs Filename:=strC, fileFormat:=xlExcel9795
ElseIf 

って感じで保存してみれば、どうでしょう?(97で保存する場合)
詳しくは、SaveAsメソッドのヘルプをご覧ください。


>>たとえば、"データ操作"中に、開いているブックのオブジェクトを取得しているのに、
>>きちんと、参照解放せぬままCLOSEしようとしていませんか?
>
>変種処理は処理毎にFunctionを作成しているのですが、
>解放ロジックは必要なのでしょうか?
Function内だけで使用する変数なら、特に参照解放する必要もないのですが、
クラスや、モジュールの内部変数の場合は、必要です(と思います)


>>#ブックのシートを参照設定しておいて、その解放がきちんとされていない。
>
>この参照設定の解放というのは、何をすれば良いのでしょうか。

set Sh =Nothing
だけでOKです。

>以下、検証詳細です。
>
>コピー元のファイルにはマクロ(コマンドボタン/処理は軽いもの)や
>セル式が埋め込まれたものですが、サイズは240kb程度です。
>シンプルにOpen→SaveAs→Closeとした場合にも処理ができなかったので、
>コピー元ファイルを適当な新規ブックで実行してみたところ、処理が成功しました。
>
>次に、問題のファイルを使用して、Saveの直前に
>Application.DisplayAlerts = True
>と記述し、システムのメッセージを表示させたところ、
>「このエクセルは最新版のExcelで作成されました」のメッセージが・・・。
>このメッセージに対して、手動で「はい」を押下すれば正常に保存され、
>「いいえ」を押下するとCloseされずに開いたままです。
>処理の最初に
>Application.DisplayAlerts = False
>を記述したので、「いいえ」を押下したのと同じ状態で処理が中断されたようです。

まさに、fileFormatによるもののようですね。
ご確認ください。
これで解決したらいいですね。

【8293】サンプル変でしたね。すいません。
回答  しのしの  - 03/10/7(火) 16:07 -

引用なし
パスワード
   さっきのサンプル変でしたね。
すいません。
でも、初心者さんなら、ヘルプをご覧になれば、
思いとおりに出来るだけのお力があると推測します。
がんばってください。

【8300】Re:サンプル変でしたね。すいません。
お礼  初心者  - 03/10/7(火) 19:09 -

引用なし
パスワード
   ▼しのしの さん:

ありがとうございます。
お陰様でもう一息!というところに辿り着いた気がします。
これから調べて試してみます。

12858 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free