Query from another sheet in same Workbook
 

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

Query from another sheet in same Workbook
 

This shows an example I had from querying w/o selection or activesheet


Code:
--------------------
connstring = "ODBC;DSN=maximo;Database=maximo;UID=" & uid & ";PWD=" & pwd & ";"
sqlstring = "SELECT DISTINCT EQUIPMENTSPEC.EQNUM, EQUIPMENTSPEC.ASSETATTRID, EQUIPMENTSPEC.ALNVALUE FROM MAXIMO.EQUIPMENTSPEC EQUIPMENTSPEC WHERE" & tag

With Worksheets("Data").QueryTables.Add(Connection:=con nstring, Destination:=Worksheets("Data").Range("B1"), Sql:=sqlstring)
.Name = "Maximo Query for Data Worksheet"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False

End With
--------------------


--
ob3ron02
------------------------------------------------------------------------
ob3ron02's Profile: http://www.excelforum.com/member.php...o&userid=15450
View this thread: http://www.excelforum.com/showthread...hreadid=274464

Query from another sheet in same Workbook
 

Hi Abdul,

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

Is there better way than this?

Personally, I would use an AdvancedFilter instead of a QueryTable for this. It seems a fairly simple Criteria Range.

A B C D E
1 A b SR lDate lDate
2 005G N S =12345 <=12356
3

So you'd populate D2 and E2 with your dates:
Range("D2").Value = "=" & CLng(lDt1)
Range("E2").Value = "<=" & CLng(lDt1)

Then do a Range.AdvancedFilter (record it to get the syntax correct).

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Query from another sheet in same Workbook
 

Thanks Stephen Bullen

It is not what i want.. I have several criteria to look
in my actual file, more than ten. I was just giveing a
kelton in my post

Thanks
Abdul

-----Original Message-----
Hi Abdul,

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

Is there better way than this?

Personally, I would use an AdvancedFilter instead of a
QueryTable for this. It seems a fairly simple Criteria
Range.

A B C D E
1 A b SR lDate lDate
2 005G N S =12345 <=12356
3

So you'd populate D2 and E2 with your dates:
Range("D2").Value = "=" & CLng(lDt1)
Range("E2").Value = "<=" & CLng(lDt1)

Then do a Range.AdvancedFilter (record it to get the
syntax correct).

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk


.

Query from another sheet in same Workbook
 

"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.

--

Query from another sheet in same Workbook
 

Hi Abdul,

It is not what i want.. I have several criteria to look
in my actual file, more than ten. I was just giveing a
kelton in my post

OK, but the advanced filter criteria ranges can be very complex and can
handle most things that SQL functions can do.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk