Import Excel named range into Access

[TJ Dowling]

I'm having trouble importing an Excel named range into Access with VBA. I'm
using MS Office 2003. The table name is "xls_Link". I have created a named
range called "Import" in the file ImportFile.xls and am using this code:

Docmd.Transferspreadsheet acImport, acSpreadsheetTypeExcel5, "xls_Link",
"ImportFile.xls", True, "Import"

I get an error #3011 that says the MS Jet db engine could not find the
object "Import". It works fine if I manually import the table but I need to
automate it since it changes often. I've also tried linking with the same
result.

Any ideas how to fix it?

Thanks,
TJD

 

[Allen Browne]

Ken Snell (Microsoft Access MVP) has recently posted these Excel examples:
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm

The last one discusses using the Range argument for TransferSpreadsheet.

 

[Ken Snell \(MVP\)]

Allen, that information is for the Range argument when exporting, not
importing < s >. I am going to have to add this type of info to that page.

TJ, try this as the Range argument in your TransferSpreadsheet step:

"Import$"
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/