|
急求附件疑难解答,谢谢各位大侠相助!
修改一下代码试试能不能发。
- Sub 查询()
- Dim cn, rs, sql$
- Set cn = CreateObject("adodb.connection")
- With cn
- .provider = "microsoft.ace.oledb.12.0"
- .connectionstring = "extended properties='excel 12.0;hdr=no';data source=" & ThisWorkbook.FullName
- .Open
- End With
- sql = "select f7,f8,f9,f10,f11,f12,f13,f14,f15,f17 from [产品信息库$a2:q"
- sql = sql & Sheet2.Range("a65536").End(3).Row & "] where"
- With Sheet3
- If .Range("d2") <> "" Then sql = sql & " f8='" & .Range("d2") & "' and "
- If .Range("g2") <> "" Then sql = sql & " f9='" & .Range("g2") & "' and "
- If .Range("j2") <> "" Then sql = sql & " f7 like '%" & .Range("j2") & "%' and "
- sql = Left(sql, Len(sql) - 5)
- Set rs = cn.Execute(sql)
- .Range("4:1000").ClearContents
- .Range("a4").CopyFromRecordset rs
- End With
- cn.Close: Set cn = Nothing
- End Sub
复制代码
|
|