tranferpreadsheet with Range not working

[gammaman]

I am trying to use transferspread sheet in a loop to transfer data from an excel workbook containing 3 "Named" sheets each into there own access table. After running the macro, the table definitions are created but the tables have no data.
In the Range parameter I am trying to pass in the variable tSheetName which contains the name of the current worksheet.

Open File Dialog to choose file to process
Set fd2 = Application.FileDialog(msoFileDialogFilePicker)
'tSheetCount = 1
'tNameCount = 1

With fd2
  .Show
  For Each vrtSelected In .SelectedItems
    For k = 1 To ws_count
        xlWrkBk.Activate
        'store the name of the current worksheet
        tSheetName = xlWrkBk.Sheets(k).Name
        'create a table definition based on the name of the current worksheet
        Set tb = db.TableDefs(tSheetName)
        
        strColumns = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR")
     'Loop First Row in Each Excel Worksheet which contains header column names and write to the current table defention

     For strColumn = LBound(strColumns) To UBound(strColumns)
   
        Set columnName = xlWrkBk.Sheets(k).Cells(1, strColumn)
       
        If columnName = "Member Birthdate" Or columnName = "Member Effdt" Then
           Set FieldName = tb.CreateField(columnName, dbDate, 10)      'insert Date Type Fields into the table def
           tb.Fields.Append FieldName
        Else
           Set FieldName = tb.CreateField(columnName, dbText, 200)     'insert text type fields into table def
           tb.Fields.Append FieldName
        End If
           
     Next strColumn
      
     'transfers data from excel file and imports it into the access table
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, TableName:=tSheetName, FileName:=vrtSelected, HasFieldNames:=True, Range:="tSheetName!"
   
     tSheetName = Nothing
    Next k
    Next vrtSelected
    End With

[June7]

Don't enclose variables in quotes, that results in the literal text "tSheetName!" instead of the value in tSheetName. Concatenate variables with literals to get the desired construct.

Range:=tSheetName & "!"

[Dal Jeanis]

Nice catch, June7!

[gammaman]

Thank You Much!!