Excel and SQL - connection between Excel files

Question

I have the (final) code I got on the internet. In this line of code gives error in WHERE, since it is a text:

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

Substituting for this below does not give error in WHERE, being a number:

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

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

Here is the complete code, and I also ask: Need to set Nothing at the end of the code for the variables conn, rst, and fld? For full code to run, do I need to add some library?

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

Thank's. And set Nothing and library?

Paulo Semblano
– Paulo Semblano

2018-07-09 03:58:29 +00:00
Commented Jul 9, 2018 at 3:58 — Paulo Semblano
– Paulo Semblano, Commented Jul 9, 2018 at 3:58

Tim Williams · Accepted Answer · 2018-07-08 19:07:59Z

2

...WHERE [VENDAS$].[Vendedor] = Murilo;"

should be

...WHERE [VENDAS$].[Vendedor] = 'Murilo'"

Non-numeric fields need quotes around values.

I typically omit the semicolon - I have found instances where it's a problem if you include it but I've never run into issues when leaving it out.

edited Jul 8, 2018 at 19:07

answered Jul 8, 2018 at 18:57

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel and SQL - connection between Excel files

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related