上書き保存の速度について・・・

エクセル2003のファイル（1.5MB程度）を上書き保存すると
結構時間がかかってしまいます。（30～40秒程度）

・ファイル内のシート同士はリンクしています（8つのシートがリンク）
・外部ファイルとのリンクは1つだけ
・関数が入っているセルの数は200個程度

上記の内容で動作が遅くなっている原因は何でしょうか？
また、解決策があればお願いします。
できれば上書き保存の時間を20秒以下にしたいのですが・・・

No.4 回答者： Wendy02 回答日時： 2008/05/06 14:00

こんにちは。

まず、

>上記の内容で動作が遅くなっている原因は何でしょうか？

　.Range("B1:AL17") もし、ここに数式があれば、数式を貼り付けた時点で、再計算が始まりますね。

元のコードは書き直すと、このようにはなりますが、数式があれば、ほとんど変わらないです。
----------------
　Dim l As Long
　　　l = Sheets("Sheet1").Range("E1").Value
　　　l = (l + 1) * 17
　　Application.ScreenUpdating = False
　　With Sheets("総合（乖離）")
　　　　.Range("B1:AL17").Copy .Range("B18", .Cells(l, 38))
　　　　 'この時点で再計算が始まる
　　　　.Range("B18", .Cells(l, 38)).Value = .Range("B18", .Cells(l, 38)).Value
　　End With
　　Application.ScreenUpdating = True
　　Workbooks("入力.xls").Close SaveChanges:=True
'-----------------

No.3 回答者： fumufumu_2006 回答日時： 2008/05/05 20:37

>>Workbooks("入力.xls").Close SaveChanges:=True

>>をコメントにして、実行しない場合の時間を比べてください。
>⇒1秒もかからず動作しました。
>>入力.xlsを保存する場合、ANo.1で行った作業を「入力.xls」に対しても行って、時間を比較してみてください。
>⇒やはり30秒程度かかりました。
これはやはり、「入力.xls」の上書き保存が重いという結論になると思います。
とりあえず、「入力.xls」を単体で開き（または問題のブックと同時に開き）、「入力.xls」を手動で上書き保存する場合の時間を調べてみてはどうでしょうか？
それが30秒程度かかるなら、やはり原因は「入力.xls」です。
それが30秒程度かからないなら・・・困ったことになります（原因不明に戻ってしまう）。

以下は「入力.xls」が重い場合の話です。
ANo.1で書いている
>[ツール][オプション]の[計算方法]タブで
>[計算方法]で
>手動
>[保存前に再計算]のチェックを外す
>[ブックオプション]
>[リモート参照を更新する]のチェックを外す
>[外部リンクの値を保存する]のチェックを外す
での速度変化で、どの部分の変更で保存時間が変わるかを調べる。
>Sub test()
>Dim ws As Worksheet
>For Each ws In ThisWorkbook.Worksheets
>MsgBox ws.Name & "=" & ws.DrawingObjects.Count
>ws.DrawingObjects.Delete
>Next
>End Sub
でオブジェクトを消してみて、保存時間が変わるかを調べる。
で、軽量化を図る。

ちなみに「入力.xls」にはWorkbook_BeforeCloseは設定されていないですよね。
Workbook_BeforeCloseモジュールがある場合は、その内容によって対処が変わると思います。
これは下の保存せずに終了させる方法でも実行されてしまうので、問題になる場合があります。

一番簡単な方法は、「入力.xls」を上書きせずに終了する事ではないでしょうか？
試しに、
Workbooks("入力.xls").Close SaveChanges:=True
を
Workbooks("入力.xls").Close SaveChanges:=False
にしてみてください。
短時間で終わるはずです。
「入力.xls」にも何かWorkbook_BeforeCloseのモジュールが設定されているばあいは、それを行わない場合の時間も調べてみてください。

もちろん「入力.xls」に変更されたデータがない（または保存しなくてもよい）場合です。
この時、保存が必要なデータは他のシート（データだけのシート(軽い)）に保存して、「入力.xls」には保存が必要なデータがない状態にして保存せずに終了します。
データとインターフェースを別ブックに分離する方法は、ある程度大きなシステムになった場合には、いろいろな利点がある場合があります。
とりあえず、「入力.xls」の軽量化（とWorkbook_BeforeCloseがあれば中止）と保存せずに終了を試してみてはどうでしょうか？

この回答への補足

>「入力.xls」を手動で上書き保存する場合の時間を調べてみてはどうでしょうか？
⇒30秒くらいかかりますね。

(1)[保存前に再計算]のチェックを外す
(2)[リモート参照を更新する]のチェックを外す
(3)[外部リンクの値を保存する]のチェックを外す
⇒(1)をやった場合35秒、(2)・(3)はどちらも30秒

オブジェクトは「入力.xls」の中には存在しないので、これ以上の軽量化は難しい。

>「入力.xls」にはWorkbook_BeforeCloseは設定されていないですよね。
⇒ThisWorkbookの中に、先ほどの
l = (Sheets("Sheet1").Range("E1") + 1) * 17
Sheets("総合（乖離）").Range("B1:AL17").Copy
Sheets("総合（乖離）").Range(Sheets("総合（乖離）").Cells(18, 2), Sheets("総合（乖離）").Cells(l, 38)).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("総合（乖離）").Range(Sheets("総合（乖離）").Cells(18, 2), Sheets("総合（乖離）").Cells(l, 38)).Copy
Sheets("総合（乖離）").Range(Sheets("総合（乖離）").Cells(18, 2), Sheets("総合（乖離）").Cells(l, 38)).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Workbooks("入力.xls").Close SaveChanges:=True
を記述しています。

>データとインターフェースを別ブックに分離する
⇒「入力.xls」は「判定一覧/xls」につながっていて、「判定一覧/xls」は
「入力.xls」に入力された得点データが集計されて一覧表示されるようになっています。
もし、「入力.xls」をデータとインターフェースに分けてしまうと、データを打ち込んだ後、
もしくは「判定一覧/xls」を開く前にインターフェース用のブックを開かなくてはいけなくなり
1つ作業工程が増えて面倒かなと思ったので、データとインターフェースをまとめました。
理想は、「得点を打ち込んだ後、一覧のブックを開けば値が反映されている」っていう感じなんですが
「入力.xls」をデータとインターフェースに分けてもできますか？

補足日時：2008/05/05 22:16

No.2 回答者： fumufumu_2006 回答日時： 2008/05/04 21:48

ANo.1です。

まず、
Workbooks("入力.xls").Close SaveChanges:=True
をコメントにして、実行しない場合の時間を比べてください。
また、入力.xlsを保存する場合、ANo.1で行った作業を「入力.xls」に対しても行って、時間を比較してみてください。

p.s.
l = (Sheets("Sheet1").Range("E1") + 1) * 17
Sheets("総合（乖離）").Range("B1:AL17").Copy
Sheets("総合（乖離）").Range(Sheets("総合（乖離）").Cells(18, 2), Sheets("総合（乖離）").Cells(l, 38)).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("総合（乖離）").Range(Sheets("総合（乖離）").Cells(18, 2), Sheets("総合（乖離）").Cells(l, 38)).Copy
Sheets("総合（乖離）").Range(Sheets("総合（乖離）").Cells(18, 2), Sheets("総合（乖離）").Cells(l, 38)).PasteSpecial Paste:=xlPasteValues
の部分は、lを変数にする必要がないか、lによってはエラーになる場合があるんではないでしょうか？
見やすくするために以下だとします。
Range("B1:AL17").Copy
Range(Cells(18, 2),Cells(l, 38)).PasteSpecial
この場合は、PasteSpecialの範囲が、copyした時点の行列数と同じでないとエラーになると思うので、変数にする意味がないと思います。
不定の場合は、
Cells(18, 2).PasteSpecial
か
Range("B18").PasteSpecial
ではないでしょうか？
それと、たぶんですが、
Sheets("総合（乖離）").Range("B1:AL17").Copy
Sheets("総合（乖離）").Range(Sheets("総合（乖離）").Cells(18, 2), Sheets("総合（乖離）").Cells(l, 38)).PasteSpecial Paste:=xlPasteValues
だけでいいんではないでしょうか?(途中のPaste:=xlAllの行と、次の.Copyの行はいらない？）

この回答への補足

返答ありがとうございます。早速やってみたところ、

>Workbooks("入力.xls").Close SaveChanges:=True
>をコメントにして、実行しない場合の時間を比べてください。
⇒1秒もかからず動作しました。

>入力.xlsを保存する場合、ANo.1で行った作業を「入力.xls」に対しても行って、時間を比較してみてください。
⇒やはり30秒程度かかりました。

p.s.
iを変数にしたのは、sheet1のE1のセルに登録人数が入力されていて、その人数分コピーするためです。
また、「途中のPaste:=xlAllの行と、次の.Copyの行」はどういうことかというと、
B1:AL17に数式が入っていて、それを人数分コピペするのが次の行で、そのまま保存してもいいのですが、
容量が大きくなるので、反映したデータを値のみの貼り付けを行い、保存しようと思い
「途中のPaste:=xlAllの行と、次の.Copyの行」を追加しました。

補足日時：2008/05/05 17:02

No.1 回答者： fumufumu_2006 回答日時： 2008/05/03 13:43

まず、再計算などで遅いのかどうかを調べてみてはどうでしょうか?

[ツール][オプション]の[計算方法]タブで
[計算方法]で
手動
[保存前に再計算]のチェックを外す
[ブックオプション]
[リモート参照を更新する]のチェックを外す
[外部リンクの値を保存する]のチェックを外す
この状態で保存して速度の変化があるか比較してみてください。

結構大きい（1.5MB程度）ので、何かオブジェクト(シェープやグラフ)が入ってませんか？
とりあえず、下のプログラムでその手の物を全部消してみてください。（必要な物があってもそれも消して）
この状態で保存して、サイズと速度の変化があるか比較してみてください。
Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
MsgBox ws.Name & "=" & ws.DrawingObjects.Count
ws.DrawingObjects.Delete
Next
End Sub

各シートで[編集][ジャンプ]の[セル選択]ボタンを押して[選択オプション]の[数式]をチェック(内容はすべて選択)して[OK]を押して、余計な数式が無いか調べてください。

それでも速度の変化がないようなら・・・、他に何かあるでしょうか？

この回答への補足

全て試しましたが保存時間の変化ありませんでした。
頑張って数式を減らして、１ＭＢ位の容量にしました。
再計算の時間はかからなくなったのですが、上書き保存をすると
以前と同じ程度の時間がかかります。
「リンク」に時間がかかっているのではないかな？、と思うのですが
何か解決策はありますか？
ちなみに、ブックを閉じるときにこんなマクロが動作します。↓

Private Sub Workbook_BeforeClose(Cancel As Boolean)
l = (Sheets("Sheet1").Range("E1") + 1) * 17
Sheets("総合（乖離）").Range("B1:AL17").Copy
Sheets("総合（乖離）").Range(Sheets("総合（乖離）").Cells(18, 2), Sheets("総合（乖離）").Cells(l, 38)).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("総合（乖離）").Range(Sheets("総合（乖離）").Cells(18, 2), Sheets("総合（乖離）").Cells(l, 38)).Copy
Sheets("総合（乖離）").Range(Sheets("総合（乖離）").Cells(18, 2), Sheets("総合（乖離）").Cells(l, 38)).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Workbooks("入力.xls").Close SaveChanges:=True
End Sub

補足日時：2008/05/04 14:06