thanks so much for the reply.

I have a couple of questions:

1) each sheet has a query written in MSQuery.
They are not graphical, as it could not display them graphically.
the WHERE clause on all queries has this key line:

AND x.DSTI_PDS_PROJMANAGER = <manager name goes here

I can't use a parameter ( ? symbol) as it says they cant be used on
queries that cant be displayed graphically.

previoulsy I tried building the SQL using strings:

For Each rn In .Range(.[A2], .[A2].End(xlDown))
sConn =
"ODBC;DSN=NIKULIVE;UID=arsuser;PWD=arsrecon1;SERVE R=nikulive.dstintl.com;"
sSQL = "SELECT DSTI_PROJ_REF, DSTI_PDS_PROJMANAGER "
sSQL = sSQL & "FROM niku.ODF_CA_PROJECT "
sSQL = sSQL & "WHERE DSTI_PDS_PROJMANAGER = '" & rn.Value &
"'"

With Sheet4.QueryTables(1)
..Connection = sConn
..CommandText = sSQL
..Refresh BackgroundQuery:=False
End With
Next


I had problems getting this working as Sheet4 that I was testing on did
not have a query in there previoulsy. Someone suggested that I have to
ADD a Qurty table to it first, but I'm not sure how to do this.
I prefer your method - but this seems to be a problem without being
able to create parameters..

any ideas?


--
matpj
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=472083