Importing a file using module

[TheRiver]

This is a code I have to find a file and import the file.

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


But no matter what I try it does not work. I checked and double checked the path and filename, but it passes through the code ok, but no file is imported. What am I doing wrong? I just can't seems to work it out, HELP!!!!

 

[JeffTullin]

Have you put a breakpoint on the TransferText line to see if the code is actually attempting to load something?
(If .foundfiles =0 then it will never execute)

This seems like a lot of code to do what you want.
Since you would only expect one file to match anyway, why not just do this:

dim strLookin as string

strLookIn = "K:\WL CMDS (Current Financial Year)_\Dudley Group of Hospitals NHS Trust\Inpatient Waiting _List CMDS"


if dir(strLookin & "\" & str_filename) > "" then
'true if file exists
DoCmd.TransferText acImportFixed, "IPWL _04 RNA
Import",
"WL CMDS (RNA00)",
strLookin & "\" & str_filename
else
msgbox "File not found"
endif

 

[TheRiver]

I sorted my problem now, just got another problem though.

What if I wanted to transfer three files from a specfied folder, but within this folder there are over 30 other files.

So If I select Apr I want it to return the following files:

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

Any idead of adding this to the below code:
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

 

[MakeItSo]

First:
Slim down your code by using
str_filename = "12 IWL " & str_answer & "(QEE).txt"
instead of your entire select case.


Then:
Go through your code with single steps to check whether
DoCmd.TransferText ....
is reached at all.

I assume some IF condition unmatched so it just skips this line.

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[MakeItSo]

OOps forget my post - waayyyy too slow! ;-)

 

[JeffTullin]

For three files that match a criteria
you will need to loop

dim szfile as string
szFile = dir(strLookin & "\" & "01 IWL " & Apr & "*.txt&quot

while szFile > "" then
DoCmd.TransferText acImportFixed, "IPWL _04 RNA
Import",
"WL CMDS (RNA00)",
strLookin & "\" & szFile

'any more files?
szFile = dir()
wend

 

[TheRiver]

how would this fit in with the rest of my code? taking into the fact that I might run May, Jun Jul, etc

Also this is not liked within the module?!!!
While szFile >"" Then

 

[JeffTullin]

>>Also this is not liked within the module?!!!
While szFile >"" Then
<<

Three whole exclamation marks ...?

Sorry about that , cut & paste strikes again.
The THEN was left over from a first draft that used
IF <> THEN
I changed it to WHILE and forgot to remove the THEN.
I assume you are not familiar with WHILE?

So, let's try again. I also see that I used Apr instead of str_answer, so that would have been a problem too.

Please take this as a guide.


dim szfile as string
'this next line assembles a string that
' for Apr would look like
' C:\folder\01 IWL Apr*.TXT
'for May it would be
' C:\folder\01 IWL May*.TXT

szFile = dir(strLookin & &quot;\&quot; & &quot;01 IWL &quot; & str_answer & &quot;*.txt&quot

'following the first call to DIR,
'szFile would hold one file that matched the pattern
'and thus be greater than &quot;&quot;

while szFile > &quot;&quot;

'so, lets load that file
DoCmd.TransferText acImportFixed, &quot;IPWL _04 RNA
Import&quot;,
&quot;WL CMDS (RNA00)&quot;,
strLookin & &quot;\&quot; & szFile

'any more files?

szFile = dir()
'if there are, then szFile has a new filename
'and remains greater than &quot;&quot;
'so the loop continues

'if there are no more matches, the DIR()
'returns an empty string, and the loop
'will stop
wend