Пожалуй свои три копейки вставлю
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
Function Test(sql As String) As Variant
' sql = Select t1.Name as name, t1.Number as n, t2.Cnt as cnt FROM [Лист1$] as t1 INNER JOIN [Лист2$] as t2 on t1.name = t2.name
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Debug.Print Application.ActiveWorkbook.FullName
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Application.ActiveWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
objRecordset.Open sql, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Dim vArray As Variant
ReDim vArray(1 To objRecordset.RecordCount, 1 To objRecordset.Fields.Count)
R = 1
Do Until objRecordset.EOF
C = 1
For Each f In objRecordset.Fields
vArray(R, C) = f.Value
C = C + 1
Next
R = R + 1
objRecordset.MoveNext
Loop
Test = vArray
End Function
Ну и функцию вызывать очевидным образом, например, test("Select t1.Name as name, t1.Number as n, t2.Cnt as cnt FROM [Лист1$] as t1 INNER JOIN [Лист2$] as t2 on t1.name = t2.name") при этом вводить как функцию массива.
