如何使用SQL查询并将数据从一个Excel表中获取到VBA的另一个excel表

Question

我有两个Excel工作簿。一个有目标客户列表，另一个具有销售数据表。我想使用VBA并编写SQL查询，以获取特定客户的销售历史记录，并将该销售历史记录转移到目标客户工作簿中的新ListObject。最好的方法是什么？

我尝试了OLEDB连接，但我似乎无法使其工作，我什至不确定这是解决我的问题的最佳方法。

这是我当前拥有的代码的一个示例。

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

以下是与另一个工作簿的简单连接。

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