Do a select on a range in excel and outputting that in another worksheet

It's possible to use Excel as a ODBC source and do a query on data in a worksheet.

Here's what I've done.

I've imported data into Excel from a SQL-server database into a worksheet.

I use a select statement that connects to SQl_server and dumps the data into sheet1.

开发者_如何转开发

The initial select if long and complicated, and I manipulate it using VBA to make the query dynamic.

For this reason I do not want to mess around with the original query to enclose it in a

SELECT a,b,c, sum(debit) as debit, sum(credit) as credit, accountnumber FROM (
  SELECT lost_of_fields
  FROM lots_of_tables_with_lots_of_joins
  WHERE couple_of_conditions
  ORDER BY few_fields ) AS subselect
) GROUP BY subselect.accountnumber

I don't want to do this because SQL-server complains about selecting lots of fields, whilst only doing a GROUP BY on one of them.

Question

Can I do a

SELECT a,b,c, sum(debit) as debit, sum(credit) as credit, accountnumber 
FROM named_range_in_excel 
GROUP BY accountnumber

And dump the output into a sheet in the same Excel workbook?

If I can do this without using VBA, that would be cool, if VBA is needed that's OK too.

---

You might try using an "External Data" reference, using a query from the same workbook and the query editor to form the SELECT statement.

You don't say what version of Excel (the details vary a bit, but the capability is much the same across versions)

Eg in 2010: Data tab, Get External Data, From Other Sources, From Microsoft Query, ExcelFiles.

UPDATE: added screenshot. But, SQL does complain the group by as you said. I'm not a SQL expert. others may be able shed some light...

An example of the expected output might help.