How to link to a named range in an excel spreadsheet using vba code

[Kovenna]

Can someone help?

I have been trying to do this for hours with no success.

I would like to use Docmd.TransferSpreadsheet but

1. I have to specify IMX = 0 otherwise I cannot update data in the linked table
2. I have no idea how to reference the named range in TransferSpreadsheet.



Ideally I would like to use the full connection string in a function then I should have access to all the various parts for the connection string

Thanks for your help in advance

[ranman256]

you must connect the entire sheet.
you could have another sheet that points to the named range values in the other.

[Micron]

Which side is doing the work, Excel or Access? You're supposed to be able to use a named range if you import while in Access. Does this help:
https://learn.microsoft.com/en-us/of...ferspreadsheet

There is also Automation if all else fails.
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

[June7]

Can link to a named range. Syntax for TransferSpreadsheet:

"SheetName!RangeName"

Yes, use ! in TransferSpreadsheet command which will create a link that has $ character. Weird but true.

Once link is established, IMEX can be modified with VBA and TableDefs.

However, I am getting odd behavior with my testing. I cannot establish link with named range using TransferSpreadsheet unless the workbook is open. This is very odd.

Or build a query object with parameters. Example.

SELECT * FROM [Units$] IN 'C:\Users\June\Condos.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes;TABLE=Units$Print_Area];

[Welshgasman]

Now crossposted at https://www.access-programmers.co.uk...ng-vba.331402/