Import Excel Worksheet Into Access - Illegal "!"

[Kariera]

I thought this would be the easiest thing in the world to do – using VB I want to import a specific worksheet into Access. The “!” is an illegal character and Access won’t take it in the code. Do you know how to import a spreadsheet (not the whole file and not necessarily the first sheet in the workbook) into Access? I don’t want to name the different ranges for different pages. Right now I had “pd 6 2004!Rep” – pd 6 2004 is the sheet and Rep is the range. "myRange" in the code is the problem.

Any help would be appreciated.

varPeriod = Me.cmbPeriod
varYear = Me.cmbYear
myRange = "pd. " & varPeriod & " - " & varYear & "!Rep"
Set fs = Application.FileSearch
With fs
.LookIn = "C:\Files\2004"
.FileName = "*TopSalesRep*"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
myFileName = Dir(.FoundFiles(i), vbDirectory)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblDataImport", "C:\Files\2004\" & myFileName, , myRange

Next i
Else
MsgBox "There were no files found."
End If