非IT企業に勤める中年サラリーマンのIT日記

非IT企業でしかもITとは全く関係ない部署にいる中年エンジニア。唯一の趣味がプログラミングという”自称”プログラマー。

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

   

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

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

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

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

そんなのReDimすれば簡単じゃんと思うなかれ。(とか言う僕も最近まで知らなかったのですが)2次元配列の第一要素数は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) //ここでエラー!
 

 

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

その解決策として、一挙に2次元配列化してくれる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には2次元配列となって読み込んだデータが格納されます。

個人的に気持ち悪いのが、取得した配列が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