Hi,

I'm using DoCmd.TransferSpreadsheet function bellow. Now the issue that I have is that it takes whatever data is in the first sheet in the Excel file regardless of what was the activesheet when I saved/closed the file. Is there any way to get around this?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MigData", TxtFilename, True

Yes, there is . . . you just need to add to the end of the properties you already have to specify a certain sheet and even a certain range if need be.

Example: DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MigData", TxtFilename, True, "MyMainSheet!A7:U65536"

NOTE: This does not work with sheets created in older versions of excel.
It will have to have been created in Excel97 or Later and must be at least version 5.0


Coby.

Hi,

That only works assuming that the sheet is the same name... but say I have an excel doc that has 3 sheets, I save/exit having had the 2nd sheet as the active sheet.

When I come to upload the file, I want to upload the 2nd sheet in excel, but I won't necessarily know what the name of the sheet will be?

Woudl there be a way to read the sheets from excel, and in access have a pull down menu allowing to specify the target sheet?