i当前有一个连接到VBA中SQL数据库的代码。数据正如我想要的那样正确填充了数据,但是我想知道是否有一种方法可以凝结下面的代码。我有4个不同的列和26行,我觉得如果我走这条路线,我会浪费很多时间。我希望该范围从C20:C45和从H20:H45显示的结果显示。有人可以帮忙吗?谢谢!
' Open the connection and execute data for WFTEs.
Set rs = conn.Execute("SELECT sum(Hours)/80 FROM payroll2015_rif WHERE DepartmentCode = '" & Range("$E$6") & "' AND payperiod = '" & Range("C20") & "' and paycode IN ('REG1', 'REG2');")
' Transfer result.
Sheets(2).Range("$H20").CopyFromRecordset rs
' Close the recordset
rs.Close
' Open the connection and execute data for WFTEs.
Set rs = conn.Execute("SELECT sum(Hours)/80 FROM payroll2015_rif WHERE DepartmentCode = '" & Range("$E$6") & "' AND payperiod = '" & Range("C21") & "' and paycode IN ('REG1', 'REG2');")
' Transfer result.
Sheets(2).Range("$H21").CopyFromRecordset rs
' Close the recordset
rs.Close
这样的事情可以解决问题吗?基本上,我将从Excel范围中获取相关值,并将它们放入VBA中的数组,通过循环浏览变量创建结果数组,然后将数组置于Excel范围中。
注意,我没有包含ADODB连接和记录集声明,我认为您在代码中有某个地方。
Sub myLoop()
Dim dept As String
Dim payperiod As Variant
Dim result As Variant
Dim i As Integer
dept = Range("E6")
payperiod = Range("C20:C45")
ReDim result(1 To UBound(payperiod), 1 To 1)
'don't forget to set your ADODB stuff somewhere
For i = 1 To UBound(payperiod)
Set rs = conn.Execute("SELECT sum(Hours)/80 FROM payroll2015_rif WHERE DepartmentCode = '" & dept & "' AND payperiod = '" & payperiod(i,1) & "' and paycode IN ('REG1', 'REG2');")
result(i, 1) = rs(0)
rs.Close
Next i
Range("H20:H45") = result
End Sub
让我们分解一下并尝试一些伪代码。首先,我们将解决您问题的SQL部分:
SELECT payperiod
, calc = SUM(Hours) / 80
FROM payroll2015_rif
WHERE DepartmentCode = <DepartmentCode>
AND payperiod IN (<PayPeriods>)
AND paycode IN ('REG1', 'REG2')
GROUP BY payperiod
现在我们需要替换包含在&lt中的任何东西;>带有来自Excel的值。您已经找到了 DepartmentCode ,因此我们将在薪水期间工作。SQL期望将'Period 1', 'Period 2', 'Period 3'
之类的内容传递给IN
子句。
一种非常幼稚的方法是:
Set strValues = Range("C21") & ', ' & Range("C22") & ', ' & Range("C23")
这很乏味,但很容易被重构为循环之类的东西。将其与您已经拥有的示例一起放在一起,应该给您想要的东西。