import problems

[john]

Help!!!
I nned to 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)")
Set wl_cmds = Create.OpenRecordset("WL NonRes")

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")
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

 

[Steven Parsons [MSFT]]

Hi John -

This is Steven from Microsoft Access Technical Support replying to your
newsgroup post. Please try the following code in an Access database to see
if it will get you close to your objective:

Sub subImportFiles()

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''
'Import text files.
'
'This routine requires a reference to the "Microsoft Scripting Runtime"
library.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''
Dim str_answer As String
Dim strPath As String
Dim strNewTableName As String
Dim fso As FileSystemObject
Dim fld As Folder
Dim fls As Files
Dim fil As File
Dim intPosition As Integer
Dim intCounter As Integer

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")

'Path where text files reside.
'Be sure to include the backslash (\) on the end of your path."
strPath = "C:\Documents and Settings\My Documents\Temp\"
intCounter = 0

'Instantiate file system objects that will point to necessary folder
and files.
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(strPath)
Set fls = fld.Files

'Find each text file that contains the value of str_answer as part of
the file name and
'import that text file into the current database.
For Each fil In fls
intPosition = 0
intPosition = InStr(1, fil.Name, str_answer, vbTextCompare)

'The current text file contains the value of str_answer as part of
its file name.
If intPosition > 0 Then
strNewTableName = Left$(fil.Name, Len(fil.Name) - 4)
DoCmd.TransferText acImportFixed, "IPWL 04 RNA Import",
strNewTableName, strPath & fil.Name
intCounter = intCounter + 1
End If
Next fil

'Destroy all the file system object pointers.
Set fil = Nothing
Set fls = Nothing
Set fld = Nothing
Set fso = Nothing

'Inform user of the number of imported text files.
MsgBox "There were " & intCounter & " files successfully imported."
End Sub

Please let me know if this solves your problem or if you would like further
assistance. I look forward to hearing from you.

Sincerely,
Steven Parsons [MSFT]
Microsoft Access Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! (http://www.microsoft.com/security)