2

I maintain a batch process that inserts summary rows into a database table that is an exact copy of the source table that contains the detail. This is achieved by many Insert Into/Select from statements with various fields in the group by clause and overrides to those that aren't. Determining these summarization requirements is always challenging, I'm speaking SQL and the users speak Accounting.

I would like to build an excel spreadsheet that allows the users to show me exactly how they want the data summarized. My idea is a single Excel spreadsheet with the source data, options for every field that allow them to state whether they want the data grouped by that field, or overridden with an override value. Then the users can hit a button, and the summarized data appears on a new sheet.

Can I write SQL directly within an excel macro on a sheet just like it's a relational database table? Most of the info I've found while searching deals with importing or linking a spreadsheet to a database. I'm not looking for this. Security wise the users don't have access and I'd like to keep it independent of any specific database.

I would like to stick with SQL for the grouping logic as I know it already and I don't have the time to learn grouping logic in visual basic. Can anyone point me in the right direction? Thanks.

user640118
  • 803
  • 2
  • 13
  • 25

1 Answers1

1

You can make use ADO to connect from Excel to another data source using SQL. And you can use ADO to connect to Excel as a data source.

And of course you can use it to connect Excel to Excel.

Further reading on ADO:

http://support.microsoft.com/kb/257819

http://support.microsoft.com/kb/278973

Or you could see how far Pivot Tables will take you.

Matthew
  • 26
  • 1
  • Thanks for the reply. I'd written this question off since I got the tumbleweed award for it! I eventually found the ADO stuff and was able to run SQL against an excel spreadsheet just like a relational db table. Pretty useful imho. I'll post what I developed when I'm back in the office. – user640118 Jun 10 '11 at 16:22
  • For an example of using this approach, see [here](http://stackoverflow.com/a/32478268/111794). For a full discussion of the limitations of doing this, see [here](http://stackoverflow.com/a/32396594/111794). – Zev Spitz Sep 09 '15 at 23:32