Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Query
Hello!,
I have the following query in excel but it only fetch data from two sheets only?! Also it do not referesh the data unless i do it manually. Is ther a better way of doing this? Sub QueryTest() Dim sCmd() Dim dt1 As Date Dim dt2 As Date Dim i% ReDim sCmd(34) sCmd(0) = _ " SELECT " & _ " FORMAT(M1.ORDER_DATE,'mmm') AS Month, M1.NAME, M1.CUSTOMER_NAME, M1.BU, M1.ITEM, SUM(M1.GROSS_AMOUNT) as " & _ "GROSS_AMOUNT" & vbCrLf & _ " FROM `C:\Sales\Jan 09 Sales.xlsx`.`Sales$` M1" & vbCrLf sCmd(1) = _ " GROUP BY FORMAT(M1.ORDER_DATE,'MMM'), M1.CUSTOMER_NAME, M1.BU, " & _ " M1.ITEM, M1.NAME" & vbCrLf sCmd(2) = " Union All" & vbCrLf sCmd(3) = _ " SELECT " & _ " FORMAT(m2.ORDER_DATE,'mmm') AS Month, m2.NAME, m2.CUSTOMER_NAME, m2.BU, m2.ITEM, SUM(m2.GROSS_AMOUNT) as " & _ "GROSS_AMOUNT" & vbCrLf & _ " FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m2" & vbCrLf sCmd(4) = _ " GROUP BY FORMAT(m2.ORDER_DATE,'MMM'), m2.CUSTOMER_NAME, m2.BU, " & _ " m2.ITEM, m2.NAME" & vbCrLf sCmd(5) = " Union All" & vbCrLf sCmd(6) = _ " SELECT " & _ " FORMAT(m3.ORDER_DATE,'mmm') AS Month, m3.NAME, m3.CUSTOMER_NAME, m3.BU, m3.ITEM, SUM(m3.GROSS_AMOUNT) as " & _ "GROSS_AMOUNT" & vbCrLf & _ " FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m3" & vbCrLf sCmd(7) = _ " GROUP BY FORMAT(m3.ORDER_DATE,'MMM'), m3.CUSTOMER_NAME, m3.BU, " & _ " m3.ITEM, m3.NAME" & vbCrLf sCmd(8) = " Union All" & vbCrLf sCmd(9) = _ " SELECT " & _ " FORMAT(m4.ORDER_DATE,'mmm') AS Month, m4.NAME, m4.CUSTOMER_NAME, m4.BU, m4.ITEM, SUM(m4.GROSS_AMOUNT) as " & _ "GROSS_AMOUNT" & vbCrLf & _ " FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m4" & vbCrLf sCmd(10) = _ " GROUP BY FORMAT(m4.ORDER_DATE,'MMM'), m4.CUSTOMER_NAME, m4.BU, " & _ " m4.ITEM, m4.NAME" & vbCrLf sCmd(11) = " Union All" & vbCrLf sCmd(12) = _ " SELECT " & _ " FORMAT(m5.ORDER_DATE,'mmm') AS Month, m5.NAME, m5.CUSTOMER_NAME, m5.BU, m5.ITEM, SUM(m5.GROSS_AMOUNT) as " & _ "GROSS_AMOUNT" & vbCrLf & _ " FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m5" & vbCrLf sCmd(13) = _ " GROUP BY FORMAT(m5.ORDER_DATE,'MMM'), m5.CUSTOMER_NAME, m5.BU, " & _ " m5.ITEM, m5.NAME" & vbCrLf sCmd(14) = " Union All" & vbCrLf sCmd(15) = _ " SELECT " & _ " FORMAT(m6.ORDER_DATE,'mmm') AS Month, m6.NAME, m6.CUSTOMER_NAME, m6.BU, m6.ITEM, SUM(m6.GROSS_AMOUNT) as " & _ "GROSS_AMOUNT" & vbCrLf & _ " FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m6" & vbCrLf sCmd(16) = _ " GROUP BY FORMAT(m6.ORDER_DATE,'MMM'), m6.CUSTOMER_NAME, m6.BU, " & _ " m6.ITEM, m6.NAME" & vbCrLf sCmd(17) = " Union All" & vbCrLf sCmd(18) = _ " SELECT " & _ " FORMAT(m7.ORDER_DATE,'mmm') AS Month, m7.NAME, m7.CUSTOMER_NAME, m7.BU, m7.ITEM, SUM(m7.GROSS_AMOUNT) as " & _ "GROSS_AMOUNT" & vbCrLf & _ " FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m7" & vbCrLf sCmd(19) = _ " GROUP BY FORMAT(m7.ORDER_DATE,'MMM'), m7.CUSTOMER_NAME, m7.BU, " & _ " m7.ITEM, m7.NAME" & vbCrLf sCmd(20) = " Union All" & vbCrLf sCmd(21) = _ " SELECT " & _ " FORMAT(m8.ORDER_DATE,'mmm') AS Month, m8.NAME, m8.CUSTOMER_NAME, m8.BU, m8.ITEM, SUM(m8.GROSS_AMOUNT) as " & _ "GROSS_AMOUNT" & vbCrLf & _ " FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m8" & vbCrLf sCmd(22) = _ " GROUP BY FORMAT(m8.ORDER_DATE,'MMM'), m8.CUSTOMER_NAME, m8.BU, " & _ " m8.ITEM, m8.NAME" & vbCrLf sCmd(23) = " Union All" & vbCrLf sCmd(24) = _ " SELECT " & _ " FORMAT(m9.ORDER_DATE,'mmm') AS Month, m9.NAME, m9.CUSTOMER_NAME, m9.BU, m9.ITEM, SUM(m9.GROSS_AMOUNT) as " & _ "GROSS_AMOUNT" & vbCrLf & _ " FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m9" & vbCrLf sCmd(25) = _ " GROUP BY FORMAT(m9.ORDER_DATE,'MMM'), m9.CUSTOMER_NAME, m9.BU, " & _ " m9.ITEM,m9.NAME" & vbCrLf sCmd(26) = " Union All" & vbCrLf sCmd(27) = _ " SELECT " & _ " FORMAT(m10.ORDER_DATE,'mmm') AS Month, m10.NAME, m10.CUSTOMER_NAME, m10.BU, m10.ITEM, SUM(m10.GROSS_AMOUNT) as " & _ "GROSS_AMOUNT" & vbCrLf & _ " FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m10" & vbCrLf sCmd(28) = _ " GROUP BY FORMAT(m10.ORDER_DATE,'MMM'), m10.CUSTOMER_NAME, m10.BU, " & _ " m10.ITEM,m10.NAME" & vbCrLf sCmd(29) = " Union All" & vbCrLf sCmd(30) = _ " SELECT " & _ " FORMAT(m11.ORDER_DATE,'mmm') AS Month, m11.NAME, m11.CUSTOMER_NAME, m11.BU, m11.ITEM, SUM(m11.GROSS_AMOUNT) as " & _ "GROSS_AMOUNT" & vbCrLf & _ " FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m11" & vbCrLf sCmd(31) = _ " GROUP BY FORMAT(m11.ORDER_DATE,'MMM'), m11.CUSTOMER_NAME, m11.BU, " & _ " m11.ITEM,m11.NAME" & vbCrLf sCmd(32) = " Union All" & vbCrLf sCmd(33) = _ " SELECT " & _ " FORMAT(m12.ORDER_DATE,'mmm') AS Month, m12.NAME, m12.CUSTOMER_NAME, m12.BU, m12.ITEM, SUM(m12.GROSS_AMOUNT) as " & _ "GROSS_AMOUNT" & vbCrLf & _ " FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m12" & vbCrLf sCmd(34) = _ " GROUP BY FORMAT(m12.ORDER_DATE,'MMM'), m12.CUSTOMER_NAME, m12.BU, " & _ " m12.ITEM,m12.NAME" & vbCrLf For i = 0 To 34 Debug.Assert Len(sCmd(i)) <= 255 Next With ActiveWorkbook.Connections("Query from MyExcel").ODBCConnection ..CommandType = xlCmdSql ..BackgroundQuery = True ..CommandText = sCmd .Connection = Array(Array( _ "ODBC;DSN=MyExcel;DBQ=C:\Sales\Jan 09 Sales.xlsx;DefaultDir=C: \Sales;DriverId=1046;FIL=excel 12.0;MaxBufferSi" _ ), Array("ze=2048;PageTimeout=25;")) ' .CommandText = sCmd '.Refresh End With Sheets("Sheet1").Select Range("A1").Select Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False End Sub Once I run the code the query definition shows as follows: SELECT FORMAT(M1.ORDER_DATE,'mmm') AS Month, M1.NAME, M1.CUSTOMER_NAME, M1.BU, M1.ITEM, SUM(M1.GROSS_AMOUNT) as GROSS_AMOUNT FROM `C:\Sales\Jan 09 Sales.xlsx`.`Sales$` M1 GROUP BY FORMAT(M1.ORDER_DATE,'MMM'), M1.CUSTOMER_NAME, M1.BU, M1.ITEM, M1.NAME Union All SELECT FORMAT(m2.ORDER_DATE,'mmm') AS Month, m2.NAME, m2.CUSTOMER_NAME, m2.BU, m2.ITEM, SUM(m2.GROSS_AMOUNT) as GROSS_AMOUNT FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m2 GROUP BY FORMAT(m2.ORDER_DATE,'MMM'), m2.CUSTOMER_NAME, m2.BU, m2.ITEM, m2.NAME Union All SELECT FORMAT(m3.ORDER_DATE,'mmm') AS Month, m3.NAME, m3.CUSTOMER_NAME, m3.BU, m3.ITEM, SUM(m3.GROSS_AMOUNT) as GROSS_AMOUNT FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m3 GROUP BY FORMAT(m3.ORDER_DATE,'MMM'), m3.CUSTOMER_NAME, m3.BU, m3.ITEM, m3.NAME Union All SELECT FORMAT(m4.ORDER_DATE,'mmm') AS Month, m4.NAME, m4.CUSTOMER_NAME, m4.BU, m4.ITEM, SUM(m4.GROSS_AMOUNT) as GROSS_AMOUNT FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m4 GROUP BY FORMAT(m4.ORDER_DATE,'MMM'), m4.CUSTOMER_NAME, m4.BU, m4.ITEM, m4.NAME Union All SELECT FORMAT(m5.ORDER_DATE,'mmm') AS Month, m5.NAME, m5.CUSTOMER_NAME, m5.BU, m5.ITEM, SUM(m5.GROSS_AMOUNT) as GROSS_AMOUNT FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m5 GROUP BY FORMAT(m5.ORDER_DATE,'MMM'), m5.CUSTOMER_NAME, m5.BU, m5.ITEM, m5.NAME Union All SELECT FORMAT(m6.ORDER_DATE,'mmm') AS Month, m6.NAME, m6.CUSTOMER_NAME, m6.BU, m6.ITEM, SUM(m6.GROSS_AMOUNT) as GROSS_AMOUNT FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m6 GROUP BY FORMAT(m6.ORDER_DATE,'MMM'), m6.CUSTOMER_NAME, m6.BU, m6.ITEM, m6.NAME Union All SELECT FORMAT(m7.ORDER_DATE,'mmm') AS Month, m7.NAME, m7.CUSTOMER_NAME, m7.BU, m7.ITEM, SUM(m7.GROSS_AMOUNT) as GROSS_AMOUNT FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m7 GROUP BY FORMAT(m7.ORDER_DATE,'MMM'), m7.CUSTOMER_NAME, m7.BU, m7.ITEM, m7.NAME Union All SELECT FORMAT(m8.ORDER_DATE,'mmm') AS Month, m8.NAME, m8.CUSTOMER_NAME, m8.BU, m8.ITEM, SUM(m8.GROSS_AMOUNT) as GROSS_AMOUNT FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m8 GROUP BY FORMAT(m8.ORDER_DATE,'MMM'), m8.CUSTOMER_NAME, m8.BU, m8.ITEM, m8.NAME Union All SELECT FORMAT(m9.ORDER_DATE,'mmm') AS Month, m9.NAME, m9.CUSTOMER_NAME, m9.BU, m9.ITEM, SUM(m9.GROSS_AMOUNT) as GROSS_AMOUNT FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m9 GROUP BY FORMAT(m9.ORDER_DATE,'MMM'), m9.CUSTOMER_NAME, m9.BU, m9.ITEM,m9.NAME Union All SELECT FORMAT(m10.ORDER_DATE,'mmm') AS Month, m10.NAME, m10.CUSTOMER_NAME, m10.BU, m10.ITEM, SUM(m10.GROSS_AMOUNT) as GROSS_AMOUNT FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m10 GROUP BY FORMAT(m10.ORDER_DATE,'MMM'), m10.CUSTOMER_NAME, m10.BU, m10.ITEM,m10.NAME Union All SELECT FORMAT(m11.ORDER_DATE,'mmm') AS Month, m11.NAME, m11.CUSTOMER_NAME, m11.BU, m11.ITEM, SUM(m11.GROSS_AMOUNT) as GROSS_AMOUNT FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m11 GROUP BY FORMAT(m11.ORDER_DATE,'MMM'), m11.CUSTOMER_NAME, m11.BU, m11.ITEM,m11.NAME Union All SELECT FORMAT(m12.ORDER_DATE,'mmm') AS Month, m12.NAME, m12.CUSTOMER_NAME, m12.BU, m12.ITEM, SUM(m12.GROSS_AMOUNT) as GROSS_AMOUNT FROM `C:\Sales\Feb 09 Sales.xlsx`.`Sales$` m12 GROUP BY FORMAT(m12.ORDER_DATE,'MMM'), m12.CUSTOMER_NAME, m12.BU, m12.ITEM,m12.NAME (Excel 2007|Win XP) Thanks, Abdul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Query
when accesing an Excel Workbook using ADO method the sheet names must end wit a dollar sign like you are using in the code to get sheet Sales$. I don't see two worksheets in the macro. Only the "Sales" sheet. To get data from multiple worksheets you need in the FROM section of your SQL you need to add all the worksheet names. If you sheet names are similar the LIKE may work. Not sure. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=167354 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Query
Hi Joel,
Thaks for your reply, All the sheets are names "Sales" bu in different workbooks and m1, m2 are used as alias to name them ????????? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Web query: Want to pause macro while Web query completes refreshin | Excel Programming | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) |