I have a report coming from ms query. In order to get the correct data
into this report I need to change the criteria on a daily basis. Is
there any way to change this from the sheet the report is on. I'm new
to this and think it can be done using vb. But I need some help in
getting started. Any help would be greatly appreciated.

you don't really need to use MS Query. I'm assuming that your SQL query is
passed to a database and that a table is then populated?
Its easier IMHO to use ADO and get data that way.

so, in a new standard modue, add the following code, and set a
reference(Tolls/References) to the Microsoft ActiVe Data Objects Library 2.7
or later...

The code is pared down enormously, but the idea is to get you started

Option Explicit
Sub GetUserSQL()
Dim SQL As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConn As String
Dim index As Long
Dim OK As Boolean

sConn = "PROVIDER=MSDataShape;Data PROVIDER=MSDASQL;"
sConn = sConn & "driver={SQL Server};"
sConn = sConn & "server=BHW01;uid=;pwd=;database=ASQA_Projects ;"

Set conn = New ADODB.Connection

With conn
.ConnectionString = sConn
.Open
End With

SQL = "select top 100 * from Projects"

Set rs = New ADODB.Recordset

rs.Open SQL, conn, adOpenDynamic, adLockOptimistic

If rs.RecordCount 500 Then
MsgBox "Too many rows (500) "
ElseIf rs.RecordCount = 0 Then
MsgBox "Nothing returned"
ElseIf rs.Fields.Count 255 Then
MsgBox "Too many fields"
Else
OK = True
End If

If OK Then
Range("B2").CopyFromRecordset rs
With Range("B2").Resize(rs.RecordCount, rs.Fields.Count)
.Interior.Color = 16777164 'pale blue
End With

End If

rs.Close
conn.Close

Set rs = Nothing
Set conn = Nothing

End Sub

this works as is. But you need to change the server and database names and
also alter the SQL text appropriately...





"sd" wrote:

I have a report coming from ms query. In order to get the correct data
into this report I need to change the criteria on a daily basis. Is
there any way to change this from the sheet the report is on. I'm new
to this and think it can be done using vb. But I need some help in
getting started. Any help would be greatly appreciated.