数据源
计算公式
H1-2*K1
K1
W1-2*K1
W1
H1目标需求
用SQL IN 方法,SQL包含-,*的记录.
下面程序不能实现目标,好像是%的应用.谢谢.
附程序.Function ConnectRst(Sql As String) As ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Rst As New ADODB.Recordset
Cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ThisWorkbook.FullName
Rst.Open Sql, Cnn, adOpenStatic
Set ConnectRst = Rst
End Function
Sub llss()
Dim Sql As String
Sql = "select 计算公式 from [Sheet1$] WHERE 计算公式 not IN ('%-%','+','%*%','/') "
Dim Rst As ADODB.Recordset
Set Rst = ConnectRst(Sql)
With Rst
.MoveFirst
For ii = 0 To .RecordCount - 1
Debug.Print .Fields(0)
.MoveNext
Next ii
End With
End Sub
计算公式
H1-2*K1
K1
W1-2*K1
W1
H1目标需求
用SQL IN 方法,SQL包含-,*的记录.
下面程序不能实现目标,好像是%的应用.谢谢.
附程序.Function ConnectRst(Sql As String) As ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Rst As New ADODB.Recordset
Cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ThisWorkbook.FullName
Rst.Open Sql, Cnn, adOpenStatic
Set ConnectRst = Rst
End Function
Sub llss()
Dim Sql As String
Sql = "select 计算公式 from [Sheet1$] WHERE 计算公式 not IN ('%-%','+','%*%','/') "
Dim Rst As ADODB.Recordset
Set Rst = ConnectRst(Sql)
With Rst
.MoveFirst
For ii = 0 To .RecordCount - 1
Debug.Print .Fields(0)
.MoveNext
Next ii
End With
End Sub
Sql = "select 计算公式 from [Sheet1$] WHERE 计算公式 not like '%[/*+-]%';"
"from [Sheet1$] "& _
"WHERE 计算公式 not like '%[/]%' and not like '%[*]%' and not like '%[+]%' and not like '%[-]%';"
Dim rs As ADODB.Recordset
Dim strConnect As String
On Error Resume Next strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TestExcel.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES;IMEX=0"""
Set cn = New ADODB.Connection
cn.Open strConnect
'创建表和字段
cn.Execute "CREATE TABLE TestData (myField text)"
'插入测试数据
cn.Execute "INSERT INTO TestData(myField) VALUES ('H1 -2 * K1');"
cn.Execute "INSERT INTO TestData(myField) VALUES ('K2');"
cn.Execute "INSERT INTO TestData(myField) VALUES ('H10 +2 + K1');"
cn.Execute "INSERT INTO TestData(myField) VALUES ('S1 -20 - K1');"
cn.Execute "INSERT INTO TestData(myField) VALUES ('D / 2 * K1');"
cn.Execute "INSERT INTO TestData(myField) VALUES ('H1 /2 / K1');"
cn.Execute "INSERT INTO TestData(myField) VALUES ('H1 * 2 * K1');"
cn.Execute "INSERT INTO TestData(myField) VALUES ('TEST');"
cn.Execute "INSERT INTO TestData(myField) VALUES ('H1 + 2 - K1 * D /C');"
'查询测试
Set rs = cn.Execute("select myField from TestData WHERE myField not like '%[/*+-]%';")
While Not rs.EOF
Debug.Print rs!myField
rs.MoveNext
Wend
cn.Close
Set rs = Nothing
Set cn = Nothing
MsgBox "finish"
'返回结果
'K2
'TEST
?