Use Transferspreadsheet with variables

[AFLightC]

I have the following code to use transferspreadsheet:

------------
Function mcrImportExcelTab1(sFile As String, sTab As String)

DoCmd.TransferSpreadsheet acImport, 8, "tblTempImport", sFile, True, sTab

End Function
-------------

My sTab variable comes in like this : Air PTE Criteria Pollutants!A$1:$H$16

I've also tried : [Air PTE Criteria Pollutants]!A$1:$H$16

I get an error, either 3011 or 3125, respectively.

In all cases, in the error message the ! has been changed for a $, now reading Air PTE Criteria Pollutants$A$1:$H$16

I'm trying to import multiple tabs with various ranges. Any ideas on how to do this?

 

[AFLightC]

Okay, I figured this out. While I had the spreadsheet opened to read the tabs and find the range, I named the range. I then passed the range name as the variable after closing and saving the spreadsheet