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

    Specifying Worksheet in TransferSpreadsheet Macro (Access XP)

    I'm frustrated trying to get Access to import an excel worksheet to a table using the TransferSpreadsheet function within a macro. I have multiple worksheets in the excel workbook file - There's no problem getting Access to import the first sheet in the workbook, but when I try to specify a different worksheet, Access indicates that it can't find the object. I think I have a syntax problem. How do you identify the source xls workbook right down to the worksheet level? .....Also, the database will be repeatedly refreshed (that's the reason I'm trying to automate this)....I want it to import the full range of data on a specific sheet (One worksheet = One Table), and the range of data on the sheet will change from one load to another...so I figure I just want to grab what's on the worksheet, regardless of the number of cells with values - am I understanding that this could be a problem?

    Help! Anxiously Waiting.

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

    Re: Specifying Worksheet in TransferSpreadsheet Macro (Access XP)

    If you want to import a table from another than the first worksheet in a workbook, you must specify a literal range, such as

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImport", "C:ExcelDemo.xls", True, "Sheet2!A112"

    or use a named range:

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImport", "C:ExcelDemo.xls", True, "MyRange"

    Unfortunately, Access does not recognize dynamic named ranges, so you must take care that your named ranges grow and shrink with the data. As an alternative, create a link to a worksheet; Access will adjust the linked table dynamically. You can either work with the linked table directly, or use a make-table query to transfer the data from the linked table to a "real" table.

Posting Permissions

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