|
新插入一个模块,粘贴代码,然后按钮重新指定宏。- Const adUseClient = 3
- Const adModeShareDenyWrite = 8
- Const adModeReadWrite = 3
- Const adModeRead = 1
- Sub 查询()
- Dim AdoConn As Object, AdoRst As Object
- Dim StrConn$, strSQL$, strTemp$
- Dim DataSource$
- Dim iCondition As Byte
- Dim i As Byte
- Set AdoConn = CreateObject("ADODB.Connection")
- Set AdoRst = CreateObject("ADODB.Recordset")
- DataSource = ThisWorkbook.FullName
- Select Case Application.Version
- Case Is = "14.0":
- StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _
- DataSource & ";Extended Properties=""Excel 12.0;HDR=yes;imex=1"";"""
- Case Is = "12.0"
- StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _
- DataSource & ";Extended Properties=""Excel 12.0;HDR=yes;imex=1"";"""
- Case Else
- StrConn = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source=" & DataSource & "Extended Properties=""Excel 8.0;HDR=yes;imex=1"";"
- End Select
- Dim arr
- arr = Range("b1:x2")
-
- For i = LBound(arr) To UBound(arr, 2)
- If Len(arr(2, i)) > 0 Then
- strTemp = strTemp & arr(1, i) & " like '" & arr(2, i) & "%' and "
- End If
- Next
-
- If Len(strTemp) > 0 Then strTemp = Left(strTemp, Len(strTemp) - 5)
- 'strTemp = Replace(strTemp, "%%", "%")
-
- For i = 1 To 4
- Select Case True
- Case Len(strTemp) > 0:
- strSQL = strSQL & "select * from [" & i & "$a1:y] where " & strTemp & " union all "
- Case Else
- strSQL = strSQL & "select * from [" & i & "$a1:y] union all "
- End Select
- Next
- strSQL = Left(strSQL, Len(strSQL) - 10)
- On Error GoTo ErrCheck
- With AdoConn
- .CommandTimeout = 5
- .ConnectionTimeout = 5
- .CursorLocation = adUseClient
- .Mode = adModeRead
- .ConnectionString = StrConn
- .Open
- End With
-
- Set AdoRst = AdoConn.Execute(strSQL)
-
- With Range("a5")
- If Cells(Rows.Count, 1).End(xlUp).Row > 4 Then
- Range("a5:x" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
- End If
- Application.ScreenUpdating = False
- .CopyFromRecordset AdoRst
- Application.ScreenUpdating = True
- MsgBox "查询完成"
- End With
- AdoConn.Close
- Exit Sub
- ErrCheck:
- MsgBox Err.Number & vbCrLf & _
- Err.Description
- Set AdoRst = Nothing
- Set AdoConn = Nothing
- End Sub
复制代码 |
|