Three Generations of the Same Task
Last week, I discussed an old method of importing data into Excel from an SQL command with some input parameters. The goal was to include parameter values coming from cells in Excel, with data automatically refreshed anytime the value of any parameter changes in the grid.
This week, I discuss a different method for accomplishing the same goal.
SQL via OLEDB
This method is still used by many experts, especially those familiar with both VBA and SQL syntax.
In Excel 2016, this method is hidden by default. To retrieve it, you’ll need to go to File/Options/Data and click the box next to From SQL Server (Legacy).
Once you add back any of the old import methods, you’ll see them under Data/Get Data/Legacy Wizards.
Using the SQL legacy import, you can select the server and the database and enter this dialog.
Select a table to end up with the same import dialog.
If you create a table, you can see the entire table with all columns and rows. Until this point, you could not select specific columns or filter specific rows. From the table, you can right-click Table/Edit Query.
Change the Command Type to SQL and write the SQL command to bring the right columns.
To make things simpler, I’ll select just three columns and use only one parameter for the day.
Select FullDateAlternateKey,EnglishDayNameOfWeek,CalendarYear from DimDate where EnglishDayNameOfWeek=’~Day~’. The value for day will not return any rows, but soon you’ll see why to use this odd value of ~Day~.
After clicking OK, you will see a table with three columns and no rows.
At this point, you need to insert a piece of VBA code that will substitute a day in the week instead of the dummy value ~Day~. After changing the value of the cell that contains the day, the VBA code will run, substitute the value, refresh the query and bring back the value of the parameter to be “~Day~”.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(“Day”)) Is Nothing Then
ActiveWorkbook.Connections(1).OLEDBConnection.CommandText = Application.WorksheetFunction.Substitute(ActiveWorkbook.Connections(1).OLEDBConnection.CommandText, “~Day~”, Range(“day”).Value)
ActiveWorkbook.Connections(1).Refresh
ActiveWorkbook.Connections(1).OLEDBConnection.CommandText = Application.WorksheetFunction.Substitute(ActiveWorkbook.Connections(1).OLEDBConnection.CommandText, Range(“day”).Value, “~Day~”)
End If
End Sub
The code implements an event in the sheet and is called up every time any cell in this sheet changes. If more than one parameter needs to change, the code must be changed to substitute multiple string values in the SQL command.
This option is mentioned because it is used by existing solutions, and it can be seen without the code here. However, I wouldn’t recommend to anyone to start implementing this technique when much better ones are available, as we will see.
Next week: The modern method for running an SQL query with parameters.
