Updating an SQL query using vba 2

[djburnheim]

I have a spreadsheet that gets data from a SQL database. Each week I need to change the dates in the querys to update the spreadsheet. I'm trying to write a macro that will take the dates from a form and update the querys automatically. Below is what I have so far...

Dim StartDate As Date
Dim EndDate As Date

StartDate = dtpWeekStart.Value - 1
EndDate = dtpWeekStart.Value + 5

Range("N4&quot.Select
With Selection.QueryTable
.Connection = _
"ODBC;DSN=SQLServer;UID=User;PWD=password;APP=Microsoft® Query;WSID=Workstation;DATABASE=Master"
.CommandText = Array( _
"SELECT Count(*) AS 'Cases Created'" & Chr(13) & "" & Chr(10) & "FROM Master.dbo.SW_HD_CASE SW_HD_CASE" & Chr(13) & "" & Chr(10) & "WHERE (SW_HD_CASE.swDateCreated Between {ts '2003-01-12 00:00:00'} And {ts '2003-01-18 00:00:00'}) AND (SW_HD_CASE.swCreated" _
, "By='john.doh')&quot
.Refresh BackgroundQuery:=False
End With

I need to be able to insert my "StartDate" and "EndDate" into the CommandText array and then this needs to repeat for another 17 people.

 

[gusset]

i did this in *vb* recently and used the Replace function, replacing a dummy string in the Sql code with the userdata.

however, i don't think that vba has this function; someone else can confirm this. if it doesn't, you can write a short function to do the job. but there's no need. someone will have done it before and posted it on the web. its something that every programmer will have needed at some point.

hope this helps

g

 

[djburnheim]

I have managed to do this using the following code...I have it working in excel 2000 but am still working problems in 97. Basically I declared a variable for the query as a string and then defined the string using the values as required then I just used a For & Next function, see below.

***code***

Dim OpenQuery As String
Dim ClosedQuery As String
Dim StartDate
Dim EndDate
Dim Row

StartDate = Format(Cells(4, 4).Value - 1, "yyyy-mm-dd HH:MM:SS&quot
EndDate = Format(Cells(4, 4).Value + 5, "yyyy-mm-dd HH:MM:SS&quot
Row = 6

For i = 1 To 18

OpenQuery = "SELECT Count(*) AS 'Cases Created' FROM Vantive.dbo.SW_HD_CASE SW_HD_CASE WHERE (SW_HD_CASE.swDateCreated Between {ts '" & StartDate & "'} And {ts '" & EndDate & "'}) AND (SW_HD_CASE.swCreatedBy='" & Cells(Row, 3) & "')"

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=SQL Server;UID=user;PWD=password;APP=Microsoft® Query;DATABASE=database" _
, Destination:=Cells(Row, 16))
.CommandText = Array(OpenQuery)
.Name = "Open"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Next i