Excelファイルデータベースの書込みスピードは？(Excelデータベースその6)

ExcelVBA-Excelファイルをデータベースとして使う-その６です。前回の記事はこちらになります。
hirocom777.hatenadiary.org

書き込みスピードを確認する

さて、今回はデータベース接続の書き込みスピードについて確認してみたいと思います。
読込ではデータベース接続のほうが速かったのですが、書き込みではどうでしょう？
同じデータにアクセスしてスピードを比べてみようと思います。

今回も、以下のサイトからデータをいただいて使わせてもらおうと思います。
jusyo.jp

こちらの静岡県データ(CSVファイル-約3700件)をシートにコピーして使わせていただきます。ありがとうございます！！
今回は、シート上のデータをフィールドだけ入力した『Sizuoka.xlsx』のシート『sizuoka』にコピーする時間を比べたいと思います。

ADOでやってみる

それではやってみましょう。書き込みには以下で紹介したAddRecordを使えばいいですね。
hirocom777.hatenadiary.org
データをコピーしたシートに以下のプロシージャをコピーしてください。

Option Explicit

Private Sub AddData_ADO()
Dim vrnField() As Variant
Dim vrnData() As Variant
Dim i As Long
  Debug.Print Now
  vrnField() = Me.Range("$A$1:$V$1")
  vrnField() = WorksheetFunction.Transpose(WorksheetFunction.Transpose(vrnField))

  i = 2
  Do While Me.Cells(i, 1) <> ""
    vrnData() = Range(Me.Cells(i, 1), Me.Cells(i, 22))
    vrnData() = WorksheetFunction.Transpose(WorksheetFunction.Transpose(vrnData))
    AddRecord ThisWorkbook.Path & "\Sizuoka.xlsx", "sizuoka", vrnField(), vrnData()
    i = i + 1
  Loop
  Debug.Print Now
End Sub

『 WorksheetFunction.Transpose(WorksheetFunction.Transpose(vrnData))』ですが、これは2次元配列を1次元配列に直す裏ワザです。
レンジ範囲を配列にコピーすると1行でも2次元配列になっちゃうんです。１レコードのデータを表すので１次元配列じゃないといけません。
結果は？……約12分！！え？すごい遅い！！秒じゃなくて分！！どうしてこうなった？？

VBAでは？

VBAではどうなんでしょ。今度はシートに以下のプロシージャを配置して実行してみてください。

Option Explicit

Sub AddData_VBA()
Dim wbk As Workbook
Dim rngRange As Range
Dim i As Long

  Debug.Print Now
  Application.ScreenUpdating = False
  
  Set wbk = Workbooks.Open(FileName:=ThisWorkbook.Path & "\Sizuoka.xlsx")
  
  i = 2
  For i = 2 To Me.Range("A:A").Count
    If Me.Cells(i, 1) = "" Then Exit For
    Set rngRange = Range(Me.Cells(i, 1), Me.Cells(i, 22))
    rngRange.Copy wbk.Sheets("sizuoka").Range(rngRange.Address)
  Next
  wbk.Close SaveChanges:=True
  
  Application.ScreenUpdating = True
  Debug.Print Now
End Sub

結果は、……約50秒！！。て、言うかこれやってることは前回とほぼ一緒。データを別のファイルにコピーしているだけだからあまり変わらないんですよね。検索していない分こちらのほうがちょっとだけ早いかな。