excel - 如何在 VBA 中使用 sql 查询并将数据从一个 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