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
"LJG" <lj_girvan[ at ]no_spam_hotmail.com> wrote in message
news:eilEYn8sGHA.1296[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > 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
>
>
>
--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4388 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!
|