|
楼主 |
发表于 2019-1-9 10:29
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
感谢你,这个太强大了。
- Sub mxz()
- Dim conn As New ADODB.Connection
- Dim rst As New ADODB.Recordset
- conn.Open "dsn=excel files;dbq=" & ThisWorkbook.FullName
- Application.ScreenUpdating = False
- With Worksheets("mxz")
- '清空明细账
- .UsedRange.Offset(3, 0).ClearContents
- '取年初数据
- Set rst = conn.Execute("select ' ',' ','上年结转',0,0,0,0,期初数量,期初金额 from [期初数据$] where 页码='" & UCase(.Range("i1")) & "' and 期初金额<>0")
- .Range("a65536").End(xlUp).Offset(1, 0).CopyFromRecordset rst
- '取明细数据
- Set rst = conn.Execute("select 日期,凭证号,摘要,借方数量,借方金额,贷方数量,贷方金额,0,0 from [09年度数据$] where 页码='" & UCase(.Range("i1")) & "' order by 日期,凭证号")
- .Range("a65536").End(xlUp).Offset(1, 0).CopyFromRecordset rst
- '计算余额
- If .Range("c4") <> "上年结转" Then .Range("h4") = .Range("d4"): .Range("i4") = .Range("e4")
- For i = 5 To .Range("a65536").End(xlUp).Row
- .Cells(i, 8) = .Cells(i - 1, 8) + .Cells(i, 4) - .Cells(i, 6)
- .Cells(i, 9) = .Cells(i - 1, 9) + .Cells(i, 5) - .Cells(i, 7)
- Next i
- '取本月合计
- Set rst = conn.Execute("select max(日期),'','本月合计',SUM(借方数量),SUM(借方金额),SUM(贷方数量),SUM(贷方金额),0,0 from [09年度数据$] where 页码='" & UCase(.Range("i1")) & "' and (借方金额<>0 OR 贷方金额<>0) group by month(日期) order by 1")
- .Range("a65536").End(xlUp).Offset(1, 0).CopyFromRecordset rst
- '取本季累计
- '一季度
- Set rst = conn.Execute("select max(日期),'','本季累计',SUM(借方数量),SUM(借方金额),SUM(贷方数量),SUM(贷方金额),0,0 from [09年度数据$],(select distinct month(日期) as rq from [09年度数据$] where month(日期) in(1,2,3)) as ljrq where 页码='" & UCase(.Range("i1")) & "' and (借方金额<>0 OR 贷方金额<>0) and month(日期)<=rq and month(日期) in(1,2,3) group by rq order by 1")
- .Range("a65536").End(xlUp).Offset(1, 0).CopyFromRecordset rst
- '二季度
- Set rst = conn.Execute("select max(日期),'','本季累计',SUM(借方数量),SUM(借方金额),SUM(贷方数量),SUM(贷方金额),0,0 from [09年度数据$],(select distinct month(日期) as rq from [09年度数据$] where month(日期) in(4,5,6)) as ljrq where 页码='" & UCase(.Range("i1")) & "' and (借方金额<>0 OR 贷方金额<>0) and month(日期)<=rq and month(日期) in(4,5,6) group by rq order by 1")
- .Range("a65536").End(xlUp).Offset(1, 0).CopyFromRecordset rst
- '三季度
- Set rst = conn.Execute("select max(日期),'','本季累计',SUM(借方数量),SUM(借方金额),SUM(贷方数量),SUM(贷方金额),0,0 from [09年度数据$],(select distinct month(日期) as rq from [09年度数据$] where month(日期) in(7,8,9)) as ljrq where 页码='" & UCase(.Range("i1")) & "' and (借方金额<>0 OR 贷方金额<>0) and month(日期)<=rq and month(日期) in(7,8,9) group by rq order by 1")
- .Range("a65536").End(xlUp).Offset(1, 0).CopyFromRecordset rst
- '四季度
- Set rst = conn.Execute("select max(日期),'','本季累计',SUM(借方数量),SUM(借方金额),SUM(贷方数量),SUM(贷方金额),0,0 from [09年度数据$],(select distinct month(日期) as rq from [09年度数据$] where month(日期) in(10,11,12)) as ljrq where 页码='" & UCase(.Range("i1")) & "' and (借方金额<>0 OR 贷方金额<>0) and month(日期)<=rq and month(日期) in(10,11,12) group by rq order by 1")
- .Range("a65536").End(xlUp).Offset(1, 0).CopyFromRecordset rst
- '取本年累计
- Set rst = conn.Execute("select max(日期),'','本年累计',SUM(借方数量),SUM(借方金额),SUM(贷方数量),SUM(贷方金额),0,0 from [09年度数据$],(select distinct month(日期) as rq from [09年度数据$]) as ljrq where 页码='" & UCase(.Range("i1")) & "' and (借方金额<>0 OR 贷方金额<>0) and month(日期)<=rq group by rq order by 1")
- .Range("a65536").End(xlUp).Offset(1, 0).CopyFromRecordset rst
- '明细账排序
- .UsedRange.Offset(3, 0).Sort Key1:=Range("A4"), Header:=xlGuess
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|