Как использовать sql-запрос и получать данные с одного листа Excel на другой в VBA?

#excel #vba #oledbconnection #listobject

Вопрос:

У меня есть две книги Excel. У одного есть список целевых клиентов, а у другого есть таблица данных о продажах. Я хотел бы использовать vba и написать sql-запрос, чтобы получить историю продаж для конкретных клиентов и переместить эту историю продаж в новый ListObject в книге Target Customers. Каков наилучший способ сделать это?

Я пробовал подключение 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 = "'" amp; Range("A2").Value amp; "'"
For x = 2 To counter
    targetList = targetList   ",'"   CStr(Range("A" amp; 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 " amp; 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

1. Я думал, что мог бы прикрепить свои рабочие книги, я не вижу места для этого.

2. Сначала вам нужно будет установить объекты connection и recordset, определить строку подключения для подключения к этой конкретной книге. На вкладке Данные Excel > Из других источников > Из Microsoft Query > Файлы Excel. Как только вы откроете его, вы сможете выбрать книгу, которую хотите использовать как базу данных, и как только у вас будут выбраны все таблицы и строки, вы можете нажать на кнопку SQL, и она предоставит вам вашу строку SQL для размещения в VBA.

Ответ №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=" amp; dbpath amp; ";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

1. Отличный, приятный код. Есть две небольшие опечатки «intcolindex». Я обнаружил, что если бы я произвел какие-либо манипуляции с данными до их переноса, мне пришлось бы сохранить, иначе этот код перенес бы данные до манипуляции.