Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Excel Data

    I am having issues trying to find the correct syntax to import data from Excel. I have the TrasnferSpreadsheet down fine. What I would like to do is give the statement a data range in the workbook.

    I have tried 'Region 33'!A1:T900, 'Region 33' A1:T900, and may more combinations. I am not having any luck finding the correct way to give the range.

    My final goal is to set up a loop where I can feed a variable sheet name to the statement and have Access read in the A1:T900 range from many sheets in the workbook. Sheets have the naming convention Regionxx. I have a workbook containing 30 tabs for the given Regions.


    thanks for any help in getting this statement working.
    Richard

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Would it be possible to use a "Named Range" in Excel? If you can modify the workbook to have Named Ranges on each of the worksheets, and you can use a consistent naming convention, then that should let you do the import programatically. If you can't use that approach, then the syntax to define the range, given you know the range, is:
    WorksheetName & "!A1:T900"
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I will try this and see how it goes.

    Thanks Wendell.
    Richard

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Named Range worked. For some reason Access converter the following from "'Region 33'" & "!A1:T900" to 'Region 33'$A1:T900. Of course Excel could not find that range.

    But named rangeds worked like a dream.

    Thanks for your help.
    Richard

Posting Permissions

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