Importing Spreadsheet

[pepperca]

I have the following piece of code attached to a button on a form. It works great if you only have one sheet in an excel workbook to import. However, the particular workbook I am trying to import from has 2 sheets and I need to import both of them. The sheet names will change from time to time so I can't reference a particular sheet name and have it work every time I import this information.

newFileCMS = curDirCMS 'initializes variable to current directory
newFileCMS = ahtCommonFileOpenSave(aht_OFN_HIDEREADONLY, curDirCMS, strFilter, 1, xls)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImportSheet", newFileCMS, False


I appreciate any help.

Cindy

 

[mp9]

Can't you specify a range as a sixth parameter of the TransferSpreadsheet method, i.e. after the False?

This would enable you to have two TransferSpreadsheet commands, each with a different sheet range parameter.

Search Access VBA help for more on the TransferSpreadsheet method and its parameters.

 

[pepperca]

I don't think this would help because I would have to know the sheet name and I won't always know this information without actually opening the Workbook.