CODEWITHSUNDEEP
excelvbaobjectms-accessoledb
crawfojk
crawfojk

Reputation: 1

Access Database Engine could not find object - object is Excel named range

This is something that has had me going round and round in circles for a while now, essentially all I would like to do is to insert the values of an Excel dynamic range into an Access table.

I have had success in doing this by referencing the range as for example, however to make things a little more self sufficient I would prefer to use a dynamic range.

The code I have is as follows:

Sub ExportDistDatatoSql()

Dim cn As ADODB.Connection
Dim STRQUERY As String

Set cn = New ADODB.Connection

With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & ThisWorkbook.Path & "\uMyDB.accdb;"
.Open
End With

ssql = "INSERT INTO Crude_Prods_DB Select * from [Excel 12.0;HDR=YES;DATABASE=C:\TEST\mysheet.xlsm].[n_range]"

cn.Execute ssql

End Sub

The error I am seeing is attached and I have checked an [n_range] does exist in the workbook.

[error seen when attempting to insert data into access table from excel named range]

Any suggestions would be much appreciated.

Upvotes: 0

Views: 987

Answers (1)

Gustav
Gustav

Reputation: 55841

I learned this the hard way. A dynamic range is resolved only when Excel runs, thus it doesn't exist when you just read the file.

Only a saved and fixed Named Range can be read by Access.

Upvotes: 1

Related Questions