Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

up vote 8 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.