求助用SQL实现去重汇总数据

Justin-2016

用SQL实现去重汇总数据.zip (6.91 KB, 下载次数: 5)

2024-5-4 12:38 上传

点击文件名下载附件

baofa2

1. Sub test0()
   
2.   Dim Conn As Object, SQL As String, strConn As String
   
3.   Set Conn = CreateObject("ADODB.Connection")
   
4.   If Application.Version < 12 Then
   
5.     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="
   
6.   Else
   
7.     strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source="
   
8.   End If
   
9.   Conn.Open strConn & ThisWorkbook.FullName
   
10.   SQL = "SELECT 名称,SUM(数量) AS 数量 FROM [Sheet1$A:B] WHERE 名称 IS NOT NULL GROUP BY 名称 "
    
11.   Range("F2").CopyFromRecordset Conn.Execute(SQL)
    
12.   Conn.Close
    
13.   Set Conn = Nothing
    
14. End Sub
    

复制代码