Office■ クエリ更新でメモリ不足
Excelのクエリ更新で発生したメモリ不足の対応方法です。
クエリでは外部のテキストファイルを取込む処理で、表はこんな感じにしています。
※ 諸般の事情で本物の表が提示できなかったので、表はサンプルです。
更新はマクロを使用して実行していました。
'Sheet2を選択
Sheet2.Range("B3").Select
'クエリを更新する。
Selection.QueryTable.Refresh BackgroundQuery:=False
すると、クエリを更新する「Selection.QueryTable.Refresh BackgroundQuery:=False」でメモリ不足が発生します。
実行時エラー '7':
メモリが不足しています。
．．．こういうエラーが最も嫌らしいですよ。
■ Excel2000だとこうなる
今回のメモリ不足は、Excel2000で作ったものをExcel2010で動くように修正していた時に発生しました。
そこで、修正中のファイルをExcel2000形式（Excel 97-2003 ブック(*.xls)）に保存し直して実行したところ、やはり「Selection.QueryTable.Refresh BackgroundQuery:=False」で異常終了。
実行時エラー '-2147024882(8007000e)':
オートフィルタがオンになっている状態で列の挿入や削除はできません。
オートフィルタコマンドをオフにして、もう一度行って下さい。
あぁ、そういう事ですか．．．
■ 原因と対応
これで想定できるのは、Excelのどこかのバージョンからオートフィルタの設定中でも列の追加が出来るようになったってことで、その動作には大量のメモリを使用するってことです。
ということで、マクロを修正してオートフィルタを解除してからクエリ更新を実行することにしました。
'Sheet2を選択
Sheet2.Range("B3").Select
'オートフィルタが設定されている場合は解除する
If ActiveSheet.AutoFilterMode Then
Selection.AutoFilter
End If
'クエリを更新する。
Selection.QueryTable.Refresh BackgroundQuery:=False
AutoFilterModeプロパティで、シートにオートフィルタが設定されているかを確認し、設定されている場合は解除するようにします。
これで動くようになったのですが、よく考えると不味かったです。
■ テキストファイルのレイアウト変更
Excel2000で実行した時のエラーで、列の挿入云々っていうくだりが気になります。
それで取込むテキストファイルを調べたのですが、レイアウト変更が行われていて、５つほど列が追加されていたようです。
あー、ってことは、まずクエリの定義を直さないと不味いですよね。
ってことで、テキストファイルのレイアウト変更が根本的な原因だったようです。
■ 後書き
正直、とても手こずりました。
そもそも、このExcelは現場の人が自分で作ったもので、いわゆるEUCってヤツでした。んで、その作った人が居なくなってメンテナンスが出来なくなったのです。しかも面倒な作りをしていて、単純にExcel2010に変換しても動かない。
ということで、私の部署（システム部）に泣き付いてきました。
最初は他のメンバーに依頼していたようですが、みんな手におえないってことで逃げ出して、最後に私に回ってきたのです。
んで、中身を見ると想定通り無茶苦茶な作り。理解不能なインデント、意味不明なコメント、目的の不明な処理、グローバル変数の連発、その他もろもろ．．．。こりゃみんな逃げるよ（俺も逃げたい）。
記事にしたクエリにしたところで、あちこちからデータを持ってきているのですが、それを何処からどの様に入手するかも不明って感じで、さてどうしたものやらって状態でした。
さらに、依頼内容（メール）の追記には機能追加の要望が．．．
スポンサードリンク
0
0
