[Excel]ADODBでNull変換されてしまう

質問

お世話になります。
今、Office2003で次のようなマクロを作成しています。
マクロが保存されているブックM、
参照するデータが保存されているブックD、
出力先のブックOがあり、
ブックMにてADODBをつかって、ブックDからSQLによる条件でブックOへ出力します。

このとき、ブックDのセルではちゃんとデータがセットさせているのに
出力したブックOでは空になってしまうという現象が発生して困っています。

デバッグ等して確認してみたところ、
書式が標準のセルで、文字列型の数値(文字列型のセルから値コピーした場所)が入っていると
RecordSetにとれた段階でNullに変換されてしまてしまっているように見えました。

この問題を解決するために何かよい方法はありますでしょうか?

ソースコードの詳細は確認できないのですが
こんな感じだったと思います。
※ rs.Requeryにて、rsの中身を確認した時点でNullだったので QueryTablesによる取り込みは関係ないと考えています。

Set conn = CreateObject("ADODB.Connection")
conn.Open ConnectString

Set sql = CreateObject("ADODB.Command")
sql.CommandType = 1
sql.ActiveConnection = conn
sql.CommandText = SQL文(? とUNION含む)
sql(0) = パラメータ設定
...

Set rs = CreateObject("ADODB.RecordSet")
Set rs = sql.Execute
With Sheet1.QueryTables.Add(rs, "A1")
.AdjustColumnWidth = False
.FieldNames = True
.BackgroundQuery = False
.Refresh False
.Delete
End With
...

回答No.5

end-u · ベストアンサー

度々失礼。
http://support.microsoft.com/kb/194124/ja
全てが強制的に文字列に変わるようですが

With conn
　　.Provider = "Microsoft.Jet.OLEDB.4.0"
　　.Properties("Extended Properties").Value = "Excel 8.0;IMEX=1" '■
　　.Properties("Data Source") = "c:\temp\data.xls"
　　.Open
End With
..『オプション IMEX=1;』を追加する方法でもいいかもしれません。

さっきのSub try()のQueryTableオブジェクトだけで使う場合はConnectionを以下に変更です。

wkCON = "OLEDB;" & _
　　　　"Provider=Microsoft.Jet.OLEDB.4.0;" & _
　　　　"Data Source=C:\temp\data.xls;" & _
　　　　"Extended Properties=""Excel 8.0;IMEX=1;"""

お礼

お世話になります。試してきました。

結論から言うと100%の解決は無理でした。
どうやら、HDR=YESとしてしまうと、IMEX=1としても
最初の数行の内で数の多い型になってしまうようです。

※ 最初の10行の型部分をテキストに、
　 なおすとHRD=YESでもその後ろが混ざっていた場合もちゃんととれました。

仕方ないので、HRD=NO;IMEX=1;としタイトル部分を配列に格納しておいて、
2行目にSQLの結果を挿入した後、1行目にタイトルを手動で設定する方法で解決しました。

ありがとうございました。

補足

お世話になります！
困っていた内容はこれかもしれません！

月曜日に本物でも、試してみてまたご連絡させていただきます。
値として数値っぽものが入っているだけでその扱いはテキストなので
その違いによる問題はでない見込みです。

その他、回答いただいた内容ですが、
QueryTablesでもSQLを指定できることは知りませんでした。
UNION ALLを利用したのは、ただの習慣です。

回答No.4

end-u

間違いました..orz
With wkBook.Sheets(1)
　　.Range("A1").Value = "区分"
　　.Range("A2").Value = "追加"
　　.Range("A3").Value = "更新"
　　.Range("A4").Value = "削除"
　　.Range("B1").Value = "都道府県コード"
　　.Range("B2:B4").Value = "1"
　　Set Criteria = .Range("A1:B4")
End With

回答No.3

end-u

..ぅ。失礼...orz
QueryTableオブジェクトでも混在データはNullを返す？
#元からそんな仕様だったっけかな...

代案で
Sub try2()
　　Const BKDATA = "c:\temp\data.xls"
　　Const BKOUT = "out.xls"
　　Dim wkBook　 As Workbook '抽出条件作業Book
　　Dim CopyTo　 As Range
　　Dim Criteria As Range

　　Application.ScreenUpdating = False
　　'抽出先
　　With Workbooks(BKOUT).Sheets("sheet1")
　　　　.UsedRange.Clear
　　　　Set CopyTo = .Range("A1")
　　End With
　　'抽出条件
　　Set wkBook = Workbooks.Add(xlWBATWorksheet)
　　With wkBook.Sheets(1)
　　　　.Range("A1:C1").Value = "区分"
　　　　.Range("A2").Value = "追加"
　　　　.Range("B3").Value = "更新"
　　　　.Range("C4").Value = "削除"
　　　　.Range("D1").Value = "都道府県コード"
　　　　.Range("D2:D4").Value = "1"
　　　　Set Criteria = .Range("A1:D4")
　　End With
　　'抽出元
　　With Workbooks.Open(BKDATA, ReadOnly:=True)
　　　　.Sheets("住所録").Range("A1").CurrentRegion.AdvancedFilter _
　　　　　　　　Action:=xlFilterCopy, _
　　　　　　　　CriteriaRange:=Criteria, _
　　　　　　　　CopyToRange:=CopyTo, _
　　　　　　　　Unique:=False
　　　　.Close False
　　End With

　　wkBook.Close False
　　Application.ScreenUpdating = True

　　Set Criteria = Nothing
　　Set CopyTo = Nothing
　　Set wkBook = Nothing
End Sub

ScreenUpdatingプロパティで画面表示制御しOpenを意識させないようにして、
オーソドックスにAdvancedFilterメソッド（[フィルタオプションの設定]）を使うと良いです。

回答No.2

end-u

>SQLはたとえばこんな具合のものです。
あくまで例えば、という事で実際はもっと複雑だったりしますか？
もし例示通りで良ければ

Sub try()
　　Const BKDATA = "c:\temp\data.xls"
　　Const BKOUT = "out.xls"
　　Dim wkSHT As Worksheet
　　Dim wkQRY As QueryTable
　　Dim wkCON As String
　　Dim wkSQL As String
　　Dim p　　 As String

　　p = "?"
　　wkCON = "ODBC;DSN=Excel Files;DBQ=" & BKDATA
　　wkSQL = "SELECT * FROM [住所録$] WHERE 区分='追加' AND 都道府県コード=" & p & _
　　　　　　" UNION ALL " & _
　　　　　　"SELECT * FROM [住所録$] WHERE 区分='更新' AND 都道府県コード=" & p & _
　　　　　　" UNION ALL " & _
　　　　　　"SELECT * FROM [住所録$] WHERE 区分='削除' AND 都道府県コード=" & p
　　Set wkSHT = Workbooks(BKOUT).Sheets("sheet1")
　　Set wkQRY = wkSHT.QueryTables.Add(Connection:=wkCON, _
　　　　　　　　　　　　　　　　　　　Destination:=wkSHT.Range("A1"), _
　　　　　　　　　　　　　　　　　　　Sql:=wkSQL)
　　With wkQRY
　　　　.RefreshStyle = xlOverwriteCells
　　　　.AdjustColumnWidth = False
　　　　.Refresh BackgroundQuery:=False
　　　　wkSHT.Names(.Name).Delete '名前定義削除
　　　　.Delete 'QueryTable削除（上の行とセットで必要に応じ。）
　　End With

　　Set wkSHT = Nothing
　　Set wkQRY = Nothing
End Sub

こんな感じになりますが。

または、SQL文は以下でも同じかと。
wkSQL = "SELECT * FROM [住所録$]" & _
　　　　" WHERE (区分='追加') AND (都道府県コード=" & p & ")" & _
　　　　　 " OR (区分='更新') AND (都道府県コード=" & p & ")" & _
　　　　　 " OR (区分='削除') AND (都道府県コード=" & p & ")"

回答No.1

end-u

>書式が標準のセルで、文字列型の数値(文字列型のセルから値コピーした場所)が入っていると..
書式ではなく、データ型の混在が原因だと思われます。

[HOWTO] Visual Basic または VBA から ADO を Excel データで使用する
http://support.microsoft.com/kb/257819/ja
より抜粋。
>両方の OLE DB プロバイダに適用される考慮事項
>データ型の混在についての注意
>
>「スキャンする行数」で説明したとおり、ADO は Excel のワークシートまたは範囲に含まれる各列のデータ型を推測する必要があります (これは Excel のセル書式設定には影響されません)。同じ列に数値と文字列値が混在していると重大な問題が発生することがあります。Jet プロバイダと ODBC プロバイダはどちらも、最も数が多い型についてはそのデータを返し、その他の数が少ないデータ型については NULL (空) 値を返します。2 つの型が列内で同数の場合、文字列ではなく数値が返されます。

そもそも、ADODB.RecordSetを経由せず、
QueryTablesでダイレクトにxlsファイルを読み込めば良いと思いますが、
何か事情があるのでしょうか？

お礼

ありがとうございます。
やっぱり、混ざっていることが原因ですか。。。
できれば、エラーとして検出するよりも許容する方向へ倒したいです

> そもそも、ADODB.RecordSetを経由せず、
> QueryTablesでダイレクトにxlsファイルを読み込めば良いと思いますが、
> 何か事情があるのでしょうか？

手抜きといってしまえばそれまでなのですが、
抽出する条件が、複数パターンあってそれを結合して取り込む必要があるためです。

QueryTablesでは全部を取り込んでしまうので条件指定は出来ず
また、オートフィルタをかけたものを取り込むにしても複数ある関係で
毎回、1行目のタイトル行を削除したりしなければならないのかなーと

SQLはたとえばこんな具合のものです。
SELECT * FROM [住所録$] WHERE 区分='追加' AND 都道府県コード=?
UNION ALL
SELECT * FROM [住所録$] WHERE 区分='更新' AND 都道府県コード=?
UNION ALL
SELECT * FROM [住所録$] WHERE 区分='削除' AND 都道府県コード=?