Error 3011 When linking a spreadsheet

Hi All,
I'm trying to set up a database which links tables automatically so there are about 30 data sources i have to refresh on a fortnightly basis. The problem is i want to link a spreadsheet where each sheet is a table and when i specify the sheet in the range section of the transferspreadsheet method it tells me that it can't find it or its spelt incorrectly which i know it isn't i've checked dozen times.
I've used a table for the code to get the table name, file name and sheet name.

Below is the code i'm using. Does anyone know what is wrong with this stupid thing because it's doing my head in? :confused:

Many Thanks


Option Compare Database
Option Explicit

Private db As Database

Public Sub MainIS()

Set db = CurrentDb
ImportTablesIS "_ImportTables"

End Sub

Private Sub ImportTablesIS(strTableNames As String)

Dim rs As Recordset

Set rs = db.OpenRecordset( _
"SELECT Table_Name, Import_Spec, Location, Presented, Format, Sheet, Last_Completed " & _
"FROM [" & strTableNames & "] " & _
"WHERE Skip = False AND (Left([Table_Name], 3) = 'P2P') " & _
"ORDER BY Seq_No ASC;")

Do Until rs.EOF

With Application.FileSearch
.NewSearch
.FileName = rs!Location

If .Execute(msoSortByLastModified, msoSortOrderAscending) > 0 Then

If rs!Format = "Notepad" Then

DoCmd.TransferText acLinkDelim, rs!Import_Spec, rs!Table_Name, .FoundFiles(1), True

rs.Edit
rs!Last_Completed = Now
rs.Update
rs.MoveNext

ElseIf rs!Format = "Excel" Then

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel97, _
rs!Table_Name, rs!Location, True, rs!Sheet

rs.Edit
rs!Last_Completed = Now
rs.Update
rs.MoveNext

End If
End If
End With