|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub TJ()
Dim cnn As Object, rs As Object, rs2, SQL$, SQL2$, i&, arr
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Extended Properties='Excel 12.0;HDR=yes;IMEX=1';Data Source=" & ThisWorkbook.FullName
SQL = "SELECT 推荐人,count(记账日期) as 新客消费人天数,sum(消费金额) as 新客总额 FROM [1-2$a1:f] where 新老='新客'and (记账日期>=#2020/7/15# and 记账日期<=#2020/8/14#) group by 推荐人,记账日期"
SQL2 = "SELECT 推荐人,count(记账日期) as 老客消费人天数,sum(消费金额) as 老客总额 FROM [1-2$a1:f] where (记账日期>=#2020/7/15# and 记账日期<=#2020/8/14#) group by 推荐人,记账日期,新老 having 新老='老客'"
Set rs = cnn.Execute(SQL)
Set rs2 = cnn.Execute(SQL2)
With ThisWorkbook.Sheets(2)
.Range("a2").CopyFromRecordset rs
.Range("d2").CopyFromRecordset rs2
End With
rs.Close
rs2.Close
cnn.Close
Set rs = Nothing
Set rs2 = Nothing
Set cnn = Nothing
End Sub |
|