名前付きデータ範囲を読み込む

Sub 名前付き範囲のデータを転記()
Dim myCon As New ADODB.Connection, myRS As New ADODB.Recordset, FileName As String

FileName = ThisWorkbook.Path & "\xls\DataBook.xls"
myCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;" & _
           "Data Source=" & FileName

'名前付き範囲「社員」の内容を読み込む
myRS.Open "[社員]", myCon

'A1番地を基点に転記
Range("A1").CopyFromRecordset myRS

myRS.Close: Set myRS = Nothing
myCon.Close: Set myCon = Nothing

End Sub

名前付き範囲「社員」

実行結果