MS Query

A

Abdul

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
 
J

joel

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

Abdul

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SIMPLE DRAGGING issue 1
Deleting/Clearing duplicate range of cells 8
Redesign for hitting limit argument in a UDF 18
Percentages breakouts for Ranking 1
Relative column headings in Crosstab when used in Chart 0
2D graph with label 1
Limiting results from one side of a join conditionally 1
Multiples 6

Top