Results 1 to 2 of 2
2004-07-06, 18:54 #1
- Join Date
- Mar 2004
- 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.
2004-07-06, 19:23 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 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.