How to use a sql query and get data from one Excel sheet to another in VBA?

Question

I have two excel workbooks. One has a list of target customers and the other has a a table of sales data. I would like to use vba and write a sql query to get the sales history for specific customers and move that sales history to a new ListObject in the Target Customers workbook. What is the best way to do this?

I have tried an OLEDB connection, but I can't seem to get it to work, and I'm even not sure that that is the best way to solve my problem.

This is an example of the code I currently have.

Public Sub GetSales()

Dim targetList As String

'Get list of target customers
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
counter = Selection.Rows.Count

targetList = "'" & Range("A2").Value & "'"
For x = 2 To counter
    targetList = targetList + ",'" + CStr(Range("A" & CStr(3)).Value) + "'"
Next x


'Query I want to run
'SalesData is the ListObject in the the Sales Data workbook
sqlQuery = "Select * From SalesData WHERE Customer IN " & targetList


    With ActiveWorkbook.Connections("SalesData").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = sqlQuery
        .CommandType = xlCmdSql
        .Connection = Array(something in here??)
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With


'Return the queried sales data into a list object _
'on a new sheet in the Target Customers workbook
ActiveWorkbook.Worksheets.Add().Name = "Sales History"
Worksheets("Sales History").Activate

With ActiveSheet.ListObjects.Add '(results of query)
    .DisplayName = "SalesHistory"
End With

End Sub

Answer 1

Below is a simple connection and query to another workbook.

Sub simple_Query()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    dbpath = "your path here"
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    strSQL = "SELECT * FROM [Sheet1$] "
    Set vNewWB = Workbooks.Add 'or .CopyFromRecordset rs to open workbook
    connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & dbpath & ";Extended Properties=""Excel 12.0; HDR=YES; IMEX=1""; Mode=Read;"
    cn.Open connstr
    Set rs = cn.Execute(CommandText:=strSQL)
    vNewWB.Sheets(1).Range("A2").CopyFromRecordset rs
    For intcolIndex = 0 To rs.Fields.Count - 1
        Range("A1").Offset(O, intcolIndex).Value = rs.Fields(intcolIndex).Name
    Next
    rs.Close
    cn.Close
    Set cn = Nothing
    Set rs = Nothing
End Sub