|
发表于 2014-3-8 11:19
|
显示全部楼层
本楼为最佳答案
- Sub 统计汇总()
- Dim conn, sql(1 To 2), address(1 To 2), i, arr(1 To 2)
- Set conn = CreateObject("adodb.connection")
- If Val(Application.Version) >= 12 Then
- conn.Open "provider=microsoft.ace.oledb.12.0;extended properties=excel 12.0;data source=" & ThisWorkbook.FullName
- Else
- conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
- End If
- sql(1) = "select 商品编码,商品名称,发生单价,sum(销售数量),sum(实收金额) from [销售汇总$a3:j] where 所属组织 is not null group by 商品编码,商品名称,发生单价"
- sql(2) = "select 销售日期,sum(销售数量),sum(实收金额) from [销售汇总$a3:j] where 所属组织 is not null group by 销售日期"
- 'sql(1) = "select 商品编码,商品名称,发生单价,sum(销售数量),sum(实收金额),from [销售汇总$a4:j] where 所属组织 is not null group by 商品编码,商品名称,发生单价"
- 'sql(2) = "select 销售日期,sum(销售数量),sum(实收金额),from [销售汇总$a4:j] where 所属组织 is not null group by 销售日期"
- address(1) = "B"
- address(2) = "J"
- With Sheets("统计汇总")
- .Range("b4:z" & Application.Max(.[b65536].End(xlUp).Row, 4)).Clear
- For i = 1 To 2
- .Range(address(i) & 4).CopyFromRecordset conn.Execute(sql(i))
- Next i
- For i = 1 To 2
- arr(i) = .Range(address(i) & 65536).End(xlUp).Row
- .Range(address(i) & arr(i) + 1) = "合计"
- Next i
- .Range("e" & arr(1) + 1).FormulaR1C1 = "=sum(r2c:r[-1]c)"
- .Range("f" & arr(1) + 1).FormulaR1C1 = "=sum(r2c:r[-1]c)"
- .Range("k" & arr(2) + 1).FormulaR1C1 = "=sum(r2c:r[-1]c)"
- .Range("l" & arr(2) + 1).FormulaR1C1 = "=sum(r2c:r[-1]c)"
- With Application.Union(.[e:e], .[f:f], .[k:k], .[l:l])
- .NumberFormat = "0.00"
- End With
- For i = 1 To 2
- With Range(address(i) & 4).CurrentRegion
- .Font.Size = 10
- .Borders.LineStyle = xlContinuous
- .HorizontalAlignment = xlCenter
- End With
- Next i
- End With
- conn.Close: Set sonn = Nothing
- End Sub
复制代码 |
|