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

[Nola111]

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:

  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?

 

[ranman256]

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]

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]

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]

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]

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.