Edited at

VBAメモ: ExcelシートをSQLで検索(型が一致しないエラー)


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単体で動かしたかったんだ。

現場ってそういうもんだよね。