求助：如何利用VBA+SQL处理几万行的同路径下工作簿数据汇总

sdong7

1. Sub tt() 'by sdong7 20141030
   
2. Application.DisplayAlerts = False
   
3. 
   
4. Dim dic, arr, arr1(1 To 1000000, 1 To 3)
   
5. Dim mxr
   
6. Dim hz As Workbook
   
7. Set hz = GetObject(ThisWorkbook.Path & "\工作簿2.xlsx")
   
8. Set dic = CreateObject("scripting.dictionary") '创建字典
   
9. mxr = hz.Worksheets("sheet1").[a1048576].End(xlUp).Row
   
10. arr = hz.Worksheets("sheet1").Range("a1:bb" & mxr)
    
11. 
    
12. Workbooks("工作簿2.xlsx").Close
    
13. On Error Resume Next
    
14. 
    
15. For i = 2 To mxr
    
16. If dic.Exists(arr(i, 7)) Then '
    
17. hang = dic(arr(i, 7))
    
18. arr1(hang, 3) = arr1(hang, 3) + arr(i, 14)
    
19. arr1(hang, 2) = arr1(hang, 2) + arr(i, 53)
    
20. Else
    
21. k = k + 1
    
22. dic(arr(i, 7)) = k
    
23. arr1(k, 1) = arr(i, 7)
    
24. arr1(k, 3) = arr(i, 14)
    
25. arr1(k, 2) = arr(i, 53)
    
26. End If
    
27. Next i
    
28. 
    
29. [a2].Resize(k, 3) = arr1
    
30. 
    
31. End Sub
    

复制代码

sdong7

这是典型的字典多列求和问题！！！

sdong7

传上附件！！！！！！！！！！！！

zjl19860613

wangqh8203 发表于 2014-10-30 15:47
兄弟，有没有从excel表导入sql的语句，最近也在用ADO导入导出，头都整大了

Sub ADO汇总()
    Dim cnn As Object, SQL$
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties=excel 12.0;data source=" & ThisWorkbook.Path & "\14年全年原始数据.xlsx"
    SQL = "select 部门,成本中心名称,姓名,身份证号码,sum(出勤天数)as 出勤天数 ,sum(总工时)as 总工时,sum(法定出勤工时)as 法定出勤工时,sum(延时工时)as 延时工时,sum(公休工时)as 公休工时,sum(法定工时)as 法定工时,sum(月基本工资)as 月基本工资, sum(月岗位工资)as 月岗位工资,sum(月绩效工资)as 月绩效工资 ,sum(延时加班工资)as 延时加班工资,sum(公休加班工资)as 公休加班工资,sum(法定加班工资)as 法定加班工资,sum(加班工资总额)as 加班工资总额,sum(全勤奖)as 全勤奖,sum(奖励)as 奖励,sum(举荐奖)as 举荐奖,sum(岗位补贴)as 岗位补贴,sum(中夜班津贴)as 中夜班津贴,sum(病假工资)as 病假工资,sum(高温津贴)as 高温津贴,sum(补发基本工资)as 补发基本工资,sum(补发岗位工资)as 补发岗位工资,sum(补发绩效工资)as 补发绩效工资,sum(补发延时加班工资)as 补发延时加班工资,sum(补发公休加班工资)as 补发公休加班工资,sum(补发法定加班工资)as 补发法定加班工资,sum(伙委会补贴)as 伙委会补贴,sum(专利奖金)as 专利奖金,sum(工伤工资)as 工伤工资,sum(驻外员工补贴)as 驻外员工补贴,"
    SQL = SQL + "sum(未成年体检费返还)as 未成年体检费返还,sum(经济补偿金)as 经济补偿金,sum(坚守岗位奖)as 坚守岗位奖,sum(及时报道奖)as 及时报道奖,sum(休假费用)as 休假费用,sum(值班津贴)as 值班津贴,sum(上年奖金)as 上年奖金,sum(春节往返程补贴)as 春节往返程补贴,sum(其他应发合计)as 其他应发合计,sum(应发合计)as 应发合计,sum(养老保险)as 养老保险,sum(失业保险)as 失业保险,sum(医疗保险)as 医疗保险,sum(住房公积金)as 住房公积金,sum(应税合计)as 应税合计,sum(所得税)as 所得税,sum(规费)as 规费,sum(罚款)as 罚款,sum(住宿费)as 住宿费,sum(劳保费)as 劳保费,sum(用餐消费)as 用餐消费,sum(离职损失)as 离职损失,sum(补扣工资)as 补扣工资,sum(补扣住宿费)as 补扣住宿费,sum(预扣下月住宿费)as 预扣下月住宿费,sum(紧急辞职扣除工资)as 紧急辞职扣除工资,sum(暂扣费用)as 暂扣费用,sum(离职培训费分摊)as 离职培训费分摊,sum(公司借款代扣)as 公司借款代扣,sum(应扣其他合计)as 应扣其他合计,sum(应扣合计)as 应扣合计,sum(实发工资)as 实发工资 from [Sheet1$]  group by 部门,成本中心名称,姓名,身份证号码"
   
    [a1].CurrentRegion.Offset(1).ClearContents
    [a2].CopyFromRecordset cnn.Execute(SQL)
   
    cnn.Close
    Set cnn = Nothing
End Sub

zjl19860613

wangqh8203 发表于 2014-10-30 15:47
兄弟，有没有从excel表导入sql的语句，最近也在用ADO导入导出，头都整大了

刚才发你了今天我用的另外一个工资汇总表的语句，特点如下：
1、字段特别多，有80个左右，经过下午时间的测试，均通过；
2、每行语句最多有1024个字符，因此需要使用SQL=SQL+这样的语句
3、注意看下 select语句的构成。
   以上可以证明在复杂表格的情况下，可以实现用ADO+SQL+VBA的统计，我的原始表有30000行，测试下来，比用SUMIFS快无数倍，且较数据透视表灵活，因为导出的结果均不带有公式，原始表30M，我的汇总表仅仅3M不到。

zjl19860613

sdong7 发表于 2014-10-30 16:14
传上附件！！！！！！！！！！！！

谢谢sdong7，你的方法很新颖，我做了下测试，可行，也达到了同样的效果！非常感谢！
看来同样的问题有多个方法，建议使用者们学习多个方法，掌握自己的最习惯的方法。本人长期使用的是宏，包括制作工资表、工资条、标准的打印模板，现在使用别的方法，发现也很不错！

yepulu

以上可以证明在复杂表格的情况下，可以实现用ADO+SQL+VBA的统计，我的原始表有30000行，测试下来，比用SUMIFS快无数倍，且较数据透视表灵活，因为导出的结果均不带有公式，原始表30M，我的汇总表仅仅3M不到。

LMY123

SQL = "select 工号,sum(应发合计)as 应发工资1,sum(出勤天数)as 出勤天数1 from [Sheet1$]  group by 工号"