|
Sub 宏1()
Dim cnn As Object, rst As Object
Dim sql$, s$, s1$, i&
Set cnn = CreateObject("adodb.connection") '后期绑定ADO
'请选择你需要的文件
s = Application.GetOpenFilename("excel文件,*.xls*", , "请选择:一个excel文件", , False)
If s = "False" Then Exit Sub
If Application.Version < 12 Then
s1 = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=""Excel 8.0;HDR=YES"";Data Source=" & s
Else
s1 = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=""Excel 12.0;HDR=YES"";Data Source=" & s
End If
cnn.Open s1 '建立链接
'语句,自己添加
sql = "SELECT 门店 FROM [月$] WHERE BD_ID = " & Sheet3.Range("A6") & " and 日期 = '2019-10' and 门店 NOT LIKE '%关闭%'and 门店 NOT LIKE '%作废%'"
ActiveSheet.UsedRange.ClearContents '清除内容
'粘贴标题行
Set rst = cnn.Execute(sql)
For i = 0 To rst.Fields.Count - 1 '利用fields属性获取所有字段名(下标从0开始)
Cells(1, i + 1) = rst.Fields(i).Name
Next
[a2].CopyFromRecordset rst '粘贴内容
cnn.Close '关闭链接
Set cnn = Nothing '释放内存
End Sub |
评分
-
1
查看全部评分
-
|