Использование VBA-Excel для запроса SQL по нескольким базам данных

Question

Использование Excel 2010, SQL и DAO

Я пытаюсь выполнить запрос к таблицам, которые находятся за пределами текущей электронной таблицы. Это было бы тривиально, внутри Access, со связанными таблицами / базами данных, но использование VBA в Excel, я поставил в тупик.

Предполагая это:

ExcelFileOne; Tab; various headed field/columns
ExcelFileTwo; Tab; various headed field/columns

Я хочу выполнить запрос в третьем файле Excel, который выглядит примерно так [с использованием точечной нотации для объяснения, а не кодирования....] - простой пример:

SELECT FileOne.[Tab$].Fields, FileTwo.[Tab$].Fields, etc.
    FROM FileOne, FileTwo, Thisworkbook
    WHERE (FileOne.[Tab$].field2 <> FileTwo.[Tab$].Field2) 
        AND (ThisWorkbook.[Tab$].Field1 ....)

По сути, я хочу дублировать то, что Access будет делать изначально для этого связанного файла.

Указатели в правильных направлениях?

[[Я мог бы использовать указатель на то, почему использование "Excel 8.0..." в соединении работает или не удается в Excel2010 с макро-файлами, и как загрузить вариант 12 или 14 в сети / системе, закрытой для пользователей.... ]]

Answer 1

Вы действительно можете запрашивать другие книги, используя DAO и ADO непосредственно в операторе SQL, а также запрашивать таблицы доступа к базам данных, просто ссылаясь на их пути. И наоборот, в запросе Access вы можете запросить книги Excel! Это свидетельствует о том, что ядро ​​Jet/ACE SQL (файлы Windows .dll) не ограничивается каким-либо одним продуктом MS Office или программой Windows, а является инструментом для всех.

В обоих приведенных ниже примерах макросы устанавливают прямое соединение с первой книгой, а в запросе SQL каждый косвенно связывается со второй книгой. Вы можете запускать код внутри или вне рабочих книг. Также оба работает по генрику INNER JOIN на листах FileOne и FileTwo, но любой совместимый оператор Jet/ACE SQL должен работать. И оба выходных запроса приводят к существующей вкладке РЕЗУЛЬТАТЫ.

DAO

Dim dbE As Object, db As Object, rst As Object
Dim sqlString As String
Dim i As Integer
Const dbOpenDynaset = 2, dbReadOnly = 4

' OPEN DB CONNECTION
Set dbE = CreateObject("DAO.DBEngine.120")  'ALSO TRY: DAO.DBEngine.35 OR .36   
Set db = dbE.OpenDatabase("C:\Path\To\FileOne.xlsm", False, True, "Excel 12.0 Xml;HDR=Yes")

' OPEN QUERY RECORDSET
sqlString = " SELECT * FROM [TAB$] t1" _
              & " INNER JOIN (SELECT * FROM" _
              & " [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\FileTwo.xlsm].[TAB$]) t2" _
              & " ON t1.ID = t2.ID"

Set rst = db.OpenRecordset(sqlString, dbOpenDynaset, dbReadOnly)

' COLUMNS
For i = 1 To rst.Fields.Count
    Worksheets("RESULTS").Cells(1, i) = rst.Fields(i - 1).Name
Next i

' DATA ROWS
Worksheets("RESULTS").Range("A2").CopyFromRecordset rst

rst.Close
db.Close

Set rst = Nothing
Set db = Nothing
Set dbE = Nothing

АДО

Dim conn As Object, rst As Object, fld As Object
Dim strConnection As String, strSQL As String
Dim i As Integer

Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

' OPEN DB CONNECTION
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                  & "Data Source='C:\Path\To\FileOne.xlsm';" _
                  & "Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"        

conn.Open strConnection

' OPEN QUERY RECORDSET
strSQL = " SELECT * FROM [TAB$] t1" _
            & " INNER JOIN (SELECT * FROM" _
            & " [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\FileTwo.xlsm].[TAB$]) t2" _
            & " ON t1.ID = t2.ID"

rst.Open strSQL, conn

' COLUMNS
For i = 1 To rst.Fields.Count
     Worksheets("RESULTS").Cells(1, i) = rst.Fields(i - 1).Name
Next i

' DATA ROWS
Worksheets("RESULTS").Range("A2").CopyFromRecordset rst

rst.Close
conn.Close

Set rst = Nothing
Set conn = Nothing