|
- Sub Inquire_SQL()
- Dim Conn As Object, Rst As Object
- Dim strConn$, strSQL$, sMyr$
- Dim arA, i As Integer
- Set Conn = CreateObject("ADODB.Connection")
- Set Rst = CreateObject("ADODB.Recordset")
- arA = Range("l1").CurrentRegion
- For i = 2 To UBound(arA)
- sMyr = IIf(Trim(arA(i, 2)) <> "", "and " & Cells(1, Asc(arA(1, 2)) - 64) & "='" & arA(i, 2) & "'", "")
- strSQL = strSQL & "or " & Cells(1, Asc(arA(1, 1)) - 64) & "='" & arA(i, 1) & "'" & sMyr
- Next
- strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & ThisWorkbook.FullName
- strSQL = "select * from [sheet1$A1:J32] Where " & Mid(strSQL, 4)
- Conn.Open strConn
- Set Rst = Conn.Execute(strSQL)
- Range("o1").CurrentRegion.Offset(1).ClearContents
- Range("o2").CopyFromRecordset Rst
- Rst.Close
- Conn.Close
- Set Conn = Nothing
- Set Rst = Nothing
- End Sub
复制代码 |
|