Import named TABLES (not RANGES) from Excel to Access

Question

So I've referenced this issue several years ago access-2016-import-from-excel-2016-table-names-do-not-show-up)

I have not been able to Google anything new on this topic so I thought I'd try to bring it up again to see if there is any new information. It still boggles the mind that Access would NOT want to get data in the form of a TABLE in Excel. The table name does appear in the name manager, yet Access does not see it as a names range for import. I've tried to create a named range and "point" it to the name of the table, but this doesn't work either. I tried importing into Access via a macro, but the macro doesn't recognize the TABLE name either.

-As the table is constantly growing, I can't use absolute references for a named range.

• I can't import a worksheet as there can be many tables on a sheet.

Other ideas I thought of but do not know how to implement:

• Can I define a dynamic range that changes in size as the table grows?
• Can I load the table to the Data Model and have Access connect to it that way?

Any assistance would be greatly appreciated. Thanks.

Answer 1

Hi, @Dom

We did a test on our side and find that when importing data from dynamic range in excel to Access, the name of the data cannot be found. We recommend you send a feedback. You can refer to feedback

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in email-notifications to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

O course you can use VBA to set data range to import. Try to use ADODB to import.
Anyway if you using stored procedure you can add unlimited amount of data to your table (while maintaining access restrictions).
You should read about that because insert SQL commend can be insufficient.

for example, a piece of code that retrieves the range from the table name

Dim r As Range: Set r = ActiveSheet.ListObjects("Tabela2").Range  

and then the range name

Dim r As Range: Set r = ActiveSheet.Range("Zakres2")  

Regards