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:

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,957
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
 

Ken Cowen

Board Regular
Joined
Jan 18, 2015
Messages
213
I like to build SQL connection strings and queries like
d1=range("date1")
d2=range("date2")
str="SELECT [BillingCodeId], [ClientId], [Details], [DateTime] FROM [dbo].[Billing] WHERE DateTime between "
str=str & d1
str-str & " and " & d2
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.

I hope this helps.
ken

<colgroup><col></colgroup><tbody>
</tbody>
 

Nola111

Board Regular
Joined
Aug 28, 2013
Messages
119
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.
 

Nola111

Board Regular
Joined
Aug 28, 2013
Messages
119
I like to build SQL connection strings and queries like
d1=range("date1")
d2=range("date2")
str="SELECT [BillingCodeId], [ClientId], [Details], [DateTime] FROM [dbo].[Billing] WHERE DateTime between "
str=str & d1
str-str & " and " & d2
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.
I hope this helps.
ken

<tbody>
</tbody>

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.
 

Nola111

Board Regular
Joined
Aug 28, 2013
Messages
119
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,510
Messages
5,602,075
Members
414,500
Latest member
kevdragon1

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
Top