1 Intro
2 Set up the query to run as a stored procedure
3 Prepare Excel
4 Make ODBC connection to database in SQL
5 Prepare “Microsoft query”
6 Link fields to query parameters
Simple example query:
declare @StartDate datetime set @StartDate = ‘2018-01-01’
declare @EndDate datetime set @EndDate = ‘2018-01-31’
select * from tblOrder where orderdate <= @StartDate and orderdate >= @EndDate |
Create and run a script that creates a stored procedure:
CREATE PROCEDURE spSelectOrder
— Add the parameters for the stored procedure here
@StartDate As DateTime,
@EndDate As DateTime
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
select * from tblOrder where orderdate <= @StartDate and
orderdate >= @EndDate
END
GO |
This stored procedure can be run in Excel using Microsoft query. To run this query, prepare a worksheet with the parameters filled. These parameters will be used as input for the query later.
Next step is to add the data source to the worksheet. Start Data, “From Other Sources” “From Microsoft Query”. This will start a wizard to create a data connection:
1 Select or create a Data Source
2 The next step in the wizard is Choose Columns. Cancel the wizard on this screen and a question will pop up asking you if you want to continue editing this query. Click Yes.
3 MS Query will be started and a dialog to select tables will be presented. Close this dialog.
4 Click SQL button in button bar or in menu choose View, SQL.
5 Type “call” followed by the stored procedure name and question marks for the parameter input between parentheses. Place this in between curly bracets. These curly bracets are required to avoid syntax check errors.
{call spMassBalans (?, ?)}
6 Press OK and you will be prompted to input values for the required parameters.
The results will be retrieved in a query result window. After the result is presented go to File and click “Return Data to Microsoft Excel”.
Microsoft query will be closed and you will return to Excel with a dialog to import the data. Choose cell A4 in the work sheet.
Again you will be prompted to input values for the parameters. This time you are able to select the cells B1 and B2 as input. Check the checkbox in the dialog to use the reference for future use. If you want you can also check to refresh the data when the cell value changes.
If you want to manually refresh the data you can right-click anywhere in the datagrid and select “refresh”.