|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub 采购01()
Dim bbxz, xmxz, dwxz, qsrq, jsrq As String
bbxz = Range("c2").Value '报表选择
xmxz = Range("c3").Value '项目选择
dwxz = Range("c4").Value '单位选择
qsrq = Range("e2").Value '起始日期
jsrq = Range("e3").Value '结束日期
Sheet1.UsedRange.Offset(9).ClearContents
Dim Sql$, Sql1$, Sql2$
Dim RST As New ADODB.Recordset, conn As New ADODB.Connection
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
Sql1 = "(select 采购单编号 from [采购单$] where 申请日期 between #" & qsrq & "# and #" & jsrq & "# and 申请单位='" & dwxz & "' and 申请项目='" & xmxz & "') a"
Sql2 = "(select 采购单号,名称,规格,单位,sum(数量) as 数量,单价,sum(小计) as 小计 from [物资库$] where 采购日期 between #" & qsrq & "# and #" & jsrq & "# group by 采购单号,名称,规格,单位,单价) b"
Sql = "select b.名称,b.规格,b.单位,b.数量,b.单价,b.小计 from " & Sql1 & " left join " & Sql2 & " on a.采购单编号=b.采购单号"
RST.Open Sql, conn, 1, 1
Range("c6") = RST.RecordCount
Range("b10").CopyFromRecordset RST
RST.Close
conn.Close
End Sub |
|