Import problems with .FoundFiles

[TheRiver]

THis is a code I have to find a file and import the file. It works all the way up to the:

If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
But I dont know why but it is not picking up the file here and allowing the import to take place.

What Is going wrong and how do I correct it?

Dim Create As Database, wl_import As Recordset, wl_cmds As Recordset, int1 As Long, int2 As Long, str_answer As String, str_filename As String, i As Integer


Set Create = DBEngine.Workspaces(0).Databases(0)
Set wl_import = Create.OpenRecordset("WL CMDS (RNA00)&quot
Set wl_cmds = Create.OpenRecordset("WL NonRes&quot


With Application.FileSearch
.NewSearch
.LookIn = "K:\WL CMDS (Current Financial Year)_\Dudley Group of Hospitals NHS Trust\Inpatient Waiting _List CMDS"
'Below File needs to be updated each month with the _latest filename

str_answer = InputBox("What month?" & Chr(10) & Chr_(13) & Chr(10) & Chr(13) & "Please ensure the month is in _the abbreviated format ""Jan"" for January", _vbInformation, "Select Month&quot
Select Case str_answer
Case "Apr"
str_filename = "01 IWL Apr(QEE).txt"
Case "May"
str_filename = "02 IWL May(QEE).txt"
Case "Jun"
str_filename = "03 IWL Jun(QEE).txt"
Case "Jul"
str_filename = "04 IWL Jul(QEE).txt"
Case "Aug"
str_filename = "05 IWL Aug(QEE).txt"
Case "Sep"
str_filename = "06 IWL Sep(QEE).txt"
Case "Oct"
str_filename = "07 IWL Oct(QEE).txt"
Case "Nov"
str_filename = "08 IWL Nov(QEE).txt"
Case "Dec"
str_filename = "09 IWL Dec(QEE).txt"
Case "Jan"
str_filename = "10 IWL jan(QEE).txt"
Case "Feb"
str_filename = "11 IWL Feb(QEE).txt"
Case "Mar"
str_filename = "12 IWL Mar(QEE).txt"
Case Else
'unknown file name - would be better if _this problem was handled better
End Select
.FileName = str_filename
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DoCmd.TransferText acImportFixed, "IPWL _04 RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)
Next i
End If
End With

 

[SkipVought]

TheRiver,

It works for me.

Did you copy and paste the Path from your Windows Explorer?

And is each filename exactly correct for the files in the specified folder?

???

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[TheRiver]

I think the problem may be to do with the following line now:
DoCmd.TransferText acImportFixed, "IPWL _04 RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)

I just cant see why it is not picking up the file I have tried everything I can think of.

 

[TheRiver]

OK sorted this problem out but want to know if I can import more than one file at a time when I select Apr, May, etc.

Eg if I can import the following files, when I select Apr:

01 IWL Apr(5MG).txt
01 IWL Apr(5MH).txt
01 IWL Apr(5MJ).txt

This is the code as it stands....

Dim Create As Database, wl_import As Recordset, wl_cmds As Recordset, int1 As Long, int2 As Long, i As Integer, str_answer As String, str_filename As String


Set Create = DBEngine.Workspaces(0).Databases(0)
Set wl_import = Create.OpenRecordset("WL CMDS (RNA00)&quot
Set wl_cmds = Create.OpenRecordset("WL NonRes&quot

With Application.FileSearch
.NewSearch
.LookIn = "K:\WL CMDS (Current Financial Year)\Dudley Group of Hospitals NHS Trust\Inpatient Waiting List CMDS"
'Below File needs to be updated each month with the _latest filename

str_answer = InputBox("What month?" & Chr(10) & Chr(13) & Chr(10) & Chr(13) & "Please ensure the month is in _the abbreviated format ""Jan"" for January", vbInformation, "Select Month&quot
Select Case str_answer
Case "Apr"
str_filename = "01 IWL Apr (QEE).txt"
Case "May"
str_filename = "02 IWL May (QEE).txt"
Case "Jun"
str_filename = "03 IWL Jun (QEE).txt"
Case "Jul"
str_filename = "04 IWL Jul (QEE).txt"
Case "Aug"
str_filename = "05 IWL Aug (QEE).txt"
Case "Sep"
str_filename = "06 IWL Sep (QEE).txt"
Case "Oct"
str_filename = "07 IWL Oct (QEE).txt"
Case "Nov"
str_filename = "08 IWL Nov (QEE).txt"
Case "Dec"
str_filename = "09 IWL Dec (QEE).txt"
Case "Jan"
str_filename = "10 IWL Jan (QEE).txt"
Case "Feb"
str_filename = "11 IWL Feb (QEE).txt"
Case "Mar"
str_filename = "12 IWL Mar (QEE).txt"
Case Else
'unknown file name - would be better if this problem was handled better
End Select
.FileName = str_filename
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DoCmd.TransferText acImportFixed, "IPWL 04 RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)
Next i
End If
End With