Excel and SQL - Connection between Excel file

Question

I have the code on (end) that I picked up on the internet. This line of code gives error in WHERE, because it is a text:

strSQL = " SELECT [VENDAS$].[Data], [VENDAS$].[Vendedor], [VENDAS$].[Total]" & _
" FROM [VENDAS$] WHERE [VENDAS$].[Vendedor] = Murilo;"

Replacing by this below does not give error in the WHERE, being a number:

strSQL = " SELECT [VENDAS$].[Data], [VENDAS$].[Vendedor], [VENDAS$].[Total]" & _
" FROM [VENDAS$] WHERE [VENDAS$].[Total] >= 100000;"

And I ask you, do you need this semicolon at the end?

Follow the full code, and also ask: Do you need to set Nothing at the end of the code for the variables Conn, rst and Fld? For the complete code to run, you need to add some Reference?

Sub RunSQL()
On Error GoTo ErrHandle
    Dim conn As Object, rst As Object
    Dim strConnection As String, strSQL As String
    Dim wkCaminho, wkArquivo As String
    Dim I As Integer
    Dim fld As Object

    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    wkCaminho = ThisWorkbook.Path & "\"
    wkArquivo = "MATRIZ DE DADOS.xlsx"
    ActiveSheet.Cells.Delete
    ' Hard code database location and name
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                       & "Data Source='" & wkCaminho & wkArquivo & "';" _
                       & "Extended Properties=""Excel 8.0;HDR=YES;"";"

'    strSQL = " SELECT [VENDAS$].[Data], [VENDAS$].[Vendedor], [VENDAS$].[Total]" & _
'    " FROM [VENDAS$] WHERE [VENDAS$].[Total] >= 100000;"

    strSQL = " SELECT [VENDAS$].[Data], [VENDAS$].[Vendedor], [VENDAS$].[Total]" & _
    " FROM [VENDAS$] WHERE [VENDAS$].[Vendedor] = Murilo;"

    ' Open the db connection
    conn.Open strConnection
    rst.Open strSQL, conn

    ' column headers
    I = 0
    Worksheets("Results").Range("A1").Activate
    For Each fld In rst.Fields
        ActiveCell.Offset(0, I) = fld.Name
        I = I + 1
    Next fld

    ' data rows
    Worksheets("Results").Range("A2").CopyFromRecordset rst

    rst.Close
    conn.Close

    MsgBox "Successfully ran SQL query!", vbInformation
    Exit Sub

ErrHandle:
    MsgBox Err.Number & " = " & Err.Description, vbCritical
    Exit Sub
End Sub

Answer 1

You need to put simple quotes when it comes to characters, with numbers you don’t need as you have already done the test yourself. Try to put it this way: "FROM [SALES$] WHERE [SALES$]. [Seller] = 'Muril';"