|
楼主 |
发表于 2010-7-30 12:02
|
显示全部楼层
刚学会VBA就看见SQL了看上去好像很简单,我还得再努力,,真是学无止境啊
请教dsmch , 如果项目在N列,本月求和及累计求和在P、Q、R、S列,并且项目里的内容是固定的, 比如顺序是品种A、C、B、D,怎么来改这段代码呢
Sub Macro2()
Dim cnn As Object, sql As String
Set cnn = CreateObject("adodb.connection")
cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
sql = "select 项目,sum(采购), sum(销售) from [PZ$] where 月份=" & Range("t1") & " group by 项目"
Range("o4").CopyFromRecordset cnn.Execute(sql)
sql1 = "select sum(采购), sum(销售) from [PZ$] where 月份<=" & Range("t1") & " group by 项目"
Range("r4").CopyFromRecordset cnn.Execute(sql1)
cnn.Close: Set cnn = Nothing
End Sub |
|