none
AccessVBAで大量のExcelのセルの値を取得する RRS feed

  • 質問

  • 質問させていただきます。
    AccessVBAでExcelのセルの値を一つずつテーブルに取り込むプログラムを利用しています。
    10万レコードの処理でAccessXPで1時間ほどの処理だったものがAccess2016で動かすとで2時間以上処理に時間がかかっており短くできないか調べております。

    Excelの基礎データは複数枚あり、繰り返し文で対象のエクセルシートをすべて参照して、Accessのテーブルに書き込みます。
    VBAの処理は下記のようになっております。
    ■Excelシート
       |販売先A|販売店B|販売店C|販売店D
    品名A|  3,000|  3,200|  2,150|  1,200
    品名B|  3,100|  3,100|  2,100|  1,300
    品名C|  2,900|  3,150|  2,200|  1,100
    品名D|  2,950|  3,100|  2,200|  1,200
    品名E|  3,000|  3,200|  2,100|  1,300
    品名F|  3,100|  3,250|  2,150|  1,200

    ■Accessテーブル
    販売店  |品名  |価格|
    ――――+―――+――+
    販売店A |品名A |3000|
    販売店A |品名B |3100|
    販売店A |品名C |2900|
    販売店A |品名D |2950|
    販売店A |品名E |3000|
    販売店A |品名F |3100|
    販売店B |品名A |3200|
    販売店B |品名B |3100|
    販売店B |品名C |3150|
    :    :   :

    ■VBA エクセルシートの値をテーブルに書き込む
    Do until oApps.cells(2,yoko) '販売店の終わりまで
    Do until oApps.cells(tate,2) '品名の終わりまで
    DB_1![販売店]=oApp.cells(1,yoko)
    DB_1![品名]=oApp.cells(tate,2)
    DB_1![価格]=oApp.cells(tate,3)
    tate = tate + 1
    Loop
    yoko = yoko + 1
    Loop





    • 編集済み optivue 2019年8月4日 21:14
    2019年8月4日 13:14

回答

  • 元の投稿が、MoveNext も Update もしていないコードなので違和感がありますが…。

    Access VBA で Excel データを Access にインポートするのではなく、
    Excel VBA で Excel データを Access にエクスポートしてみてはいかがでしょう。

    Access 側から処理すると、Excel の各セルへアクセスするたびに「プロセス間通信」を行うことになるので、パフォーマンスが落ちます。Excel 側で自身のセルへアクセスするのなら、プロセスをまたぐことがないので速度面で有利になるかと。

    もしも Access 側から処理することを期待するのなら、ループ回数を減らすことで、アプリ間の通信回数をできるだけ減らすようにします。

    Dim rng As Excel.Range
    Set rng = oApps.Workbooks(1).Worksheets(1).Range("A1:D300")
    Dim vnt As Variant
    vnt = rng.Value

    上記の場合、変数 vnt には vnt(1, 1)~vnt(300, 4) の範囲の二次元配列として取得されます。
    ループ処理を用いず、複数の範囲のデータを一回の通信で取得していることになるわけです。

    二次元配列として取得した vnt 変数をループ処理する分には、Excel とのプロセス間通信が発生しないので、Access 側のみの処理となり、パフォーマンスの低下が抑えられるでしょう。ただし指定するセル範囲があまり巨大になると、メモリ確保の時間がかかりすぎるので、場合によっては Range 範囲を適当なサイズに区切って処理するようにします。

    あるいは別案として、Access に対して外部データ取り込みにより、Excel シートを Access 上にインポートしてしまい、それを Recordset として取り扱うという方法もあります。セルのマージが多いシートなどだと処理しにくいので、Excel 側での作りにも左右されてしまいますが。

    • 回答としてマーク optivue 2019年8月5日 12:49
    2019年8月4日 21:22

すべての返信

  • Access を使える環境がないし Access や Access VBA に関する
    知識もないですが、Access の DB って Excel のセルみたいに
    範囲 (Variant 配列) で操作できないのでしょうか。

    Sub test()
        ' セル D1:F10 の範囲の値をセル A1:C10 にコピーする
        Range("a1:c10").Value = Range("d1:f10").Value
    End Sub
    

    VBA でオブジェクトや Variant 型の配列をループする場合、可能なら
    For Each ループを使った方がいいです。
    ループカウンタなどを操作する必要があるときは For Each ループが
    使えないのでそのときは通常、For ループを使いますが上記で
    Do Until ループを使っている理由は何でしょうか。

    2019年8月4日 15:35
  • 試していませんが、Variant型の配列を作ることは可能だと思います。
    ループする数はファイルによって変動するのでfor文を使うべきという事かと思いますが、
    for eachやfor文のほうが速度が早くなるのでしょうか。

    Do until型でループさせているのは詳細は不明ですが、
    既存のプログラムが使用されていたため流用しているためです。

    for文で作り直してみました。

    ■VBA エクセルシートの値をテーブルに書き込む
     for yoko = 1 to yokomax
    for tate = 1 to tatemax
    DB_1![販売店]=oApp.cells(1,yoko)
    DB_1![品名]=oApp.cells(tate,2)
    DB_1![価格]=oApp.cells(tate,3)
    next tate
     next yoko
     Loop

    • 編集済み optivue 2019年8月4日 21:15
    2019年8月4日 20:56
  • 元の投稿が、MoveNext も Update もしていないコードなので違和感がありますが…。

    Access VBA で Excel データを Access にインポートするのではなく、
    Excel VBA で Excel データを Access にエクスポートしてみてはいかがでしょう。

    Access 側から処理すると、Excel の各セルへアクセスするたびに「プロセス間通信」を行うことになるので、パフォーマンスが落ちます。Excel 側で自身のセルへアクセスするのなら、プロセスをまたぐことがないので速度面で有利になるかと。

    もしも Access 側から処理することを期待するのなら、ループ回数を減らすことで、アプリ間の通信回数をできるだけ減らすようにします。

    Dim rng As Excel.Range
    Set rng = oApps.Workbooks(1).Worksheets(1).Range("A1:D300")
    Dim vnt As Variant
    vnt = rng.Value

    上記の場合、変数 vnt には vnt(1, 1)~vnt(300, 4) の範囲の二次元配列として取得されます。
    ループ処理を用いず、複数の範囲のデータを一回の通信で取得していることになるわけです。

    二次元配列として取得した vnt 変数をループ処理する分には、Excel とのプロセス間通信が発生しないので、Access 側のみの処理となり、パフォーマンスの低下が抑えられるでしょう。ただし指定するセル範囲があまり巨大になると、メモリ確保の時間がかかりすぎるので、場合によっては Range 範囲を適当なサイズに区切って処理するようにします。

    あるいは別案として、Access に対して外部データ取り込みにより、Excel シートを Access 上にインポートしてしまい、それを Recordset として取り扱うという方法もあります。セルのマージが多いシートなどだと処理しにくいので、Excel 側での作りにも左右されてしまいますが。

    • 回答としてマーク optivue 2019年8月5日 12:49
    2019年8月4日 21:22
  • おそくなりました。updateやmovenextがない。
    ご指摘ありがとうございます。お見苦しいコード失礼しました。

    結論ですが、アドバイスいただいたExcelの処理はExcel地震で処理を行うとよいとのアドバイスから、
    ExcelのデータをExcelVBAで加工してCSVにして出力→AceessにCSVファイルをインポート
    で劇的に速度改善ができました。
    配列については完全には理解が及んでいないのですが、
    AccessからExcelへの処理を指示するとレスポンスが低下するため、
    AccessからExcelのセルに対するアクセスを極力減らす事が速度改善になること理解しました。

    その後の処理でまた問題が発生していますが、今回の事象とは関係がない話なので本件は解決済みとさせていただきます。

    2019年8月5日 12:49