Excel/VBAでデータベースから読み込んで二次元配列に収める方法

ひさびさのExcel/VBAネタです。

データベースからWHERE文で条件指定して読み込んだデータを二次元配列に収めようと思ったら意外とハマったので解決策を書き留めておきます。

そんなの簡単じゃんと思うかもしれませんが、以下の条件を満たす必要があります。

• 読み込んでくるレコード数は不明
• なので配列宣言時に要素数は指定できず。

そんなのReDimすれば簡単じゃんと思うなかれ。（とか言う僕も最近まで知らなかったのですが）２次元配列の第一要素数はReDImで変えられないのです。

Dim a() As Variant

ReDim Preserve a(0, 0)
ReDim Preserve a(0, 1)
ReDim Preserve a(0, 2)
ReDim Preserve a(1, 3) //ここでエラー！
 

 

なので、レコード１行目まではデータが格納できるものの、２行目に移った時点で実行エラーとなります。

その解決策として、一挙に２次元配列化してくれるGetRowsプロパティを使います。

以下はAccessの例です。

Dim buf() As Variant
Dim SQL_QUERY As String

SQL_QUERY="SELECT * FROM Table1 WHERE Group='A001'"

Set myCon = CreateObject("ADODB.Connection")
myCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\...\database.accdb"

With CreateObject("ADODB.Recordset")
  .ActiveConnection = myCon
  .CursorType = adOpenForwardOnly
  .Source = SQL_QUERY
  .Open

  //ここ！
  If Not .EOF Then
    buf = .GetRows
  End If
  .Close
End With

myCon.Close
Set myCon = Nothing
 

 

これで変数bufには２次元配列となって読み込んだデータが格納されます。

個人的に気持ち悪いのが、取得した配列がbuf(フィールド要素, レコード要素)と、レコードが第二要素になっているので、それを力技で逆転させています。

Dim buf2() As Variant
ReDim buf2(UBound(buf, 2), UBound(buf, 1))

For i = 0 To UBound(buf, 1)
  For j = 0 To UBound(buf, 2)
    buf2(j, i) = buf(i, j)
  Next
Next
 

 

いままでこんな風にループでデータ取得していました。むだに処理速度浪費していたかも。

Do While Not .EOF
  d = .Fields(FieldName).Value
  .MoveNext
Loop

 

 

 - Excel, VBA