How to link to an Excel named range using vba

[Kovenna]

Can anyone help?

I have spent a lot of time trying to do this.

I can use the Docmd.TransferSpreadsheet (my preferred choice) but

1. I do not know how to specify a named range rather than an Excel Worksheet
2. I do not know how to specify "IMX=0" to counter the "recent" change due to legal action that Microsoft made so that a linked excel table in Access cannot be updated.

The only other choice is to create a tabledef from the connection string but again, despite multiple googles, I cannot find the syntax for including named range and IMX=0.

Thanks for your help in advance

 

[Gasman]

Crossposted https://www.accessforums.net/showthread.php?t=89828

 

[ebs17]

I do not know how to specify a named range rather than an Excel Worksheet

I can't help you there. Named ranges are Excel objects and are probably not taken into account in import routines of other programs (I think). TransferSpreadsheet uses the same accesses internally as Jet (Access SQL) and shows the same behavior.

I like to use something like this myself:

SELECT T.* FROM [excel 12.0 xml;hdr=yes;imex=1;DATABASE=D:\anywhere\Exelfile.xlsx].[Sheet1$B2:H1000] AS T
WHERE FieldA > ""

The query is just as good as linking to the table. A fixed addressing of the range is used. You need to know how many columns there are (in the example, B to H), as well as the start line of the desired range (here 2).
The end line is somewhat variable thanks to the WHERE clause. I can therefore set the end line lower than specifically required and therefore in most cases constant. No other content in the Excel sheet should then disturb this table.
A column should be used in the WHERE clause that is guaranteed to contain content throughout.

The Excel table should be editable using the query shown => Add yes, update yes, delete no
Please note, however, that Excel does not support multi-user functionality. Access via query will block direct operations in Excel.

 

[Gasman]

I was wondering why one could not just add a range to the sheet name?
However I cannot edit anything on the query with that syntax, even with IMEX=0

SELECT T.*
FROM [excel 12.0 xml;hdr=yes;imex=1;DATABASE=F:\Users\Paul\Documents\GCD\GCD Master.xlsm].[SF66OEK$A3:H5000] AS T

 

[Josef P.]

The example from post #3 also works with a named range.

SELECT T.* FROM [excel 12.0 xml;hdr=yes;imex=0;DATABASE=D:\anywhere\Exelfile.xlsx].[NameOfCellRange] AS T
WHERE FieldA > ""

Note: imex=0 or imex=1

 

[ebs17]

Thanks for the addition.

 

[Kovenna]

The example from post #3 also works with a named range.

SELECT T.* FROM [excel 12.0 xml;hdr=yes;imex=0;DATABASE=D:\anywhere\Exelfile.xlsx].[NameOfCellRange] AS T
WHERE FieldA > ""

Note: imex=0 or imex=1

Thanks Josef, I shall give that a try. Also thanks to everyone else who helpfully posted

It seems amazing to me that something so basic should be so hard. Oh well, I guess that's in MS's DNA

 

[saml_e]

I came across the same problem when I was trying to link a table from an Excel file with multiple worksheets.
Here's how I did it using TransferSpreadsheet.

DoCmd.TransferSpreadsheet _
    TransferType:=acLink, _
    SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
    TableName:=tbl_link_name, _
    fileName:=file_source_folder & file_source_name, _
    HasFieldNames:=True, _
    Range:=file_source_range

In my case, the actual named range to use in MS Access was very much different from what I have in Excel.
I checked it by going to MS Access and manually linking the Excel file through the Linked Table Manager.
I went through the steps until prompted to choose between "Show Worksheets" or "Show Named Ranges".
I clicked on "Show Named Ranges" and found the named ranges in my Excel file as recognized by MS Access.

Example:
Worksheet name = Test Sheet
Table name (listobject) = Table_RawData
range used in MS Access TransferSpreadsheet = 'Test Sheet!'ExternalData_1

Table_RawData is an import from MS Access. Maybe that's why MS Access sees it as "ExternalData_1".
It seems crucial to manually verify the named ranges that MS Access recognizes.

I hope this helps.

 

[Pat Hartman]

Check out the help entry. TransferSpreadsheet includes a Range argument.

 

[Isaac]

It's right there in the documentation how to specify a range with TransferSpreadsheet

DoCmd.TransferSpreadsheet method (Access)

Office VBA reference topic

learn.microsoft.com