Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    TransferSpreadsheet import Sheet Name (2003 SP3)

    I searched the past posts seeking information relating to importing an Excel file, with multiple sheets, and specifying which sheet, with no results. If I used the wrong search criteria, please accept my apology in advance.

    Is there a way to import/transfer data from a specific sheet on an excel file. For example is the file has Sheet1, Sheet2 and Sheet3, can I specify I want Sheet2?

    Thanks for ideas.

    Ken

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

    Re: TransferSpreadsheet import Sheet Name (2003 SP3)

    If you want to import from another than the first worksheet, you must specify either the sheet and range to be imported, or a defined name in the Range argument of TransferSpreadsheet. Unfortunately, TransferSpreadsheet doesn't understand dynamic named ranges (that adapt automatically to rows and/or columns being added or removed), only fixed named ranges.

    Examples:
    <code>
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblImport", "Test.xls", True, "Sheet2!A137"
    </code>
    and
    <code>
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblImport", "Test.xls", True, "MyRange"
    </code>
    Here, MyRange is a defined name referring to a fixed worksheet range.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet import Sheet Name (2003 SP3)

    Hans,

    Thanks. Based on your post, it appears the options of acquiring ALL the data in a given sheet are to use automation to find the last row, and feed that into the transferspreadsheet parameters, or just set the range as a high number not expected to exceed. Is that the case?

    Regardless, what you gave me did work and I appreciate your help.

    Ken

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

    Re: TransferSpreadsheet import Sheet Name (2003 SP3)

    It's sufficient to specify a range that is larger than what you expect it will be - Access won't import empty records at the bottom or empty columns on the right.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet import Sheet Name (2003 SP3)

    Yes, that's the approach I used. One column wider than the last column of data and a few thousand rows past the current last row. And as you said, it didn't import but 5 extra rows.

    THANKS for your solution. As usual, your knowledge was dead on and very helpful.

    Ken

Posting Permissions

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