Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to have an excel spreadsheet, that when it opens will automatically populate from a SQL database, using specific queries. Each Workbook would use a different query (or multiple queries). Ideally when it is opened, the connection properties to SQL could be changed.

Any idea how to do this?

share|improve this question
up vote 9 down vote accepted

Of course. Here are the basics.

1) Create a connection (once)

  • Excel-Data-From Other Sources-SQL Server (this is for Microsoft SQL, but other data sources are available)
  • Enter Server credentials, uncheck "Connect to a specific table" if you wish to use various tables or SQL queries later on.
  • Finish. It brings you automatically to the step below, but you don't have to repeat that part.

2) Add connection to workbook

  • Excel-Data-Existing Connections
  • Pick connection you just saved
  • Pick a table. If you wish to use a query, pick any table, then on the next screen (Import Data), click Properties, Definition tab, change Command type from Table to SQL and paste your query below.
  • Import as Table

3) List and modify connections at anytime

  • Excel-Data-Connections
  • You can refresh, delete and also access Properties for example to modify the SQL queries without adding a new connection.
  • Here in properties you can also set options to refresh automatically and at startup.
share|improve this answer

This would be an ODBC import, and you'd need to set up and ODBC data source and then connect Excel using ODBC.

I've never done this, so I can't give much advice past these links.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .