Range("A1:C10").Value = "'С:\какие-то_папки\[файл.xlsx]Лист1'!A1:C10

SELECT
d.City AS City,
SUM(a.Number) AS Num
FROM
[Исходные данные$a:b] AS a,[Исходные данные$d:e] AS d
WHERE
a.Number=d.Number_city
GROUP BY
d.City

SELECT
d.City AS City,
SUM(a.Number) AS Num
FROM
[Исходные данные$a:b] AS a
RIGHT JOIN [Исходные данные$d:e] AS d
ON a.Number=d.Number_city
GROUP BY
d.City

TRANSFORM Sum(Продажи)
SELECT Город, Продукт
FROM Лист1
GROUP BY Город, Продукт
PIVOT [Тип магазина]

Sub selectData()
                      
         Dim rst As Object
         Set rst = CreateObject("ADODB.Recordset")
                      
         rst.Open _
         "TRANSFORM Sum(Продажи) " & _
         "SELECT Город, Продукт " & _
         "FROM [Лист1$A2:D23] " & _
         "GROUP BY Город, Продукт " & _
         "PIVOT [Тип магазина]" _
         , _
         "Provider=Microsoft.ACE.OLEDB.12.0;" & _
         "Data Source=" & ThisWorkbook.FullName & ";" & _
         "Extended Properties='Excel 12.0;HDR=Yes'"
                      
         [Лист1!G15].CopyFromRecordset rst
                            
         'ЗАГОЛОВКИ
         Dim i As Integer
         Dim arrName() As Variant
         ReDim Preserve arrName(0 To rst.Fields.Count - 1)
         For i = 0 To rst.Fields.Count - 1
             arrName(i) = rst.Fields(i).Name
         Next i
         With Range("G14").Resize(1, rst.Fields.Count)
             .Value = arrName
             .Font.Bold = True
         End With

End Sub

Data = Me.Recordset.GetRows()

Me.Recordset.MoveFirst