excel 宏使用sql语句
我需要在excle里面查询这个语句
数据库地址：100.100.23.11 帐号:cql 密码 :admin
select 片区=c.qymc,店代码=b.ckdm,店名=b.ckmc,库位=(select case when a.dm2_1='000' then '普通' when a.dm2_1='001' then '异地' else '错误' end),
盘点日期=a.rq,实际数=a.sl,账面数=a.sl_1,盈亏数=a.sl_2,盈亏金额=a.je_2,
(select case when a.dm4='0'then '全盘' when a.dm4='1' and a.bz is null then '部分盘' else '分类盘' end) as '盘点类型',备注=a.bz,上传时间=a.time
from ckykd as a,cangku as b,quyu as c
where a.dm2=b.ckdm and b.qydm=c.qydm and convert(varchar(10),a.rq,120) like '%2012-01%' and b.qydm='11' --b.qydm片区条件
order by c.qymc,b.ckmc asc
1.在宏编辑界面中进行SQL数据查询以及生成报表的代码如下：
Sub Static()
Dim objNewWorkbook As Workbook '定义一个Workbook对象
Set objNewWorkbook = Workbooks.Add(ThisWorkbook.Path & "\模板.xlt") '使用模板文件新建一个EXCEL报表生成文件
Set objConnection = CreateObject("ADODB.Connection") '创建一个ADO连接
objConnection.Open "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties='Excel 8.0;Hdr=yes;Imex=1';Data Source=" & ThisWorkbook.FullName '设置连接字符串
strCommand = "select 施工人, count(*) as 拆电话 from [" & Sheet1.Name & "$] where 施工动作 = '拆' and 专业类型 = '电话' group by 施工人" '查询表单一中的数据源进行统计
?objNewWorkbook.Sheets(1).Range("A3").CopyFromRecordset objConnection.Execute(strCommand)
2.将查询统计结果输出到目的报表文件的表单1的从A3开始的区域中
3.下面代码是调用EXCEL的排序方式进行汉字排序，这里SQL语句中的排序对于汉字排序与期望结果不同
4.使用EXCEL中的排序方法是根据EXCEL版本不同调用有所区别
Select Case Application.Version
Case "11.0":
objNewWorkbook.Sheets(1).Range("A3:M" & CStr(intSumRow - 1)).Sort Key1:=objNewWorkbook.Sheets(1).Range("A3"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
Case "12.0":
objNewWorkbook.Sheets(1).Sort.SortFields.Clear
objNewWorkbook.Sheets(1).Sort.SortFields.Add Key:=Range("A3:A" & CStr(intSumRow - 1)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With objNewWorkbook.Sheets(1).Sort
.SetRange Range("A2:M" & CStr(intSumRow - 1))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Case Else
End Select
objConnection.Close
End Sub
Sub Static()
Dim objNewWorkbook As Workbook '定义一个Workbook对象
Set objNewWorkbook = Workbooks.Add(ThisWorkbook.Path & "\模板.xlt") '使用模板文件新建一个EXCEL报表生成文件
Set objConnection = CreateObject("ADODB.Connection") '创建一个ADO连接
objConnection.Open "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties='Excel 8.0;Hdr=yes;Imex=1';Data Source=" & ThisWorkbook.FullName '设置连接字符串
strCommand = "select 施工人, count(*) as 拆电话 from [" & Sheet1.Name & "$] where 施工动作 = '拆' and 专业类型 = '电话' group by 施工人" '查询表单一中的数据源进行统计
?objNewWorkbook.Sheets(1).Range("A3").CopyFromRecordset objConnection.Execute(strCommand)
2.将查询统计结果输出到目的报表文件的表单1的从A3开始的区域中
3.下面代码是调用EXCEL的排序方式进行汉字排序，这里SQL语句中的排序对于汉字排序与期望结果不同
4.使用EXCEL中的排序方法是根据EXCEL版本不同调用有所区别
Select Case Application.Version
Case "11.0":
objNewWorkbook.Sheets(1).Range("A3:M" & CStr(intSumRow - 1)).Sort Key1:=objNewWorkbook.Sheets(1).Range("A3"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
Case "12.0":
objNewWorkbook.Sheets(1).Sort.SortFields.Clear
objNewWorkbook.Sheets(1).Sort.SortFields.Add Key:=Range("A3:A" & CStr(intSumRow - 1)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With objNewWorkbook.Sheets(1).Sort
.SetRange Range("A2:M" & CStr(intSumRow - 1))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Case Else
End Select
objConnection.Close
End Sub
温馨提示：内容为网友见解，仅供参考
第1个回答 2015-03-26
'VBA工具->引用->Microsoft ActiveX Date Object 2.0
Public Sub GetData()
Dim Flag, Err As Boolean
Dim Cnn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim SQL As String
Sheet2.Cells.Clear
Flag = False
Err = True
Set Cnn = New ADODB.Connection
On Error GoTo label
With Cnn
.Provider = "SQLOLEDB"
.ConnectionString = "Driver=SQL Server;Server=100.100.23.11;Database=数据库名;Uid=cql ;Pwd=admin;"
.Open
End With
Set Rst = New ADODB.Recordset
SQL = "你的sql"
Rst.Open SQL, Cnn, adOpenKeyset, adLockBatchOptimistic
Sheet2.Range("A1").CopyFromRecordset Rst
label:
Rst.Close
Cnn.Close
Set Rst = Nothing
Set Cnn = Nothing
End Sub追问
Public Sub GetData()
Dim Flag, Err As Boolean
Dim Cnn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim SQL As String
Sheet2.Cells.Clear
Flag = False
Err = True
Set Cnn = New ADODB.Connection
On Error GoTo label
With Cnn
.Provider = "SQLOLEDB"
.ConnectionString = "Driver=SQL Server;Server=100.100.23.11;Database=数据库名;Uid=cql ;Pwd=admin;"
.Open
End With
Set Rst = New ADODB.Recordset
SQL = "你的sql"
Rst.Open SQL, Cnn, adOpenKeyset, adLockBatchOptimistic
Sheet2.Range("A1").CopyFromRecordset Rst
label:
Rst.Close
Cnn.Close
Set Rst = Nothing
Set Cnn = Nothing
End Sub追问
我对这个是完全不懂 我编辑了他提示
我无法运行
要添加引用,菜单->工具->引用->Microsoft ActiveX Date Object 2.0
相似回答
