|
xyuer 发表于 2011-11-19 00:35
真是万分感谢,我已经基本上实现了我想要的效果,可是在数据库中有一个小问题,如果sheet1中的姓名,时间 ...
sql代码中的union改union all
- Sub Total()
- Dim conn As Object, sql$, i As Long, j%, p$
- Set conn = CreateObject("adodb.connection") '建立连结
- conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=no';data source=" & ThisWorkbook.FullName '开启连结
- With Sheets("Sheet2")
- .Range("D:D,F:F,H:H").ClearContents '清除Sheet2 D,E,F行
- p = "[Sheet1$A2:I" & Sheet1.[A65536].End(xlUp).Row & "]" 'SQL语句查询Sheet1工作表范围
- '合併Sheet1时间1~4,数据1~4成三行数据库
- sql = "select f1,f2,f6 from " & p & " where len(f2)>0 union all select f1,f3,f7 from " & p & " Where len(f3)>0 union all select f1,f4,f8 from " & p & " Where len(f4)>0 union all select f1,f5,f9 from " & p & " Where len(f5)>0"
- Sheet1.Cells(1, 11).CopyFromRecordset conn.Execute(sql) '将SQL得到的合併数据写入Sheet1 K:M
- For i = 2 To .[A65536].End(xlUp).Row '从Sheet2 第2列往下到最后非空白列循环
- For j = 3 To 7 Step 2
- '时间大于等于开始时间,小于等于结束时间, 且数据等于条件
- sql = "select sum(f3) from [Sheet1$K:M] where f2>=#" & .Cells(i, 1) & "# and f2<=#" & .Cells(i, 2) & "# and f1='" & .Cells(i, j) & "'"
- .Cells(i, j + 1).CopyFromRecordset conn.Execute(sql) '将SQL得到的和计写加入Sheet2 D,F,H
- Next j
- Next i
- End With
- Sheet1.[K1:M65536].ClearContents '清除Sheet1 K:M合併数据
- conn.Close '关闭连结
- Set conn = Nothing
- End Sub
复制代码 |
|