Read data from an Excel cell to search via SQL

Question

I need to execute an SQL that brings me a result of only one cell, but, I need to capture information of two.

I will enter with start date and end date, my result will be a calculated value in that term.

I’ve tried several ways that I’ve found, but I don’t know if I didn’t know how to apply or didn’t work out for my case.

The query SQL:

select sum(cr.vl_receber)
  from contareceber cr
 where cr.dt_vencimento >= '20/12/2019'
   and cr.dt_vencimento <= '26/12/2019'
   and cr.cd_empresa = 1
   and cr.cd_filial in (1,5)
   and cr.cd_pessoa not in (4, 5, 8)
   and cr.cd_formapgto = 3
   and cr.tp_status in ('AB','IP','CA','PR','CO','IN')

The dates I need to read from a Spreadsheet cell and then the user will just update the dates in that cell, so they don’t need to update in the script.

That is, the user update the date in the excel table and the script already catch the date, load and display the expected result.

I imagine something used through Powerquery would help, but I tried codes like:

cr.dt_vencimento >= '"=Planilha1!B8"'
cr.dt_vencimento <= '"=Planilha1!C8"'

or:

cr.dt_vencimento >= '"B8"'
cr.dt_vencimento <= '"C8"'

or:

cr.dt_vencimento >= '"& B8 &"'
cr.dt_vencimento <= '"& C8 &"'

Anyway, I couldn’t or I couldn’t use it. How can I query this cell and click on the script so that the query is simpler for the user?

Answer 1

To concatenate this type of information it is necessary to use the vba:

Sub Download_Standard_BOM()
'Inicializando variaveis
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

'Conectando com o banco de dados SQL
   'Insira as informações corretas:
       '1: PASSWORD
       '2: USERNAME
       '3: REMOTE_IP_ADDRESS
       '4: DATABASE
    ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORD;Persist Security Info=True;User ID=USERNAME;Data Source=REMOTE_IP_ADDRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=DATABASE"

    'Abrindo conexção
    cnn.Open ConnectionString
    'Definir limite para erro de timeout
    cnn.CommandTimeout = 900

    'Consulta do SQL
    StrQuery = "SELECT TOP 10 * FROM tbl_table"

    'Executando consulta
    rst.Open StrQuery, cnn
    'Inserindo o resultado da consulta na primeira aba celula A2
    Sheets(1).Range("A2").CopyFromRecordset rst
End Sub

With this you can execute any type of query concatenating excel fields and insert the results in a cell.

Do not forget to enable in vba library the option:

Activex Data Objects 2.8 Library

Create a button and include the macro.

Note: In the SQL query create your select and concatenate the information as your last example:

...'"& B8 &"'...