Control part of SQL Query inside External Data Connection from within Excel Workbook.

Nola111

Board Regular
Joined
Aug 28, 2013
Messages
119
Excel 2016 - I have an Excel file that imports a table from SQL Server via a connection created with the Data->Get Data->From Database->From Sql Server Database. As part of the Advanced Options while creating that connection I included a SQL query that basically reads:

Code:
  SELECT 
       [BillingCodeId]
      ,[ClientId]
      ,[Details]
      ,[DateTime]
  FROM [dbo].[Billing]
  WHERE DateTime between '2017-10-01' and '2017-11-01'

What I would like to do is be able to input those two dates into two cells inside the Excel workbook, and then when I hit refresh on the connection, the dates in that query populate with the dates in the cells.

Is this possible?
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Code:
Dim cn As ADODB.Connection
Dim sSql As String


sSql = "SELECT [BillingCodeId] ,[ClientId] ,[Details] ,[DateTime]   FROM [dbo].[Billing]   WHERE DateTime between #" & range("A1").value & "# and #" & range("A2").value & "#"


cn.Provider = "microsoft.jet.oledb.4.0"
cn.Open "C:\folder\Data.mdb"
Set rs = New ADODB.Recordset
With rs
  .ActiveConnection = cn
  .CursorLocation = adUseClient
  .CursorType = adOpenStatic
  .LockType = adLockReadOnly


  .Open sSql
End With


   'paste the records in excel
range("C1").select
selection.copyfromrecordset rs


cn.Close
 
Upvote 0
[TABLE="width: 1133"]
<colgroup><col></colgroup><tbody>[TR]
[TD]I like to build SQL connection strings and queries like[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]d1=range("date1")[/TD]
[/TR]
[TR]
[TD]d2=range("date2")[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]str="SELECT [BillingCodeId], [ClientId], [Details], [DateTime] FROM [dbo].[Billing] WHERE DateTime between "[/TD]
[/TR]
[TR]
[TD]str=str & d1[/TD]
[/TR]
[TR]
[TD]str-str & " and " & d2[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]d1 and d2 are declared as dates. It can be a little tricky getting the date formats to cooperate. d1 and d2 are declared as dates.

[/TD]
[/TR]
[TR]
[TD]I hope this helps.[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]ken[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Code:
Dim cn As ADODB.Connection
Dim sSql As String


sSql = "SELECT [BillingCodeId] ,[ClientId] ,[Details] ,[DateTime]   FROM [dbo].[Billing]   WHERE DateTime between #" & range("A1").value & "# and #" & range("A2").value & "#"


cn.Provider = "microsoft.jet.oledb.4.0"
cn.Open "C:\folder\Data.mdb"
Set rs = New ADODB.Recordset
With rs
  .ActiveConnection = cn
  .CursorLocation = adUseClient
  .CursorType = adOpenStatic
  .LockType = adLockReadOnly


  .Open sSql
End With


   'paste the records in excel
range("C1").select
selection.copyfromrecordset rs


cn.Close

Thanks ranman. Does this code replace the connection I've set up using the Get Data wizard, or does it work in-tandem with it? If the latter, should I delete the query from the connection?

Thanks again.
 
Upvote 0
[TABLE="width: 1133"]
<tbody>[TR]
[TD]I like to build SQL connection strings and queries like[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]d1=range("date1")[/TD]
[/TR]
[TR]
[TD]d2=range("date2")[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]str="SELECT [BillingCodeId], [ClientId], [Details], [DateTime] FROM [dbo].[Billing] WHERE DateTime between "[/TD]
[/TR]
[TR]
[TD]str=str & d1[/TD]
[/TR]
[TR]
[TD]str-str & " and " & d2[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]d1 and d2 are declared as dates. It can be a little tricky getting the date formats to cooperate. d1 and d2 are declared as dates.
[/TD]
[/TR]
[TR]
[TD]I hope this helps.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ken[/TD]
[/TR]
</tbody>[/TABLE]

Hi Ken - is your example to be put in the Advanced Editor of the connection properties (like how I set it up)? Then, how can I connect cells in the workbook to your example? Or are d1 and d2 the cells?

Thanks.
 
Upvote 0
Code:
Dim cn As ADODB.Connection
Dim sSql As String


sSql = "SELECT [BillingCodeId] ,[ClientId] ,[Details] ,[DateTime]   FROM [dbo].[Billing]   WHERE DateTime between #" & range("A1").value & "# and #" & range("A2").value & "#"


cn.Provider = "microsoft.jet.oledb.4.0"
cn.Open "C:\folder\Data.mdb"
Set rs = New ADODB.Recordset
With rs
  .ActiveConnection = cn
  .CursorLocation = adUseClient
  .CursorType = adOpenStatic
  .LockType = adLockReadOnly


  .Open sSql
End With


   'paste the records in excel
range("C1").select
selection.copyfromrecordset rs


cn.Close

Ranman, disregard my post #4 , I think your VBA code just replaces the connection created with the wizard. So then I did have another question. My SQL Server is an Azure SQL Server. Do you know the proper syntax to be used for the cn.Open function for the connection string if it's an Azure SQL Database?

Thanks.
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top