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