VBA excel 使用SQL
Sub 按钮1_Click()
Dim Conn As Object, Rst As Object
Dim strConn As String, strSQL As String
Dim i As Integer, PathStr As String
Set Conn = CreateObject(“ADODB.Connection”)
Set Rst = CreateObject(“ADODB.Recordset”)
PathStr = ThisWorkbook.FullName '设置工作簿的完整路径和名称
Select Case Application.Version * 1 '设置连接字符串,根据版本创建连接
Case Is <= 11
strConn = “Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=” & PathStr
Case Is >= 12
strConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & PathStr & “;Extended Properties=”“Excel 12.0;HDR=YES”";"""
End Select
'设置SQL查询语句
’ Sheet3.TextBox1.Text = “SELECT 设备别名,目标库房,sum(出量) as 统计 FROM [pout$] group by 设备别名,目标库房”
strSQL = Sheet3.TextBox1.Text ' "SELECT * FROM [pin$]" 'Sheet3.Cells(1, 1)
'strSQL = “SELECT 设备别名,目标库房,sum(出库数量) as 统计 FROM [pout
]
g
r
o
u
p
b
y
设
备
别
名
,
目
标
库
房
"
′
"
S
E
L
E
C
T
∗
F
R
O
M
[
p
i
n
] group by 设备别名,目标库房" '"SELECT * FROM [pin
]groupby设备别名,目标库房"′"SELECT∗FROM[pin]” “SELECT 设备别名,目标库房,sum(出库数量) as 统计 FROM [pout$] group by 设备别名,目标库房”
Conn.Open strConn '打开数据库链接
Set Rst = Conn.Execute(strSQL) '执行查询,并将结果输出到记录集对象
With Sheet3
.Cells.Clear
For i = 0 To Rst.Fields.Count - 1 '填写标题
.Cells(1, i + 1) = Rst.Fields(i).Name
Next i
.Range(“A2”).CopyFromRecordset Rst
.Cells.EntireColumn.AutoFit '自动调整列宽
End With
Rst.Close '关闭数据库连接
Conn.Close
Set Conn = Nothing
Set Rst = Nothing
End Sub