|
我想请教个问题,在VBA中执行如下语句报错是什么原因?下面的SQL语句是没有错的,因为我执行语句输出后还放在了SQL SERVER里执行过,都可以通过,不知道为什么在EXCEL里不能通过,
能帮助看下么?
Sub 按钮1_Click()
Set conn = CreateObject("adodb.connection")
Dim sql As String
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName 'ThisWorkbook.FullName当前工作簿的名字 ActiveSheet.Name当前工作表的名字
sql = "DECLARE @cmdText VARCHAR(8000); " _
& "DECLARE @tmpSql VARCHAR(8000); " _
& "SET @cmdText = 'SELECT CONVERT(VARCHAR(10), 产品编码, 120) AS 产品编码,' + CHAR(10); " _
& "SELECT @cmdText = @cmdText + ' CASE 业务类型 WHEN ''' + 业务类型 + ''' THEN SUM(数量) ELSE 0 END AS ''' + 业务类型+ ''',' + CHAR(10) " _
& "FROM (SELECT DISTINCT 业务类型 FROM (select 客户名称,产品编码,数量, CONVERT(varchar(100), 日期,110)+业务类型 as 业务类型 from 数据库) as 数据库 ) T " _
& "SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2) " _
& "SET @cmdText = @cmdText + ' FROM (select 客户名称,产品编码,数量, CONVERT(varchar(100), 日期,110)+业务类型 as 业务类型 from 数据库) as 数据库 GROUP BY 产品编码, 业务类型 '; " _
& "SET @tmpSql ='SELECT 产品编码,' + CHAR(10); " _
& "SELECT @tmpSql = @tmpSql + ' ISNULL(SUM(' + 业务类型 + '), 0) AS ''' + 业务类型 + ''',' + CHAR(10)" _
& "FROM (SELECT DISTINCT 业务类型 FROM (select 客户名称,产品编码,数量, CONVERT(varchar(100), 日期,110)+业务类型 as 业务类型 from 数据库) as 数据库 ) T " _
& "SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2) + ' FROM (' + CHAR(10); "
Cells(1, 1) = sql
[c1].CopyFromRecordset conn.Execute(sql)
conn.Close
End Sub
|
|