Excel – How to make Excel fetch data from a database automatically when I open the spreadsheet

microsoft excelsql

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?

Best Answer

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.

Related Solutions

SQL – How to Build an Excel Data Table from Other Excel Files Using SQL

You can use ADO with Excel VBA. For example:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset 

''This is just a convenient name to test, it would probably be
''better to use the full file name eg C:\Docs\XL.xls
strFile = Workbooks(1).FullName

''For ACE see: http://www.connectionstrings.com/excel-2007
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

''Pick one:
strSQL = "SELECT * FROM DataTable" ''Named range
strSQL = "SELECT * FROM [Sheet1$A1:E346]" ''Range
strSQL = "SELECT * FROM [Sheet1$]" ''All the data in a sheet
strSQL = "SELECT * FROM [Excel 8.0;HDR=YES;IMEX=1;" _
       & "database=C:\Docs\LTD.xls].[Sheet1$]" ''Refer to second workbook

rs.Open strSQL, cn

''Write a recordset to a sheet
Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

The query can use anything acceptable in Jet SQL:

Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000

You will find quite a bit more on: https://stackoverflow.com/, including adding to MS Access, SQL Server, MySQL and so forth.

Excel – Embed microsoft query in excel with multiple parameters from spreadsheet

You can try this in SQL view, then Excel will prompt you to select the referring cells:

where (CustomerID = ?) AND (xxx = ?) AND (zzz = ?)

Best Answer

Related Solutions

SQL – How to Build an Excel Data Table from Other Excel Files Using SQL

Excel – Embed microsoft query in excel with multiple parameters from spreadsheet

Related Question