|
Sub 查询()
Dim Conn As New ADODB.Connection
Dim strSql As String
Dim strCon As String
Dim strCj As String
strCon = "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ThisWorkbook.FullName
strCj = Application.InputBox("请输入车间代码", , , , , , , 2)
If strCj = "False" Then Exit Sub
Conn.Open strCon
strSql = "select a.车间,a.产品类型,a.使用等级,a.生产形式,count(*) as 总数量,b.检验比例,b.仪检Ⅰ等级,仪检Ⅱ等级 from [CP$A:L] as a " & _
" left join [BZ$A:D] as b on a.使用等级=b.使用等级 " & _
" Where a.车间='" & strCj & "' " & _
" group by a.车间,a.产品类型,a.使用等级,a.生产形式,b.检验比例,b.仪检Ⅰ等级,仪检Ⅱ等级"
ActiveSheet.UsedRange.Offset(1, 0).ClearContents
ActiveSheet.Range("a2").CopyFromRecordset Conn.Execute(strSql)
End Sub |
|