|
Option Explicit
Sub test()
Dim Cn As Object, Sq$, r&
Sheets("Sheet1").Activate
r = Cells(Rows.Count, 1).End(xlUp).Row
Set Cn = CreateObject("ADODB.Connection")
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
Sq = "SELECT 名称,SUM(IIF(摘要='买入',数量,0))-SUM(IIF(摘要='卖出',数量,0)) AS 数量 FROM [Sheet1$A1:C" & r & "] " & _
"GROUP BY 名称 HAVING SUM(IIF(摘要='买入',数量,0))-SUM(IIF(摘要='卖出',数量,0))>0"
[h2:i12345].ClearContents
[h2].CopyFromRecordset Cn.Execute(Sq)
Cn.Close
Set Cn = Nothing
End Sub |
|