I currently have a code that is connecting to SQL database in VBA. The data is populating correctly. However, I was wondering if there is a way to condense the code below.
I have 4 different columns and 26 rows and I feel like if I go this route, I will be wasting a lot of time. I want the range to go from C20:C45
and the results to show from cells H20:H45
.
Sub ConnectSqlServer()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim wks As Worksheet, xRow As Long
' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=chocnt-285;" & _
"Initial Catalog=trackerfy2015;" & _
"Integrated Security=SSPI;"
' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
' 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
' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub