Hi All,

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)

Is there better way than this?

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


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'}")

..Refresh BackgroundQuery:=False

End With


Thanks

Abdul