Import named TABLES (not RANGES) from Excel to Access

Dom 771 Reputation points
2022-11-09T18:43:39.953+00:00

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.

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
846 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,671 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Charles Qi_MSFT 966 Reputation points
    2022-11-10T10:23:35.727+00:00

    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.

    0 comments No comments

  2. Oskar Shon 866 Reputation points MVP
    2022-11-14T21:06:03.877+00:00

    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

    260256-image.png

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

    and then the range name

    260240-image.png

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

    Regards