Run-time error '3011'

[LJG]

Hi Guys,

Anyone Help with this error please. I am running some code that imports
spread sheet data and keep getting this error:

Run-time error '3011':

The microsoft Jet database engine couldnot find the object
'C:\database\Appointment_456_fred Bloggs.xls', Make sure the object
exists and that you spell its name and path correctly.

When I go to Debu the error were it stalls it shows the filename correctly?
and will not complete the import?

This is my code:

Function newAppoint()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSS As String
Dim strTransfer As String

10 If MsgBox("You are about to import a new appointment, " & _
"Do you wish to continue?. ", vbYesNo) = vbYes Then
20 DoCmd.SetWarnings False
30 Set db = CurrentDb
40 Set rst = db.OpenRecordset("tblUser") 'the name of the table
50 If rst.RecordCount = 0 Then
60 MsgBox "No Records To Process"
70 End If
80 rst.MoveLast
90 rst.MoveFirst
100 Do Until rst.EOF
110 strSS = "C:\database\appointments\Appointment_" _
& "*" & rst.Fields("User Name") & ".xls" 'the name of
the Field
120 strTransfer = Dir(strSS)
130 Do While strTransfer <> "" ' Start the loop.
140 DoCmd.TransferSpreadsheet acImport, 8,
"tempAppointments", _
strTransfer, True, ""
150 strTransfer = Dir
160 Loop
170 rst.MoveNext
180 Loop
190 rst.Close
200 Set rst = Nothing
210 Set db = Nothing
220 Call MsgBox("Your new appointment(s) have been added",
vbInformation, "Appointment Updates ")

230 End If
End Function

it fails at lines 140-150, although if I hiver with mouse over section it
shows me the file name.

Any help would be appreciated.

TIA

Les

 

[Pieter Wijnen]

Dir Removes the path
strSS = "C:\database\appointments\Appointment_" _
& "*" & rst.Fields("User Name") & ".xls" 'the name of
strTransfer = Dir(strSS) ' = rst.Fields("User Name") & ".xls

Pieter