Share via

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.
859 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,689 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  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
  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