我正在嘗試在 VBA 中運行 SQL 查詢，但出現錯誤：
物件關閉時不允許操作。
該查詢在 SQL 中完美運行，但我還沒有設法在 VBA 代碼中翻譯它。錯誤位于 WS.Range("B20").CopyFromRecordsetrs 行。
Private Sub UpdateButton_Click()
Dim oCon As ADODB.Connection, oCmd As Object
Dim rs As Object, SQL_1 As String
Dim WS As Worksheet, n As Long
'GET DATES
Dim StartDate As String, EndDate As String
With ThisWorkbook.Sheets("A&B Sankey")
StartDate = Format(.Range("R2").Value, "yyyy-mm-dd hh:MM:ss")
EndDate = Format(.Range("T2").Value, "yyyy-mm-dd hh:MM:ss")
End With
'CONNECT FUNCTION
Set oCon = DbConnect
Set oCmd = CreateObject("ADODB.Command")
oCmd.CommandTimeout = 0
oCmd.ActiveConnection = oCon
SQL_1 = _
" DECLARE @StartDate nvarchar(20)" & vbCrLf & _
" DECLARE @EndDate nvarchar(20)" & vbCrLf & _
" SET @StartDate ='" & StartDate & "'" & vbCrLf & _
" SET @EndDate ='" & EndDate & "'" & vbCrLf
SQL_1 = SQL_1 & _
" SELECT x.*, y.* INTO #temp1 FROM " & vbCrLf & _
" (SELECT [Charge_slabs_A]=count(CASE WHEN f.[FURNACE_NUMBER] =1 then f.[slab_weight] else null end)," & vbCrLf & _
"[Slab_weight_Discharged_A]=1000*avg(c.[fa_weight])," & vbCrLf & _
"[Avg_Charg_Temp_A]=avg(case when b.[Furnace]='A' then b.[charge_temperature]else null end)" & vbCrLf & _
"" & vbCrLf
SQL_1 = SQL_1 & _
" FROM fix.dbo.Fce_HD_Hourly a " & vbCrLf & _
" LEFT JOIN ALPHADB.dbo.Mill_Temp_Aims as b on DATEADD(hour, DATEDIFF(hour, 0, b.[charge_time]), 0) = a.[_TimeStamp]" & vbCrLf & _
" LEFT JOIN ALPHADB.dbo.reheats_hourly_data c ON c.[start_time]= a.[_timestamp]" & vbCrLf & _
" LEFT JOIN alphadb.dbo.HFNCPDI f on f.[counter] = b.[mill_counter]" & vbCrLf & _
" WHERE a.[_TimeStamp] between @StartDate and @EndDate and b.[charge_time] between @StartDate and @EndDate " & vbCrLf & _
" GROUP BY a.[_TimeStamp]) as x " & vbCrLf & _
" FULL OUTER JOIN (SELECT [Avg_DisCharg_Temp_B]=avg(CASE WHEN b.[FURNACE] ='B' then convert(real,isnull (b.[ave_disch_temp],'0')) else null end),[Time]= a.[_TimeStamp] " & vbCrLf & _
" FROM fix.dbo.Fce_HD_Hourly as a" & vbCrLf & _
" LEFT JOIN Mill_Temp_Aims as b on DATEADD(hour, DATEDIFF(hour, 0, b.[discharge_time]), 0) = a.[_TimeStamp]" & vbCrLf & _
" WHERE a.[_TimeStamp] BETWEEN CONVERT(datetime, @StartDate , 120) AND CONVERT(datetime,@EndDate , 120) and b.[discharge_time] BETWEEN CONVERT(datetime, @StartDate , 120) AND CONVERT(datetime, @EndDate , 120) " & vbCrLf & _
" GROUP BY a.[_TimeStamp]) AS y ON y.[Time] = x.[_TimeStamp]" & vbCrLf & _
" SELECT [Charge_slabs_A],[Slab_weight_Discharged_A],[Avg_Charg_Temp_A],[Avg_DisCharg_Temp_B]" & vbCrLf & _
" FROM #temp1 DROP TABLE #temp1"
'EXECUTE RESULT
oCmd.CommandText = SQL_1
Set rs = oCmd.Execute
'SHOW RESULT
Set WS = ThisWorkbook.Sheets("-Input Data-")
WS.Range("B20:CC20000").ClearContents
WS.Range("B20").CopyFromRecordset rs <-------------------ERROR
'CLOSE
oCon.Close
MsgBox "Result written to " & WS.Name & _
"For " & StartDate & "-" & EndDate, vbInformation, "Finished"
End Sub
Function DbConnect() As ADODB.Connection
Dim sConn As String
sConn = "driver={SQL server}; SERVER=; " & _
"UID=; PWD=; DATABASE=;"
Set DbConnect = CreateObject("ADODB.Connection")
DbConnect.Open sConn
End Function
連接函式、執行結果和顯示結果是否設定正確？
uj5u.com熱心網友回復：
特別是對于復雜的查詢，考慮將 SQL 和 VBA 分開，并將 SQL 引數化為帶有 qmark 占位符的預處理陳述句。ADO 支持使用您已經使用的ADO 命令物件進行引數化！這使您可以避免任何DECLARE混亂甚至危險的串聯。此外，由于引數化，故意使用日期型別并避免任何FORMAT或CONVERT需要。您還可以#temp1使用單個陳述句來避免：
SQL （在 Excel 單元格中另存為 .sql 或字串）
下面的查詢使用更多資訊別名，并使用AS運算子進行列別名。此外，所有系統命令都一致地大寫以提高可讀性。請注意 qmarks ( ?) 用于引數而不是 @ 變數。請測驗查詢并根據需要進行調整。
SELECT x.[Charge_slabs_A], x.[Slab_weight_Discharged_A],
x.[AVG_Charg_Temp_A], y.[AVG_DisCharg_Temp_B]
FROM
(SELECT COUNT(CASE
WHEN h.[FURNACE_NUMBER]=1
THEN h.[slab_weight]
ELSE NULL
END) AS [Charge_slabs_A],
1000 * AVG(r.[fa_weight]) AS [Slab_weight_Discharged_A],
AVG(CASE
WHEN m.[Furnace]='A'
THEN m.[charge_temperature]
ELSE NULL
END) AS [AVG_Charg_Temp_A]
FROM fix.dbo.Fce_HD_Hourly AS f
LEFT JOIN ALPHADm.dbo.Mill_Temp_Aims AS m
ON DATEADD(HOUR, DATEDIFF(HOUR, 0, m.[charge_time]), 0) = f.[_TimeStamp]
LEFT JOIN ALPHADm.dbo.reheats_hourly_data AS r
ON r.[start_time]= f.[_timestamp]
LEFT JOIN alphadm.dbo.HFNCPDI h
ON h.[counter] = m.[mill_counter]
WHERE f.[_TimeStamp] BETWEEN ? AND ?
AND m.[charge_time] BETWEEN ? AND ?
GROUP BY f.[_TimeStamp]
) AS x
FULL OUTER JOIN
(SELECT AVG(CASE
WHEN m.[FURNACE] ='B'
THEN convert(real,isnull (m.[ave_disch_temp],'0'))
ELSE NULL
END) AS [AVG_DisCharg_Temp_B],
f.[_TimeStamp] AS [Time]
FROM fix.dbo.Fce_HD_Hourly AS f
LEFT JOIN Mill_Temp_Aims AS m
ON DATEADD(HOUR, DATEDIFF(HOUR, 0, m.[discharge_time]), 0) = f.[_TimeStamp]
WHERE f.[_TimeStamp] BETWEEN ? AND ?
AND m.[discharge_time] BETWEEN ? AND ?
GROUP BY f.[_TimeStamp]
) AS y
ON y.[Time] = x.[_TimeStamp]
VBA （讀取上述查詢并系結日期引數）
Private Sub UpdateButton_Click()
Dim oCon As ADODB.Connection, oCmd As ADODB.Command
Dim rs As Object, SQL_1 As String
Dim WS As Worksheet, n As Long
'GET DATES
Dim StartDate As Date, EndDate As Date
With ThisWorkbook.Sheets("A&B Sankey")
StartDate = CDate(.Range("R2").Value)
EndDate = CDate(.Range("T2").Value)
End With
'CONNECT FUNCTION
Set oCon = DbConnect
Set oCmd = CreateObject("ADODB.Command")
oCmd.CommandTimeout = 0
oCmd.ActiveConnection = oCon
'READ IN SQL
With CreateObject("Scripting.FileSystemObject")
SQL_1 = .OpenTextFile("C:\path\to\my\SQL\Query.sql", 1).readall
End With
' SQL_1 = ThisWorkbook.Sheets("MySQLSheet").Range("A1")
'EXECUTE RESULT
With oCmd
.CommandText = SQL_1
' BIND ? PARAMETERS IN SQL (USING adDate TYPES)
For n = 1 to 4
.Parameters.Append .CreateParameter("startdateparam" & n, adDate, adParamInput, , StartDate)
.Parameters.Append .CreateParameter("enddateparam" & n, adDate, adParamInput, , EndDate)
Next n
' CREATE RECORDSET
Set rs = .Execute
End With
'SHOW RESULT
With ThisWorkbook.Sheets("-Input Data-")
.Range("B20:CC20000").ClearContents
.Range("B20").CopyFromRecordset rs
End With
'CLOSE
MsgBox "Result written to " & WS.Name & _
"For " & StartDate & "-" & EndDate, vbInformation, "Finished"
rs.Close: oCon.Close
Set rs = Nothing: Set oCmd = Nothing: Set oCon = Nothing
End Sub
Function DbConnect() As ADODB.Connection
Dim sConn As String
sConn = "Driver={SQL Server}; SERVER=; " & _
"UID=; PWD=; DATABASE=;"
Set DbConnect = CreateObject("ADODB.Connection")
DbConnect.Open sConn
End Function
uj5u.com熱心網友回復：
oCmd.Execute 只能執行單個 SQL 命令 ( executes the query, SQL statement, or stored procedure specified in the CommandText )；您正在嘗試執行一批命令：'declare, ... set, ... select'。使用此方法只能提交一個命令。一種選擇是將所有這些批處理放在 SQLServer 中的存盤程序中并從 VBA 呼叫它，或者洗掉宣告/設定部分，并用 ADO 命令引數替換它們。
示例查詢（您可以輕松適應您的查詢）：
Public Sub DoIt()
Dim conn As ADODB.Connection, _
cmd As ADODB.Command, _
rs As ADODB.Recordset, _
parmStartDate As ADODB.Parameter, _
parmEndDate As ADODB.Parameter, _
strSql As String
Set conn = New ADODB.Connection
With conn
.ConnectionString = "driver={SQL server}; SERVER=MYSSINSTANCE; UID=; PWD=; DATABASE=;"
.Open
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = conn
.CommandText = "select NumDays=datediff(day, ?, ?)"
.CommandType = adCmdText
Set parmStartDate = .CreateParameter("StartDate", adDBTimeStamp, adParamInput)
parmStartDate.Value = "2020-01-01"
.Parameters.Append parmStartDate
Set parmEndDate = .CreateParameter("EndDate", adDBTimeStamp, adParamInput)
parmEndDate.Value = "2020-03-05"
.Parameters.Append parmEndDate
Set rs = .Execute()
Debug.Print rs!NumDays
rs.Close
Set rs = Nothing
End With
.Close
End With
Set conn = Nothing
End Sub
uj5u.com熱心網友回復：
在代碼中的某個時刻，我相信您需要通過以下方式打開記錄集物件：
rs.Open
正如 Parfait 在評論中提到的那樣，這不應該是必需的，因為 Execute 方法應該打開 rs（打開 rs 的替代方法的參考：https ://docs.microsoft.com/en-us/troubleshoot/sql/connect/open-ado -連接記錄集物件）
另外我想知道您是否需要明確說明 rs 是什么型別的物件：
Dim Rs As adodb.Recordset
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/464239.html
上一篇：在多列上將垂直轉換為水平