Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    NSW, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Named Excel Worksheet (Access97)

    How do I specify worksheet name to TransferSpreadsheet Method? I need to import various spreadsheets into our database. Each spreadsheet has various number of worksheets. For example, Expenses Spreadsheet could have one work sheet for each month (Jan, Fefb

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing Named Excel Worksheet (Access97)

    I think you name each of the spreadsheets as a named range in the workbooks and then use the range parameter to import.
    David Grugeon
    Brisbane Australia

  3. #3
    Lounger
    Join Date
    Sep 2001
    Location
    NSW, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Named Excel Worksheet (Access97)

    I think that range refers to a range on the given worksheet, ie. A1:K6 for example. When I import data it usually imports it from the very first worksheet (ie. Sheet1).

    I need to tell it to import it from, say sheet6 range A1:K6. There has to be a way of doing it. When you import data using Import Wizard, the Wizard prompt you for the required worksheet if it finds two or more worksheets in the imported spreadsheet.

    Just got to find out how.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing Named Excel Worksheet (Access97)

    I think you can name the range (Sheet1 a1:k6) and "Fred" in the spreadsheet

    and then use "Fred" where it says [range] in the TransferSpreadsheet command.

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblParticipant", strFileLocation, True, "Fred"
    David Grugeon
    Brisbane Australia

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

    Re: Importing Named Excel Worksheet (Access97)

    Try "sheetname!range" for the Range argument:

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tablename", "path+filename", True, "Sheet6!A1:K6"

    If the sheet name contains spaces, enclose it in single quotes, for instance
    <pre>"'Expenses Sheet'!A1:K6"</pre>

    HTH, Hans

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Named Excel Worksheet (Access97)

    Continuing (or reviving) this thread...
    What do we do for a series of worksheets all with different range names?
    Ala "SheetNames!RangeNames"

    Also, if there are literally and hundred or so spreadsheets (all with ranges) do we run the risk of running out of buffer space when copying?
    Is there a way to halt the process, clear the buffers and then resume the process? Maybe even log the transfer activity (sheetnames and range names)?

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

    Re: Importing Named Excel Worksheet (Access97)

    If each sheet has a differently named range, you'll have to write an individual DoCmd.TransferSpreadsheet line for each workbook/worksheet. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    You can insert lines in between to write a log.
    You'll have to experiment to find out whether the code can be run in one go.

Posting Permissions

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