我正在尝试在 VBA 中运行 SQL 查询,但出现错误:
对象关闭时不允许操作。
该查询在 SQL 中完美运行,但我还没有设法在 VBA 代码中翻译它。错误位于WS.Range("B20").CopyFromRecordset
rs 行。
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
连接函数、执行结果和显示结果是否设置正确?
特别是对于复杂的查询,考虑将 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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句