"Abdul" wrote ...

Following is waht I have to query from another sheet in
the same workbook

Whenever I use direct query it works while I use code it
fails... (Data2004 is my active workbbok and i want to
query data from sheet("M 01") to sheet("TempData")

I am prefering this way since i have to use several
criteria ( too complicated to use filter)

With Selection.QueryTable
.Connection = _
"ODBC;DSN=Excel Files;DBQ=D:\My " _
Documents\Data2004.xls;DefaultDir=D:\My
Documents;DriverId=790;MaxBufferSize=2048;PageTime out=5;"
.CommandText = Array( _
"SELECT * FROM `D:\My Documents\" _
& "Data2004`.`'M 01$'` `'M 01$'`" & Chr(13)& ""& Chr(10) _
& "WHERE (`'M 01$'`.A='005G') AND (`'M 01$'`.b='N')" _
& " AND (`'M 01$'`.SR='S') AND (`'M 01$'`.IDate={ts '"
& " IDt1 & " 00:00:00'}" _
& "AND `'M 01$'`.IDate<={ts '" & IDt2 & "00:00:00'}")


That MS Query wizard sure does write ugly SQL <g. Try the following
(untested):

Dim strSql As String
strSql = "" & _
"SELECT * FROM ['M 01$']" & _
" WHERE A = '005G'" & _
" AND b = 'N'" & _
" AND SR = 'S'" & _
" AND IDate = #" & Format$(IDt1, "yyyy-mm-dd 00:00:00") & "#" & _
" AND IDate <= #" & Format$(IDt2, "yyyy-mm-dd 00:00:00") & "#"
..CommandText = strSql

How can I properly reference the Active workBook
sheet M 01 Instead of referencing the whole directory?

The folder/filename has been specified in the connection string and
therefore is best omitted.

Jamie.

--