sql - 对 Excel 单元格的 SQL 脚本引用

Question

Sub SQL()
'Clearing the existing cells
Application.DisplayAlerts = False
Sheet1.Range("A1:D10000").SpecialCells(xlCellTypeVisible).Clear
Application.DisplayAlerts = True

'Setting up the connection
Dim sSQLQry As String
Dim ReturnArray
Dim Conn As New ADODB.Connection
Dim m As New ADODB.Recordset
Dim DBPath As String, sconnect As String

sconnect = "Provider=SQLOLEDB; Data Source=backoffice.db.mid.dom,4161; Initial Catalog=BackOffice; Integrated Security=SSPI;"

Conn.Open sconnect

"'SQL Script
'DB1 = ThisWorkbook.Sheets("Import to SQL").Range(c2").Value
    sSQLString = ThisWorkbook.Sheets("Scripts").Range("C2:C3").Value
    m.Open sSQLString, Conn

'Paste Date and Close Connection
    Sheet1.Range("A21").CopyFromRecordset m
    m.Close

'Set/name the column header
Sheet1.Cells(20, 1) = "ProductCode"
Sheet1.Cells(20, 2) = "Nr of Trades"
Sheet1.Cells(20, 3) = "Trade_date"
Sheet1.Cells(20, 4) = "Date_Retrieved"

Conn.Close

End Sub

大家好， 当前放置在 1 个单元格 (C2) 中的 SQL 脚本有问题。我想要做的是传播脚本，以便每行脚本有 1 个单元格。当我将脚本分成 2 个单元格并且它位于 C2 和 C3 中时，VBA 会给出错误 3001。

C2 ="Select ProductCode, FORMAT(count(distinct(tradeid)),'#,#') as Nr_Trades, TradeDate, GETDATE() From BackOffice.dbo.trades
where 1 = 1 
and TradeDate ="&E2&"group by ProductCode, TradeDate"
C3 ="order by count(distinct(tradeid)) asc"

标签： sqlexcelvba