‘ 把该代码放入xlam后,在自定义功能区加载快捷键 alt+2
‘ 使用方法 1.选择’需要进行sql查询区域 包括字段名 2 输入sql语句 3.选择输出数据的单元格
Sub ExcelSQL()
Dim strCon As String
Dim rs As ADODB.Recordset '设置记录集
Dim i, t
Dim Rng1 As Range
Dim Rng2 As Range
On Error Resume Next
Set Rng1 = Selection
Set Con = CreateObject("ADODB.Connection")
Con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=excel 12.0;Data Source=" & ActiveWorkbook.FullName
strCon = "[" & ActiveSheet.Name & "$" & Replace(Rng1.Address, "$", "") & "]"
strSql = Application.InputBox("请输入SQL语句,数据源用@代替:", Type:=2)
strSql = Replace(strSql, "@", strCon)
Set Rng2 = Application.InputBox("请选择输出位置:", Type:=8)
Set rs = Con.Execute(strSql)
For i = 0 To rs.Fields.Count - 1 '逐个字段
Rng2.Offset(0, i) = rs.Fields(i).Name '取字段名 字头放置在cell(3,3)
Next i
'Rng2.CopyFromRecordset Con.Execute(strSql) '不要字段
Rng2.Offset(1, 0).CopyFromRecordset Con.Execute(strSql)
Set Con = Nothing
End Sub