|
- Private Sub CommandButton1_Click()
- Dim conn As Object, rs As Object
- Dim sql As String, sql1 As String, sql2 As String
- Dim i As Integer, field
- Set conn = CreateObject("adodb.connection")
- Set rs = CreateObject("adodb.recordset")
- conn.Open "dsn=excel files;dbq=" & ThisWorkbook.FullName
- sql = "select 档口,导购1 AS 导购,数量 AS DX FROM [A$] Where not(档口 is null) UNION ALL select 档口,导购2 AS 购导,数量 AS DX FROM [A$] Where not(档口 is null)"
- sql = sql & " Union All Select 档口,'合计' as 导购,Sum(数量)*2 as DX FROM [A$] Where not(档口 is null) Group By 档口"
- sql1 = "transform sum(dx) as 点效 select 导购,sum(dx) as HJ from (" & sql & ") Where not(档口 is null) group by 导购 Order By 导购 PIVOT 档口"
- Cells.Clear
- Range("B5").CopyFromRecordset conn.Execute(sql1)
- rs.Open (sql1), conn
- For Each field In rs.Fields
- [a4].Offset(0, i + 1) = "'" & field.Name
- i = i + 1
- Next
- Range("B4").CurrentRegion.Borders.LineStyle = 1
- conn.Close
- Set conn = Nothing
- Set rs = Nothing
- End Sub
复制代码 |
|