|
Private Sub CommandButton1_Click()
Set wb = ThisWorkbook
With ActiveWorkbook.Connections("查询来自 MS Access Database").ODBCConnection
.BackgroundQuery = False
.CommandText = Array( _
"SELECT 查询1.月份, 查询1.大区, 查询1.省份, 查询1.分类, 查询1.品牌, 查询1.`品牌(细项)`, 查询1.门店编码, 查询1.名称, 查询1.商品编码, 查询1.简称, 查询1.商品条码, 查询1.商品状态" _
, _
", 查询1.我司状态, 查询1.合同号, 查询1.配送方式, 查询1.销售数量, 查询1.销售金额, 查询1.期末数量, 查询1.期末金额" & Chr(13) & "" & Chr(10) & "FROM `C:\Users\Administrator\Desktop\孩子王销售分析" _
, _
"\数据.mdb`.查询1 查询1 WHERE (查询1.大区 like '%" & Cells(1, 2) & "%') AND (查询1.品牌 like '%" & Cells(4, 2) & "%') AND (查询1.月份 BETWEEN '" & Cells(3, 2) & "'and '" & Cells(3, 3) & "') AND (查询1.省" _
, _
"份 like '%" & Cells(2, 2) & "%') AND (查询1.门店编码 like '%" & Cells(5, 2) & "%') AND (查询1.名称 like '%" & Cells(6, 2) & "%') AND (查询1.商品编码 like '%" & Cells(7, 2) & "%') AND (查询1.商品条码 like '%" & Cells(8, 2) & "%') " _
)
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Users\Administrator\Desktop\孩子王销售分析\数据.mdb;DefaultDir=C:\Users\Administrator\Desktop\孩子王销售分析;Driv" _
), Array("erId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("查询来自 MS Access Database")
.Name = "查询来自 MS Access Database"
.Description = ""
End With
ActiveWorkbook.Connections("查询来自 MS Access Database").Refresh
Range("A11").Select
ActiveSheet.PivotTables("数据透视表2").PivotCache.Refresh
End Sub
我把上述C:\Users\Administrator\Desktop\孩子王销售分析替换成wb.path但总报错,我替换是
ODBC;DSN=MS Access Database;DBQ=C:\Users\Administrator\Desktop\孩子王销售分析\数据.mdb;DefaultDir=C:\Users\Administrator\Desktop\孩子王销售分析;Driv" _
), Array("erId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"))
替换为
ODBC;DSN=MS Access Database;DBQ=wb.path\数据.mdb;DefaultDir=wb.path;Driv" _
), Array("erId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"))
把`C:\Users\Administrator\Desktop\孩子王销售分析", _替换成
`wb.path" , _
报错,没有系统学习过,请各位帮我指点一二。
|
|