ExcelにADO接続する
Excelシートに保存されたデータを、条件で検索して、結果を別のシートに表示したい。
検索条件の関係から、Excel関数ではちょっと辛い、めんどい、SQL使いたい。
ということで、VBAで自分自身にADO接続した。
シートをテーブルに見立てている状態。
Public Sub MyADOExcel()
Dim cn As Object
Dim rs As Object
Dim query As String
' レコードセットオープン時の引数、constしとく
Const adOpenKeyset = 1
Const adLockReadOnly = 1
' コネクション準備
Set cn = CreateObject("ADODB.Connection")
cn.Provider = "Microsoft.ACE.OLEDB.12.0"
' 1行目は項目名(HDR=YES)
cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
' レコードセット準備
Set rs = CreateObject("ADODB.Recordset")
' コネクションオープン、接続先は自分自身
cn.Open ThisWorkbook.FullName
' 普通にSQL文作る
' この場合、「DATA」という名前のシートの中を探そうとしてる
query = " SELECT 営業所コード,営業所名,(DATAシート1行目の項目名)" _
& " FROM [DATA$] " _
& " WHERE 受注残数 > 0 (AND 何か条件いろいろ)" _
& " ORDER BY 営業所コード,(何かいろいろ)"
' 作ったSQLで、レコードセット開く
rs.Open query, cn, adOpenKeyset, adLockReadOnly
'「結果」という名前のシートに結果をべったり貼ってる。
' この場合2行目2列目から貼ってる。
Worksheets("結果").Cells(2, 2).CopyFromRecordset rs
' 一応後片付け
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
いろいろはしょったが、「接続してデータ取る」の肝はこのぐらい。
問題：型が一致しないエラー
基本、調子よく動いていた。
が、まれに「-214746259 式で型が一致しません」というエラーが発生した。
結論から言うと、エラーの通り、型が違った。
一致しない原因
文字列のつもりでSQLを書いている項目を、システム側は「データ見る限り、数値じゃね？」と判断していた。
- 文字列の項目に、「数値にも見える値」が含まれていた。
- それが、たまたま、データの冒頭近くに固まっていた。
Excelシートをテーブル扱いする場合、型は最初の数件（デフォルト8件）から推測される。
こちらの思惑とは関係なく、勝手に判断されてしまう。
セルの設定を見てくれても良さそうなのに、見てくれない。
指定する方法は、ない。
解決方法（一応）
DATAシートを件の項目で降順ソートしてから、SQLを発行するようにした。
降順なら、数値より文字列が上にくるので、文字列型だと推測してもらえる可能性が高まる。
先方の了解が得られたので、この時はこれ以上の工数は使わなかった。
もっと汎用性の高い解決方法はないものか。
「型を間違いようのない、お手本データをあらかじめ冒頭に入れておく」とかだろうか。
もちろん、検索でお手本がヒットしないように条件づけするわけだけど、凡ミスの温床になりそうだなぁ。
情報元
ADO を使用して Excel ブックのデータの読み取りおよび書き込みを行う方法 (ExcelADO) / Microsoftサポート
以下引用（強調は私）：
Excel テーブルは、従来のデータベースとは異なり、列に直接データ型を指定する方法がありません。
代わりに、列の中の一定数の行が OLE DB プロバイダによりスキャンされ、そのフィールドのデータ型が推測されます。
スキャンされる行数は、デフォルトでは 8 行ですが、接続文字列の拡張プロパティで、MAXSCANROWS 設定に 1 ～ 16 の値を指定することでスキャンされる行数を変更できます。
雑談
DBってすばらしいよね、便利だよね。
でもこの時は、Excel単体で動かしたかったんだ。
現場ってそういうもんだよね。