Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Excel Wkshts with Dynamic Ranges (Access XP)

    I have an excel file with multiple worksheets and I want to import two of the worksheets into two separate tables in Access. I found that in order to use the TransferSpreadsheet function to import both of the worksheets rather than simply the first worksheet in the workbook, a range must be defined. Since my ranges for each worksheet in Excel will be changing as new data is entered, I created dynamic ranges. However, when I try to use these dynamic ranges as my specified ranges in Access with the TransferSpreadsheet function, Access claims that the range can not be found. Any suggestions on how to get around this?

    Thanks for your help,
    Angela

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Excel Wkshts with Dynamic Ranges (Access XP)

    Hello Angela,

    Welcome to Woody's Lounge.

    As you have found, Access does not recognize dynamic named ranges in Excel.

    One workaround is to create linked tables that link to the Excel worksheets, and use queries to copy records from these to Access tables. The linked tables will automatically adjust to the used range in the Excel worksheets.

    Another workaround would be to open the Excel workbook through automation, determine the range to import, close the workbook again and then pass the correct range to DoCmd.TransferSpreadsheet. It's a lot of work, though.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel Wkshts with Dynamic Ranges (Access XP)

    You can also use ADO to import data into Access. ADO will let you 'read' Excel files just like any other database.

  4. #4
    New Lounger
    Join Date
    Jun 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel Wkshts with Dynamic Ranges (Access XP)

    Linking the tables works beautifully with the dynamic ranges. Thanks for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •