VBA SQL
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=<server>;" & _
"Initial Catalog=<database>;" & _
"Integrated Security=SSPI;"
' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
' Open the connection and execute.
conn.Open sConnString
' Note the single quotes around the SQL parameters
my_query = "select * from db.table where date between '@start_date' and '@end_date'"
' Left this bit in for example, obviously it may not always apply.
start_date = Worksheets("settings").range("A2").Value
num_weeks = Worksheets("settings").range("B2").Value
end_date = DateAdd("d", (num_weeks * 7) - 1, start_date)
' Will need to keep this date formatting, if you're using dates
start_date = Format(start_date, "yyyy-mm-dd")
end_date = Format(end_date, "yyyy-mm-dd")
' Replace the parameters
my_query = Replace(my_query, "@start_date", start_date)
my_query = Replace(my_query, "@end_date", end_date)
' Spit out the query. Optional for troubleshooting.
Worksheets("settings").range("A25").Value = my_query
Set rs = conn.Execute(my_query)
Worksheets("sql_result").Cells.Clear
' Check we have data.
If Not rs.EOF Then
For intColIndex = 0 To rs.Fields.Count - 1
Worksheets("sql_result").range("A1").Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
' Transfer result.
Sheets("sql_result").range("A2").CopyFromRecordset rs
' Close the recordset
rs.Close
Else
MsgBox "Error: No records returned.", vbCritical
End If
' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing