学习地址:
Set conn = CreateObject("adodb.connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; & _
Extended Properties='Excel 12.0; HDR=YES; IMEX=1'; & _
Data Source=" & ThisWorkbook.FullName
这里HDR参数说明是否有标题行
IMEX=1会将所有数据转为文本,避免数据类型不一致出错
strsql = "select s,count(*) as cntc from [a$] group by s order by count(s)"
Set rst = conn.Execute(strsql)
ThisWorkbook.Sheets(2).Cells.Clear
'逐个粘贴结果字段名
For i = 0 To rst.Fields.Count - 1
ThisWorkbook.Sheets(2).Cells(1, i + 1) = rst.Fields(i).Name
Next i
'粘贴data
ThisWorkbook.Sheets(2).[A2].CopyFromRecordset rst
conn.Close
ThisWorkbook.Save
to do: 整行粘贴字段名,而不是循环粘贴