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

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

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