running a loop with transfertext

[ssanfu]

Here is your code modified - with comments. I have not tested the code completely, but it should work. (looks like June beat me...again )
Instead of hardcoding the filenames/tables names I would have the names in a table and load them each time...


Also I would use the system status message area to display which file was imported. That way you don't have to hit enter each time for the message box.

Sub txtImportstring()
   Dim strFilter As String
   Dim SourceFolder As String
   Dim strFolderName As String
   Dim InputSpec As String

   Dim i As Integer
   Dim LB As Integer
   Dim UB As Integer

   Dim strtblname   'array
   Dim strInputFileName  'array  
   '------------------

   ' arrays
   strtblname = (Array("Address_Information", "Portfolio_Manager_Section", "Fund_Strategy_Section", "Risk_Management", "Service_Providers", "Investment_Desicion", "Supporting Documents"))
   strInputFileName = (Array("Address Information.CSV", "Portfolio Manager Section.CSV", "Fund Strategy Section.CSV", "Risk Management.CSV", "Service Providers.CSV", "Investment Desicion.CSV", "Supporting Documents.CSV"))

   'upper & lower array bounds - ie how many elements in the array "strtblname"?
   LB = LBound(strtblname)
   UB = UBound(strtblname)

   'folder where the csv files are located
   SourceFolder = GetFolder()

   InputSpec = "actextTypeCSV12"

   For i = LB To UB
      If Len(strInputFileName(i)) > 0 Then
         'create the string
         strFolderName = SourceFolder & "\" & strInputFileName(i)

         DoCmd.TransferText acImportDelim, InputSpec, strtblname(i), strFolderName, True

         'tell which file was imported.
         'problem with MsgBox is action is required to continue
         MsgBox "Imported " & strtblename(i)
      End If

   Next

Import_Exit:
   Exit Sub

Import_Err:
   MsgBox Error$
   Resume Import_Exit
End Sub


Function GetFolder()
Dim SA As Object
Dim f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "choose a folder", 16 + 32 + 64)
If (Not f Is Nothing) Then
    GetFolder = f.items.Item.Path
End If
Set f = Nothing
Set SA = Nothing
End Function

[mike02]

thanks steve! That works !!!