QA@IT
この質問・回答は、@ITの旧掲示板からインポートされたものです。

【VB6】エクセルでコピーのループを行うと処理速度が遅くなる

VB6の質問です。
開発環境は
OS:WindowsXP SP2
Microsoft Office2000
言語:VB6 SP6
です。

VB6で作成したRecordsetのデータをループで
エクセルに貼り付けるというプログラムを作成したのですが、
エクセルのRangeのコピーの処理がループを重ねる度に遅くなっていきます。

ソースは下記のとおりです。
------------------------------------------------------------------------------
Dim rs As ADODB.Recordset
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlCopyRange As Excel.Range
Dim lngLine As Long 'ページにおけるライン(レコード)数

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("c:\ExcelFile.xls")
Set xlSheet = xlBook.Worksheets(1)

xlApp.ScreenUpdating = False
xlApp.DisplayAlerts = False
xlApp.EnableAnimations = False
xlApp.EnableAutoComplete = False
xlApp.EnableEvents = False

*************************************
ここにRecordset取得処理が入ります。
*************************************

With xlSheet
    .EnableAutoFilter = False
    .EnableCalculation = False
    .EnableOutlining = False
    .EnablePivotTable = False

    Set xlCopyRange = .Rows("1:1"))
    lngline = 1
    Do Until rs.EOF

        lngLine = lngLine + 1

        'セルの形式をコピー
        xlCopyRange.Copy .Rows(CStr(lngline) & ":" & CStr(lngline))

        rs.MoveNext

    Loop
End With

Set xlSheet = Nothing
Set xlBook = Nothing

xlApp.Quit
Set xlApp = Nothing

------------------------------------------------------------------------------
実際にはセルの形式をコピーした後にRecordsetのデータを貼り付けるのですが、
現在Copyの処理だけでも処理時間が遅くなります。
Timer関数で計ったところ実行直後は0.03秒程度だったコピーの処理時間が
100回くらいループが回ったところで1秒ほどまで遅くなります。
最終的には2000件くらいのデータを扱いたいのでこれでは実用に耐えません。

プログラムの書き方が悪いのか、それとも何か設定があるのかも分からない状態で
ネットで検索してもエクセルのコピーのプログラムはあるのですが、
処理時間まで言及しているHPが見つかりませんでした。

よろしくお願いします。

質問者:SIC

回答

最終的には2000件くらいのデータを扱いたいのでこれでは実用に耐えません。

一つの方法として、2000という数字がわかっているなら、
3000行の書式付き雛形を用意しておき、最後に不要な部分を範囲指定して行削除するではどうでしょうか。
また値の設定もVariant2次元配列で一気にまたは数百行単位で複数回で設定するのも効果があります。
Dim varValue(1 To 3, 1 To 10) As Variant
XXXXX.Range("A1:J3").Value = varValue

投稿者:まどか

編集 履歴 (0)

ADODB.RecordsetにRecordCountというプロパティがあるみたいですが、
これで先に範囲書式指定しておけませんか?

投稿者:mio

編集 履歴 (0)

>まどかさん、mioさん
返答いただきありがとうございます。

2次元配列を使ったりレコードカウントと取得したりする方法は私も考えました。
実は最初の投稿のソースは質問の内容を理解してもらいやすくするために簡素化したもので、実際はエクセルでテンプレート的なファイルを作成してそこに貼り付けて印刷等を行う予定です。
2次元配列やレコードカウントを用いた場合改ページの処理がうまく行かず断念しました。
またデータ数2000件というのは今現在手元にあるテストデータの話でして、実際プログラムの納品先の使い方によっては10000件以上出力する場合や500件程度しか出力しない場合もあります。
説明不足で申し訳ありませんでした。

出来れば、エクセルのCopyが遅くなる原因を解消したいのですがうまい方法はないでしょうか?

投稿者:SIC

編集 履歴 (0)

SICさんの書き込み (2006-08-24 09:33) より:

実際はエクセルでテンプレート的なファイルを作成してそこに貼り付けて印刷等を行う予定です。

ということは、値のコピーだけで良いということでしょうか?
やはり、Range.Value で値を一気に貼り付けるという方法しか思いつきません。

2次元配列やレコードカウントを用いた場合改ページの処理がうまく行かず断念しました。

これは、同時にやろうとして、そう感じてしまったのではないでしょうか?
後から改ページの設定はできないような仕様なのでしょうか?

# グルーピングなどの関係であれば、何とでもできますけども...

出来れば、エクセルのCopyが遅くなる原因を解消したいのですがうまい方法はないでしょうか?

え? テンプレートがあると聞いた後なので、
Copy メソッドを使う必要がないように思っていたのですが...

_________________C# と VB.NET の入門サイト
じゃんぬねっと日誌

投稿者:じゃんぬねっと

編集 履歴 (0)

SICさんの書き込み (2006-08-23 17:13) より:

VB6の質問です。

開発環境は

OS:WindowsXP SP2

Microsoft Office2000

言語:VB6 SP6

です。

直接の回答ではないのですが、DB の操作などは除いて、すべて Excel の VBA だけで書いたサンプルコードに直して、コピーの個所に関して、Excel の掲示板で質問されるのも手だと思います。
DB や VB が絡むよりも Excel の VBA 内で完結したほうが切り分けが楽になります。
また、結局は Excel というアプリケーションの仕様による部分が大きいので、情報量的には汎用の開発者向けの掲示板よりも Excel の掲示板のほうが情報の量が多いと思います。

私も、Excel 上の操作がなにかの拍子に妙に遅くなることがあって、原因が良く分からないことがありました。
ひとつの推測ですが、広い意味でのコピーなので Windows のクリップボードがなにか絡んでいるのかな?クリップボードをセキュリティー関連のソフトウェアが監視しているためかな?とも思うのですが良くは分かりません。とりあえず、Excel のバージョンは最新のサービスパックにして、セキュリティー関連のものは切ってみてはどうでしょうか。
また、ScreenUpdating などは設定されているようですが、Excel のウィンドウの大きさを故意に変えてみるなどしてはどうでしょうか。

--
unibon {B73D0144-CD2A-11DA-8E06-0050DA15BC86}

投稿者:unibon

編集 履歴 (0)

SICさんの書き込み (2006-08-24 09:33) より:

2次元配列やレコードカウントを用いた場合改ページの処理がうまく行かず断念しました。

コピーペーストでは、改ページ位置は狂いようがないと思いますが…。
それでもだめなら、やはり後から改ページ位置を修正するとか。

Excel VBAの高速化のポイントは、いかにロジックを作らずに組み込み関数を使うかと、いかに一回で大きな範囲を処理するか、だと思います。
プラスして、いかにワークシート側だけで処理を終えておくか、ですか。

投稿者:mio

編集 履歴 (0)

皆様、返答いただきありがとうございます。
またこちらの知識不足のため、お手数をおかけしまして申し訳ありません。

>じゃんぬねっとさん
テンプレートと言う言い方で誤解を与えてしまいすみません。
出力用のファイルに印刷する内容1ページ分のシートを用意しておき、
改ページの度にそれを貼り付けるという方法でやっていました。
なのでコピーの処理時間が問題となっています。

>unibonさん
セキュリティ関連ですか。そこまでは見ていませんでした。
急に遅くなったのでもしかしたらその可能性があるかも知れないので調べてみます。

>mioさん
改ページの位置ではなくて改ページのタイミングがうまく行きませんでした。
キーとなるフィールドの値が変わったらページの途中でも改ページをしたいので、
レコードカウントだけでは総ページ数が取得できなかったんです。
またキーとなるフィールドはユーザー側で変更できる仕様のため、
あらかじめ別のRecordsetで取得することも出来ませんでした。

まずはunibonさんが言われたセキュリティ絡みから調べてみて、
その上で出来る限りロジックを簡素化して対応してみようと思います。

投稿者:SIC

編集 履歴 (0)

VBのredimしなおすような感じ
同じ内容でもペーストするたびに扱うデータの容量が増えるので、徐々に遅くなっていくような感じ
自分で同じことをしたときに感じたことです

また、その時に聞いた話ですが、メモリリークを起こすからコピペはやめてくれといわれ、
テンプレートっぽい部分を範囲のコピペでなく、
値、書式を1セル毎に代入する形で作り直した覚えがあります

メモリリークの話は裏を取っていませんが、
クリップボードか何かの不安定な領域を使用するからリークしまくるだったと思います

投稿者:鶴屋さん

編集 履歴 (0)

SICさんの書き込み (2006-08-24 15:03) より:

テンプレートと言う言い方で誤解を与えてしまいすみません。

出力用のファイルに印刷する内容1ページ分のシートを用意しておき、

改ページの度にそれを貼り付けるという方法でやっていました。

なのでコピーの処理時間が問題となっています。

そうだとしても、Copy メソッドを使う必要はなく、Range.Formula などで設定してあげれば良いと思うのです。
コピー先_Range.Formula = コピー元_Range.Formula のようにです。

_________________C# と VB.NET の入門サイト
じゃんぬねっと日誌

投稿者:じゃんぬねっと

編集 履歴 (0)

SICさんの書き込み (2006-08-24 09:33) より:

2次元配列やレコードカウントを用いた場合改ページの処理がうまく行かず断念しました。

改ページが絡むなら当然めちゃくちゃ遅くなるでしょうな。

というか、Excel君に印刷をやらせるのは、不得意中の不得意なことなので、Excelを利用するのが本当にいいのか?ってことから考え直したほうがよいかと。

投稿者:ちゃっぴ

編集 履歴 (0)

うーん…。いまだに処理が良く分かってなかったりする。
総ページ数はこの場合関係ないように見えるんですが…。

まずはループで内容を設定しつつ、「そのページに何行できるか」をカウントしておいて、
その後カウントした分の行を一括して書式コピー、でいいような気がするんですが。
場合によっては、オートフィルメソッドも使えます。
ともかく、ループ中1行1行コピーを取ることは馬鹿らしいです。

投稿者:mio

編集 履歴 (0)

るぱんです。

当時は素直にループして.valueに値突っ込んでたなぁ・・・。

ページ数は、1ページに必ず●行とかって決め打ちじゃだめなんでしょうか?
まぁ、その●行は設定にはじき出しますが。汗

あとは・・・エクセルVBAにマクロ張ってそっちで制御とか・・・?
もう少し大きな枠組みでどんな処理かがわかれば
もう少し力に慣れそうな気はしますが・・・。
(開発環境は持っていますので)

投稿者:るぱん

編集 履歴 (0)

皆様、数々の返答ありがとうございます。
コピーの仕方をRangeオブジェクトを定義してCopyを実行する方法から
直接Range指定でCopyする様にしたところ
最初の方法だと1000件30分かかった処理時間が10分くらいになりました。
もちろんコピーの処理時間が遅くなる現象はそのままですが・・・。

実用できるかどうかの判断は自分では出来ないので、
この状態で上司の判断をあおいでみるつもりです。

>鶴屋さん
セル単位だとテンプレートの罫線とかが上手く行かないうえ、
Borderとか色々代入してると結局同じくらい時間がかかってしまいました・・・。

>じゃんぬねっとさん
上にも書きましたが、テンプレートの罫線や書式をうまく渡す設定ができなくて
結局Copyに頼るしかないという状況になりました。
不勉強で申し訳ありません。

>ちゃっぴさん
私もそう思うのですが、上が決めたことなのでどうしようもありません。
なんでも帳票のデザインが変わった時にActiveReportだとプログラムが作り直しだが、
Excelだったらテンプレートの修正ですむとの判断らしいです。

>mioさん
すみません、私の方の勘違いでした。
結局1ページ分の一括コピーという方法を使っています。

>るぱんさん
1ページに何行というのは決まっていますので、
1ページ分のシートを作ってそれをコピーするようにしました。
エクセルVBAは印刷する帳票が30種類ほどあるため、工数的にNGと言われました。
私自身エクセルのマクロの作成とかはあまりしたことはないんです。

皆様、本当にありがとうございました。
なるべく自分のスキルをあげてこういう基本的な質問は
しなくてもすむようにしたいと思います。

投稿者:SIC

編集 履歴 (0)

るぱんです。

よくやる手としては、テンプレートファイル(30個の帳票)のテンプレートを作って
そこに対して30種類分の分岐を設けてやる方法なのですが、
それがNGであれば、VBだけでやるしかないですね。。。

まぁ、工数的にできたとしても、お客は理解できなくなりますけどね。
そういう意味で、要件定義で工数追加になりそう(笑)

それは、さておき本題。
今回みたいに、
・ページ制御
・データコピー

のような複数機能がある場合は一回切り分けてコメントだけで
コーディングすると頭の中が整理されやすいです〜。

まぁ、当初は帳票印刷・・・と言う感じで一くくりだったんでしょうけど。汗

コメントだけでコーディングの感触がつかめると、
設計力が抜群に上がったりします。

なんたって、コメントだけ修正すれば、最後にまとめて突貫工事で
ソースコードかけますから。

今回の例をとれば、



ver1
public Sub Test()
    '帳票印刷
End Sub



ver2
public Sub Test()
    '帳票印刷
        '1.データ取得
        '2.エクセル出力
        '3.ユーザー操作によるプリンター出力(以後ユーザー操作なので無視)

End Sub



ver3
public Sub Test()
    '帳票印刷
        '1.データ取得
            '1−1.ADO接続
            '1−2.結果取得
            '1−3.取得結果の整形

        '2.エクセル出力
            '2−1.テンプレート取得
            '2−2.データ書き込み
            '2−3.ページ制御

End Sub

こんな感じでいかがでしょ?

これやると、
大見出しの単位で関数作れるのでソースが整理できます。
頭を整理しながらやると、引数と戻り値もチェックできたりして、
保守性がさらにあがると思います。

めんどくさかったら分ければいいんです。
何より、混乱することがなくなりますんで。(笑)

投稿者:るぱん

編集 履歴 (0)

何か全然流れを理解していない人がいるぞ。

投稿者:ぶさいくろう

編集 履歴 (0)

全体像がいまだ見えないので、全然違うことしているのかも知れませんが。
dataに抽出結果が展開されていて、mがその件数だとすると、

template.Rows(1).Copy
data.Select
data.Rows("1:" & m).Select
Selection.PasteSpecial Paste:=xlPasteFormats
data.Cells(1, 1).Select

' とりあえずA列B列をキーとしてbreakする
For i = 2 To m
    If data.Cells(i, 1).Value <> data.Cells(i - 1, 1).Value _
    Or data.Cells(i, 2).Value <> data.Cells(i - 1, 2).Value Then
        data.HPageBreaks.Add before:=Rows(i)
    End If
Next

こんな感じのものではない…んでしょうか。
1000件あっても一瞬ですが。

投稿者:mio

編集 履歴 (0)

こんなコンポーネントもありますよ。高速って書いてあるし、体験版もダウンロードできるみたいですから、試してみてはいかがでしょう?

投稿者:ぼのぼの

編集 履歴 (0)

皆様、返信ありがとうございます。

>るばんさん
助言ありがとうございます。
ソースが面倒くさいとかそういうことはないんです。
ただ単に出来る限り簡単なソースコードにしても
処理に時間がかかるのが問題なだけであって・・・。

>mioさん
説明不足で申し訳ありません。
帳票にはヘッダーとフッターが存在していまして、
その部分にもデータの貼り付けがあるため
エクセルのヘッダー、フッター機能は使えないと判断しました。

そのため改ページの必要が生じたら
1ページ分の書式(ヘッダー、フッターを含めて)のコピーという方法をとっています。

>ぼのぼのさん
そういうコンポーネントもあるんですね。
ただコンポーネントの購入は許可が下りないと思います。

投稿者:SIC

編集 履歴 (0)

SICさんの書き込み (2006-08-26 14:09) より:

>ぼのぼのさん

そういうコンポーネントもあるんですね。

ただコンポーネントの購入は許可が下りないと思います。

こうしてSICさんが調査している作業時間に対してもお金は発生しているわけですから、トータルの工数を考えるとコンポーネントを導入することで却って安く済む可能性もあるわけで。

別にこのコンポーネントが売れることで私が得するわけではないので無理にとは言いませんが、頭っからあきらめてしまうのは勿体ないですよ。

投稿者:ぼのぼの

編集 履歴 (0)

templateのシートをそのまま使うマクロを考えてみたり。
ヘッダとフッタは入ってないけど、追加することは難しくはないはず。
あんまり検証してないので境界条件が間違ってる可能性はあります。

result.Select
For i = 2 To m
    If data.Cells(i, 1).Value <> data.Cells(i - 1, 1).Value _
    Or data.Cells(i, 2).Value <> data.Cells(i - 1, 2).Value Then
        splitPage data, result, start, i, page
        start = i
    End If
Next
splitPage data, result, start, i, page

template.Rows("1:" & PAGE_SIZE).Copy
result.Rows(1 & ":" & (page * PAGE_SIZE - 1)).PasteSpecial Paste:=xlFormats

result.Cells(1, 1).Select

Private Sub splitPage(ByVal data As Worksheet, ByVal result As Worksheet, ByVal start As Long, ByVal i As Long, ByRef page As Long)
Dim j As Long
Dim m As Long

m = CLng((i - start) / PAGE_SIZE) - 1
For j = 0 To m
    data.Rows(start & ":" & (start + PAGE_SIZE)).Copy
    result.Cells(page * PAGE_SIZE + 1, 1).PasteSpecial Paste:=xlValues
    page = page + 1
    start = start + PAGE_SIZE
Next
If start < i Then
    data.Rows(start & ":" & (i - 1)).Copy
    result.Cells(page * PAGE_SIZE + 1, 1).PasteSpecial Paste:=xlValues
    page = page + 1
End If

End Sub

投稿者:mio

編集 履歴 (0)

>ぼのぼのさん
以前工数を減らすために
紹介いただいたコンポーネントと価格が同じくらいの帳票ソフトの購入を
提案しましたが、あっさり却下されましたので購入は無理かとコメントしました。
今回のコンポーネントの件も普通にスルーされましたしね
>mioさん
わざわざソースまで書いていただき本当にありがとうございました。
参考にさせていただきたいと思いますm(_ _)M

投稿者:SIC

編集 履歴 (0)
ウォッチ

この質問への回答やコメントをメールでお知らせします。