Excel VBAにてSQLで文字列中の空白を削除

質問

投稿させていただきます。

MSDNのフォーラムの下記URLの質問にて有効な情報を得ることができませんでしたのでここでもお聞きします。
http://social.msdn.microsoft.com/Forums/ja-JP/vbajp/thread/f8d2eae8-8547-48f8-96e2-12e5e01dc294

ADOとMicrosoftJetOLE DB4.0プロバイダを使用して、自らのブックからワークシートへ接続しています。
SELECTするワークシート(テーブル)の１つのフィールドには文字列中に空白が含まれており、それを無視して、WHERE句の条件を適用させて、データ抽出をしようとしています。

Trim関数は左右の空白削除で、使えることはわかりました。
しかし、文字列間となるとREPLACE関数で置き換えてやるのが最善と考え、それを実行しましたが、「式に未定義関数Replaceがあります」とエラーがでます。

抽出結果には置き換え前のデータを表示させたいです。
REPLACE関数に限らず、何か良い方法はありませんでしょうか。

環境はWindows 7 Excel2003です。

よろしくお願いします。

回答No.2

mitarashi

自ブックへのADO接続はトラブルの元と聞いておりましたが、今でも状況は変わっていない様なので、抽出操作用の別ブックから操作される事をおすすめします。
http://support.microsoft.com/kb/319998/ja

検索すると、Replace関数はJET ネイティブな関数でないので使えないという記事が多いのですが、当方の環境WindowsXP SP3＆Excel2010では、下記は両方とも動作いたしましたので、お知らせしておきます。

'ADO 2.8に参照設定
'ADO 2.1あたりで試しても、Excel2010のブックから抽出出来るので、ADOの仕組みがよく分からなくなりましたが、Providerの機能なんでしょうね。
JET4.0と言いながら、種々のバージョンがある様です。
http://support.microsoft.com/kb/829558
しかしながら、Windows7なら最新のバージョンが入っていそうなものですので、ご参考にはならないかと思います。

Sub test()
Dim cn As New ADODB.Connection
Dim RS As ADODB.Recordset
Dim SQL As String
Dim conn_str As String
'対象とするセル範囲に、tableRangeという名前をつけてあります。
conn_str = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & "dataBook.xlsx;" & _
"Extended Properties=""Excel 12.0 XML;HDR=Yes;"""
cn.Open conn_str
'試験データは手抜きでField1という見出しが付けてあります。
SQL = "SELECT * FROM tableRange WHERE ((Replace([Field1],"" "","""")='aaaaa'));"
Set RS = New ADODB.Recordset
RS.Open SQL, cn, adOpenStatic, adLockReadOnly
Do While Not RS.EOF
Debug.Print RS.Fields(1).Value
RS.MoveNext
Loop
RS.Close: Set RS = Nothing
cn.Close: Set cn = Nothing
End Sub

Sub test2()
Dim cn As New ADODB.Connection
Dim RS As ADODB.Recordset
Dim SQL As String
Dim conn_str As String

conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & "dataBook.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""
cn.Open conn_str
SQL = "SELECT * FROM tableRange WHERE ((Replace([Field1],"" "","""")='aaaaa'));"
Set RS = New ADODB.Recordset
RS.Open SQL, cn, adOpenStatic, adLockReadOnly
Do While Not RS.EOF
Debug.Print RS.Fields(1).Value
RS.MoveNext
Loop
RS.Close: Set RS = Nothing
cn.Close: Set cn = Nothing
End Sub

お礼

お礼が遅れてしまい、申し訳ありません。
大変参考になる提案でした。

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

回答No.1

KURUMITO

例えば次のようにしてはいかがでしょう。
同一セル内での改行を削除するなら
Range("A1").Value = Replace(expression:=Range("A1").Value, Find:=Chr(10), Replace:="")
空白を無くするのなら
Range("A1").Value = Replace(expression:=Range("A1").Value, Find:=" ", Replace:="")

お礼

お礼が遅れてしまい、申し訳ありません。
参考にさせていただきます。
ありがとうございました。

補足

ご回答ありがとうございます。

ご提示いただいた方法はVBAのReplace関数でしょうか。
私が試した方法は
SQLでのWHERE句のフィールドにもSQLのReplace関数をかけて

WHERE 空白のないフィールド='空白のない条件'

と、したかったのしたかったのです。
理解が足らず申し訳ありませんが

もう少し詳しく利用方法をお教え願えませんでしょうか。