Dim con As New ADODB.Connection
Dim constr as String
dim sqlstr as String

constr = "Provider=Microsoft.JET.OLEDB.4.0;" + _
         "Data Source=" + Path + ";" + _
         "Persist Security Info=False"
con.ConnectionString = constr
con.Open
sqlstr="INSERT INTO [table_name] (поля, поля, поля) SELECT поля, поля, поля FROM [Именованный диапазон] IN 'Путь' [Excel 8.0;HDR=NO или YES] WHERE блаблабла"
con.execute sqlstr
...

' соединение con с Access как описывалось выше
dim RS as ADODB.RecordSet
sqlstr="SELECT * FROM [RepDate] IN '" + PathExc + "' [Excel 8.0;HDR=NO]"
on Error GoTo SQLErr
Set RS = con.Execute(sqlstr)
...
GoTo Succeed
SQLErr:
...
Succeed:
...