Sub test() Dim Sql$, Cnn As Object, i%, sh As String, m%, rst As Object Set Cnn = CreateObject("ADODB.connection"): Set rst = CreateObject("adodb.recordset") p = ThisWorkbook.Path sh = Dir(p & "\明细*.xls") Sheets("汇总").[a2:c2] = Split("工号 姓名 数量") Sheets("汇总").[a3:c65536] = "" While sh > "" Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;';Data Source=" & ThisWorkbook.Path & "\" & sh Sql = "SELECT 工号, 姓名,sum(产量) as 数量 FROM [明细$] group by 工号, 姓名" Sheets("汇总").[a65536].End(xlUp).Offset(1, 0).CopyFromRecordset Cnn.Execute(Sql) Cnn.Close sh = Dir Wend m = Range("a65536").End(xlUp).Row Cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName rst.Open "select 工号,姓名,sum(数量) from [汇总$a2:c" & m & "] group by 工号,姓名", Cnn, 1, 3 Range("a3:c" & m).ClearContents Range("a3").CopyFromRecordset rst rst.Close: Cnn.Close End Sub 在狼版代码的基础上进行了一下完善,看是否符合要求。不过个人觉得比较麻烦,但又想不到更好的解决办法。 |