DoCmd.TransferSpreadsheet from Excel to Access using sheet codename - NOT tabname

[shanesuzanne]

Table name is employees
File name is varFile (used with a file picker)

This works:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "employees", varFile, False, "January!A4:AD10000"


The problem is the tab names change each month. For the life of me, I can't get the sheet codename to work. This is sheet(1). I've googled for HOURS. Can anyone point me in the right direction?

The code is in Access 2010, importing from Excel 2013. Thanks!

 

[Optimist]

I have had many situations like these before, and while it is common sense and good practice to force your originators (source of files) to adhere to naming conventions, this is not always possible and anything human is error-prone.
What I have done in the past is to use the Excel object to open the files in question and loop through the worksheets using a pattern within the sheet to *guess* what the sheet is and to ask the user to confirm. Then it is easy to use the actual names in the Access Import. In my experience, that is not only very reliable, but would add sufficiently more grief to the operator that it might even pressure the originators to adhere to naming conventions!