excel连接access数据库的多条件模糊查找

附件

cx.rar(17.72 KB)

2010-6-7 09:38, 下载次数: 47

复制内容到剪贴板

代码:

Sub hh()
    Dim Cn As Object, Sql$
    Dim Arr, k%, Str$

    Arr = Range("B2", [B1].End(4))
    For k = 1 To UBound(Arr)
        Str = Str & " '" & Arr(k, 1) & "'"
    Next
    Str = Replace(Trim(Str), " ", ",")

    Set Cn = CreateObject("Adodb.Connection")
    Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\cd.mdb"
    Sql = "Select barcode From sp " _
        & "Where itemname Like '" & [A2] & "%' " _
        & "And Right(barcode,4) In (" & Str & ")"

    [C2:C65536].ClearContents
    [C2].CopyFromRecordset Cn.Execute(Sql)
    Cn.Close: Set Cn = Nothing
End Sub