TransferSpreadsheet import Sheet Name (2003 SP3)

Topic

New Reply

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

Reply | Quote

Replies

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:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblImport", "Test.xls", True, "Sheet2!A1:D37"

and

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblImport", "Test.xls", True, "MyRange"

Here, MyRange is a defined name referring to a fixed worksheet range.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote