Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Using a SQL like query to query an excel sheet | Excel Worksheet Functions | |||
Process data in a sheet within workbook using SQL query? | Excel Programming |